Mysql实现主从复制

实验环境：

server5：主server6：从server7：从iptabls offselinux Disabled

安装mysql

*先查看是否有其它版本的,若有，则先卸载[root@server5 ~]# rpm -qa | grep mysqlmysql-libs-5.1.71-1.el6.x86_64[root@server5 ~]# rpm -e mysql-libs-5.1.71-1.el6.x86_64error: Failed dependencies:    libmysqlclient.so.16()(64bit) is needed by (installed) postfix-2:2.6.6-2.2.el6_1.x86_64    libmysqlclient.so.16(libmysqlclient_16)(64bit) is needed by (installed) postfix-2:2.6.6-2.2.el6_1.x86_64    mysql-libs is needed by (installed) postfix-2:2.6.6-2.2.el6_1.x86_64[root@server5 ~]# rpm -e --nodeps mysql-libs-5.1.71-1.el6.x86_64[root@server5 ~]# rpm -qa | grep mysql[root@server5 ~]# 

*安装mysql

[root@server5 ~]# tar zxf mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar gzip: stdin: not in gzip formattar: Child returned status 1tar: Error is not recoverable: exiting now[root@server5 ~]# tar xf mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar #注意顺序[root@server5 ~]# rpm -ivh mysql-community-common-5.7.17-1.el6.x86_64.rpm[root@server5 ~]# rpm -ivh mysql-community-libs-5.7.17-1.el6.x86_64.rpm [root@server5 ~]# rpm -ivh mysql-community-devel-5.7.17-1.el6.x86_64.rpm[root@server5 ~]# yum install libaio -y[root@server5 ~]# yum install numactl -y[root@server5 ~]# rpm -ivh mysql-community-client-5.7.17-1.el6.x86_64.rpm[root@server5 ~]# rpm -ivh mysql-community-server-5.7.17-1.el6.x86_64.rpmwarning: mysql-community-server-5.7.17-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEYerror: Failed dependencies:    /usr/bin/perl is needed by mysql-community-server-5.7.17-1.el6.x86_64    perl(File::Path) is needed by mysql-community-server-5.7.17-1.el6.x86_64    perl(Getopt::Long) is needed by mysql-community-server-5.7.17-1.el6.x86_64    perl(POSIX) is needed by mysql-community-server-5.7.17-1.el6.x86_64    perl(strict) is needed by mysql-community-server-5.7.17-1.el6.x86_64[root@server5 ~]# yum install perl  -y[root@server5 ~]# rpm -ivh mysql-community-server-5.7.17-1.el6.x86_64.rpm[root@server5 ~]# 

*在server5：master

[root@server5 ~]# vim /etc/my.cnf.....log-bin=mysql-binbinlog-do-db=testserver-id=1binlog-ignore-db=mysql.....[root@server5 ~]# /etc/init.d/mysqld startInitializing MySQL database:                               [  OK  ]Installing validate password plugin:                       [  OK  ]Starting mysqld:                                           [  OK  ][root@server5 ~]#[root@server5 ~]# cat /var/log/mysqld.log | grep pass  #过滤初始密码2017-06-13T01:22:16.400573Z 1 [Note] A temporary password is generated for root@localhost: M.uepxaHq3oI[root@server5 ~]# mysql_secure_installation        #修改mysql默认root密码[root@server5 ~]# mysql -uroot -pXiamin+0099mysql> GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO westos@'172.25.66.7'    -> IDENTIFIED BY 'Xiamin+0099';         #创建账户并给与权限Query OK, 0 rows affected, 1 warning (0.07 sec)mysql> Flush privileges;Query OK, 0 rows affected (0.12 sec)mysql> quitBye[root@server5 ~]#

*在server7：slave

[root@server7 ~]# vim /etc/my.cnf.....server-id=2.....[root@server7 ~]# /etc/init.d/mysqld startInitializing MySQL database:                               [  OK  ]Installing validate password plugin:                       [  OK  ]Starting mysqld:                                           [  OK  ][root@server5 ~]#[root@server7 ~]# mysql_secure_installation[root@server7 ~]# mysql -uroot -pXiamin+0099(此时需要先stop slave)mysql> change master to master_host='172.25.66.5', master_user='westos',    -> master_password='Xiamin+0099', master_log_file='mysql-bin.000005', master_log_pos=616;Query OK, 0 rows affected, 2 warnings (0.50 sec)mysql> start slave;Query OK, 0 rows affected (0.06 sec)mysql> show slave status\G;.....            Slave_IO_Running: Yes            Slave_SQL_Running: Yes.....mysql> quitBye[root@server7 ~]# 

测试：

在master中新建test库[root@server5 ~]# mysql -uroot -pXiamin+0099.....mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                |+--------------------+4 rows in set (0.07 sec)mysql> create database test;Query OK, 1 row affected (0.08 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                || test               |+--------------------+5 rows in set (0.00 sec)mysql> use testDatabase changedmysql> create table MyClass(    -> id int(4) not null primary key auto_increment,    -> name char(20) not null,    -> sex int(4) not null default '0',    -> degree double(16,2));Query OK, 0 rows affected (1.20 sec)mysql> show tables;+----------------+| Tables_in_test |+----------------+| MyClass        |+----------------+1 row in set (0.00 sec)mysql> show processlist;+----+--------+---------------+------+-------------+-------+---------------------------------------------------------------+------------------+| Id | User   | Host          | db   | Command     | Time  | State                                                         | Info             |+----+--------+---------------+------+-------------+-------+---------------------------------------------------------------+------------------+|  3 | westos | server7:50403 | NULL | Binlog Dump | 17034 | Master has sent all binlog to slave; waiting for more updates | NULL             || 14 | root   | localhost     | NULL | Query       |     0 | starting                                                      | show processlist |+----+--------+---------------+------+-------------+-------+---------------------------------------------------------------+------------------+2 rows in set (0.00 sec)mysql> quitBye.....[root@server5 ~]# 

在slave上查看

[root@server7 ~]# mysql -uroot -pXiamin+0099.....mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                || test               |+--------------------+5 rows in set (0.00 sec)mysql> use testReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+----------------+| Tables_in_test |+----------------+| MyClass        |+----------------+1 row in set (0.00 sec)mysql> show processlist;+----+-------------+-----------+------+---------+-------+--------------------------------------------------------+------------------+| Id | User        | Host      | db   | Command | Time  | State                                                  | Info             |+----+-------------+-----------+------+---------+-------+--------------------------------------------------------+------------------+|  1 | system user |           | NULL | Connect |  1894 | Slave has read all relay log; waiting for more updates | NULL             ||  2 | system user |           | NULL | Connect | 16880 | Waiting for master to send event                       | NULL             || 10 | root        | localhost | NULL | Query   |     0 | starting                                               | show processlist |+----+-------------+-----------+------+---------+-------+--------------------------------------------------------+------------------+3 rows in set (0.00 sec)mysql> quitBye.....[root@server7 ~]#