MySQL 5.7主主复制配置

一、主机规划

角色　　主机名　　　　IP地址

主库　　master　　192.168.0.130

从库　　slave 　     192.168.0.140

二、安装 MySQL 5.7

1.卸载两台主机mysql旧的相关软件包

# rpm -qa | grep -i mysql

# yum remove  -y mysql-* 

2.在两台主机安装MySQL 5.7

# wget http://dev.mysql.com/downloads/repo/yum/mysql57-community-release-el6-8.noarch.rpm

# rpm -ivh mysql57-community-release-el6-8.noarch.rpm 

# yum -y install mysql.x86_64 mysql-server.x86_64 mysql-devel.x86_64

3.在两台主机初始化MySQL 5.7

# mkdir /data/mysql

# chown -R mysql:mysql /data/mysql

# mysqld --initialize-insecure --user=mysql --datadir=/data/mysql

4.在master主机的/etc/my.cnf中配置以下内容

[mysqld]
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id = 1
auto-increment-increment = 2
auto-increment-offset = 1

explicit_defaults_for_timestamp = 1
character_set_server=utf8
interactive_timeout = 57600
log-bin = master-bin
log-bin-index=master-bin.index

expire-logs-days = 100
replicate-do-db  = test                #需要同步的数据库
binlog-ignore-db  = mysql　　　#需要忽略的数据库
binlog-ignore-db  = information_schema

slave-skip-errors=all
log-slave-updates
symbolic-links=0 
skip-name-resolve

5.在slave主机的/etc/my.cnf中配置以下内容

[mysqld]
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id = 2
auto-increment-increment = 2
auto-increment-offset = 2

character_set_server=utf8
interactive_timeout = 57600
log-bin =masterl-bin
expire-logs-days = 100

replicate-do-db = test                  #需要同步的数据库
binlog-ignore-db = mysql
binlog-ignore-db = information_schema

slave-skip-errors=all
log-slave-updates
symbolic-links=0
skip-name-resolve

5.在master、slave主机的重启MySQL5.7服务

# service mysqld restart

6.在master主机创建数据库和表并插入数据

# mysql -uroot -p
mysql > create database test;
mysql > use test;
mysql > create table test(id int,name varchar(100));
mysql > insert into wclwcw value (1,'tom');

7.互相授权

7.1 在主机master授权

grant replication slave on *.* to 'repl'@'192.168.0.140' identified by 'mysql';

flush privileges;

7.2 在主机master授权

grant replication slave on *.* to 'repl'@'192.168.0.130' identified by 'mysql';

flush privileges;

8、互告bin-log信息
8.1 在master主机上
mysql > show master status;
+------------------+----------+--------------+--------------------------+
| File     | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+--------------------------+
| mysql-bin.000006 |      106 |      | mysql,information_schema |
+------------------+----------+--------------+--------------------------+

8.2 在主机slave上
mysql> show master status;
+------------------+----------+--------------+--------------------------+
| File     | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+--------------------------+
| mysql-bin.000008 |      192 |      | mysql,information_schema |
+------------------+----------+--------------+--------------------------+

8.3 在master上
mysql > change master to master_host='192.168.0.140',master_user='test',master_password='mysql',master_log_file='mysql-bin.000008',master_log_pos=194;

8.4 在slave上
mysql > change master to master_host='192.168.0.130',master_user='test',master_password='mysql',master_log_file='mysql-bin.000007',master_log_pos=1082;

9、在master、slave两服务器都执行以下命令
mysql > start slave;

10、查看状态
10.1 在主机master上
mysql> show slave status\G
Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.140
                  Master_User: zz
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 778
               Relay_Log_File: template-relay-bin.000002
                Relay_Log_Pos: 780
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: wang
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 778
              Relay_Log_Space: 990
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
                  Master_UUID: e6a7bb8f-4fe6-11e6-abd0-fa163e5cb863
             Master_Info_File: /home/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 

10.2 在主机slave上
mysql> show slave status \G；
      Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.2
                  Master_User: zz
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 769
               Relay_Log_File: template-relay-bin.000002
                Relay_Log_Pos: 484
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: wang
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 769
              Relay_Log_Space: 694
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 10824007-4fe4-11e6-a4ff-fa163ea94ff8
             Master_Info_File: /home/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version:
                      
当看到了两个yes，即：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
说明已经配置成功了，同时查看主机2中是否已经有主机1中的数据库

其他
同步数据
用test做的实验，导出将test.sql文件从1.4服务器拷贝到1.2服务器
备份数据前先锁表，保证数据一致性
mysql> FLUSH TABLES WITH READ LOCK;
# mysqldump -uroot -p123456 test> /tmp/test.sql;

mysql> UNLOCK TABLES;

scp /tmp/test.sql root@192.168.1.2:/tmp

在搭建mysql master-slave复制环境时，一切都正常，但是在slave上执行：show slave status\G; 时，出现下面的状况：
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes

 

转载于:https://www.cnblogs.com/deploy/p/9508467.html