mysql复制出现问题【GTID模式】

非GTID模式下

当BIN-LOG里面出现错误导致主从不能同步的时候,

-- 错误状态的部分截图
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.41.141
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:  
                   Last_Errno: 1032
                   Last_Error: Could not execute Update_rows event on table test.testttt; Can't find record in 'testttt', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000004, end_log_pos 404
                 Skip_Counter: 0
               Last_SQL_Errno: 1032
               Last_SQL_Error: Could not execute Update_rows event on table test.testttt; Can't find record in 'testttt', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000004, end_log_pos 404
  Replicate_Ignore_Server_Ids: 
           Retrieved_Gtid_Set: 69a5d660-cfd9-11ea-8174-00505626c27e:1-5
            Executed_Gtid_Set: 69a5d660-cfd9-11ea-8174-00505626c27e:4,
b3802ae3-cfd8-11ea-85e8-00505630c9f5:1-2
1 row in set (0.00 sec)

可以用下面方法掠过该错误语句行,继续同步:

stop slave;
set global sql_slave_skip_counter=1;
start slave;

当set global sql_slave_skip_counter=1;是可能会出现一下错误:不支持GTID_MODE模式运行的数据库

ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction

GTID模式下

1. 找到错误点

show slave status\G
-- 找到这一行
Executed_Gtid_Set: 69a5d660-cfd9-11ea-8174-00505626c27e:1-5

2. 重置master和slave

reset master;
stop slave;
reset slave;

3. 设置跳过错误点

-- 1-5 可能出错,ID加1,1-6
set global gtid_purged='69a5d660-cfd9-11ea-8174-00505626c27e:1-6';

set global gtid_purged='xxxxx';要求GTID_EXECUTED为空,所以需要第二部重置操作.

4. 重启复制

change master to master_host='192.168.41.141',master_port=3306,master_user='slave',master_password='123456',master_auto_position=1;
start slave;

5. 查看

-- 确认Slave_IO_Running,Slave_SQL_Running,Last_Error,Last_SQL_Error等是否有错
-- 如果还有错但和上面的错误ID不一样,需要重复1~4
show slave status\G

双主模式更麻烦,需要两边都要做1~4操作,确认没有错误.