【mysql】#进阶#mysql的主从/主主复制配置

环境

实例	ip	系统版本	实例版本
master（主）	192.168.10.143	centos 7.3	mysql 5.7.x
slave（从）	192.168.10.139	centos 7.3	mysql 5.7.x

安装步骤

详情参考：https://blog.csdn.net/hj5419/article/details/80231064

配置参数

修改master配置：

[root@localhost /]# vim /etc/my.cnf
        -------------------------以下为打开的my.cnf部分内容---------------------------------
        server_id =1111 //服务ID，不能重复

        log_bin =/usr/local/mysql/log/mysql-bin // 二进制日志名
        log_bin_index=/usr/local/mysql/log/mysql-bin.index  // 二进制日志索引名
        max_binlog_size=200M //二进制日志200M一份
        binlog_format=ROW //复制方式为行复制
        expire_logs_days=7 //二进制日志失效时间为7天
        -------------------------其他可选配置---------------------------------
        #gtid开启  //master开启的话，slave也需要开启
        gtid_mode=on
        enfore_gtid_consistency=on

修改slave配置：

[root@localhost /]# vim /etc/my.cnf
        -------------------------以下为打开的my.cnf部分内容---------------------------------
        server_id =2222 //服务ID，不能重复

        log_bin =/usr/local/mysql/log/mysql-bin // 二进制日志名
        log_bin_index=/usr/local/mysql/log/mysql-bin.index  // 二进制日志索引名
        max_binlog_size=200M //二进制日志200M一份
        binlog_format=ROW //复制方式为行复制
        expire_logs_days=7 //二进制日志失效时间为7天

        relay_log=/usr/local/mysql/log/mysql-relay-bin //slave复制文件名
        log_slave_updates =1 // slave要为其他实例作为master的时候开启这个参数
        sync_binlog=1 // 每次在提交事务之前会将二进制日志同步到磁盘上
        -------------------------其他可选配置---------------------------------
        #gtid开启  //master开启的话，slave也需要开启
        gtid_mode=on
        enfore_gtid_consistency=on

        replication_ignore_db=mysql  //不需要复制的库名
        replication_do_db=test //需要复制的库名    

配置完，重启2个实例。

配置权限

在master上操作：

[root@localhost local]# mysql -uroot -p
Enter password:
mysql>grant replication slave on *.* to 'myrep'@'192.168.10.%' identified by '123456'; 
//这里配置只允许192.168.10下网段的ip
mysql>flush privileges; //刷新权限

配置主从

在master上操作：

mysql>show master status;

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

在slave上操作：

[root@localhost local]# mysql -uroot -p
Enter password:
mysql> change master to master_host='192.168.10.143',master_user='myrep',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=154;

/** change master to //配置复制指向master
master_host='192.168.10.143', //master ip
master_user='myrep', //复制的账号，上面配置的
master_password='123456', //密码
master_log_file='mysql-bin.000001', //在master上show master status下的File名
master_log_pos='154'; //在master上show master status下的Position值
**/
mysql>show slave status \G;

*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.168.10.143
                  Master_User: myrep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: No   //复制线程状态
            Slave_SQL_Running: No   //复制语句执行状态
              Replicate_Do_DB:
          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: 154
              Relay_Log_Space: 154
              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: NULL
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: 0
                  Master_UUID:
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           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:
1 row in set (0.00 sec)

此时Slave_IO_Running，Slave_SQL_Running状态都为NO ，那是因为没有启动复制，只是配置好了。接下来继续在slave配置：

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
mysql>show slave status \G;
//只贴关注的2个状态

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

在master上操作：

此时2个状态都为Yes,说明配置成功，并且可以主从复制了。
在master新建个库，就会同步到slave上了。
注意：
如果master上有已有的库，然后新建表或者插入更新数据，是无法同步到slave上的，复制就会报错。想要实现原有的库也同步到slave，需要将salve建个库与master库一样，并且二进制pos必须跟master一样，具体其他篇幅再说。

延迟复制

在slave上操作：

mysql>stop slave;
mysql>change master to master_delay =10; //单位为秒,延迟配置
mysql>start slave;

错误处理

在查看复制状态查看出错原因，主要关注 Slave_SQL_Running为NO说明复制出错，Last_Errno错误位置，Last_Error错误具体信息。

*************************** 1. row ***************************
        ...
        Relay_Master_Log_File: mysql-bin.000001
        Slave_IO_Running: Yes
        Slave_SQL_Running: No
        Last_Errno: 1146
        Last_Error: Error executing row event: 'Table 'test.user' doesn't exist'
        ...

先排查原因，以上说明表user不存在，具体需要调整的根据实际业务来操作，这里通过跳过事物来跳过这一步，使复制继续运行。

mysql>stop slave;
mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql>start slave;

gtid模式下使用

mysql>stop slave;
mysql>set @@session.gtid_next='0edad259-2b45-11e8-9ceb-7cd30ad38468:432674';  //这里的值就是gtid_next的值
mysql>start slave;

到此配置基本完成。

将master指向slave，就能实现主主复制。

change master to master_host='192.168.10.139',.....