环境:centos7 , MySQL5.7一主一从
背景:从上用xtrabackup物理恢复后,感觉gtid相差不大,直接change master to master_host........
问题:从上数据恢复一段时间后,gtid增加到某个值时,从库报错,从上错误日志显示:[ERROR] Slave SQL for channel '': Could not execute Write_rows event on table xxx.xxx; Duplicate entry 'xxxxxx' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log binlog.xxxxxxxx, end_log_pos xxxxxxx, Error_code: 1062
开始分析问题:
可能是从上执行了某些本地事务导致主从数据不一致,主插入数据的时候,从上显示唯一id已经存在。
查看此时从的状态:
show slave status\G
看到从的status中Executed_Gtid_Set多了一行以本地server-uuid开头的gtid,数量不是很大
Executed_Gtid_Set: xxxxxxxxxxxxxxxxxxxxxxxxxxxxx:1-8,
xxxxxxxxxxxxxxxxxxxxxxxxxx:1-36482629374
查看此时从上的binlog,看看这个本地事务做了什么
mysqlbinlog --base64-output=decode-rows -v binlog.000001 > 0001.sql
SET @@SESSION.GTID_NEXT= 'xxxxxxxxxxxxxxxxxxxxxxxxx:3'/*!*/;
###部分数据由于隐私已经删除或则更改
### INSERT INTO `xxxx`.`xxxxxxx`
### SET
### @1=xxxxxxxa
### *
### *
### *
### *
### *
### *
### INSERT INTO `xxxx`.`xxxxxxx`
### SET
### @1=xxxxxxxz
### *
### *
### *
### *
### *
### *
可以看到,这个本地事务在xxx.xxxx这个库中,插入了id为xxxxxa到id为xxxxxxz这么多条数据,而在这其中刚好有一个id为xxxxxx的数据和我们报错信息中的xxxxxxx一模一样,表信息也一模一样!!!
ok,现在问题已经找到了,看看本地的事件,很大可能就是跟他们有关(不知道为什么从上的event打开了,如果是关闭的就不用管了),先把从上的事件关了,这个不影响主上的业务。
SELECT * FROM information_schema.events\G
set global event_scheduler = off;
解决步骤:
先删除这个重复的id
######以下代码均在从上执行
stop slave;
delete from xxx.xxxx where id=xxxxxx;
start slave;
show slave status\G
如果还有报错,那就要考虑批量删除了
######以下代码均在从上执行
stop slave;
delete from xxx.xxxx where id>=xxxxxxa+1 and id <= xxxxxxxz;
start slave;
show slave status\G
我们删除本地事件执行的这些插入,start slave后,从上会重新执行主上同步过来的这个事务,就能完成主从同步了。
Tips:如果执行了上面的操作后,从上还是这个报错,就继续在从上的binlog日志中查找本地事件的插入是否和错误日志中冲突的id值相同,继续上面的删除操作。