问题
2017-01-24 09:20:36 44646 [ERROR] Slave SQL: Could not execute Write_rows event on table test.test_table;
Duplicate entry '81354' for key 'PRIMARY', Error_code: 1062;
handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000006, end_log_pos 801050669, Error_code: 1062
插入重复的key,导致插入失败,由于线上是使用双主同步的方式,不能简单的删除掉这条记录,再开始同步,这样做到最后,这个删除操作还会被同步执行,这条记录就真的被删除了
- 使用mysqlbinlog命令,查看mysql-bin.000006中end_log_pos=801050669,相应事务的gtid
mysqlbinlog mysql-bin.000006 --base64-output=decode-rows -v | grep 801050669 -A10B5 # at 801049266 #170124 8:28:21 server id 224 end_log_pos 801049314 CRC32 0x0bf0f80d GTID [commit=yes] SET @@SESSION.GTID_NEXT= 'cdc2c1ce-dfbc-11e6-85d0-e41d2d5336a4:1943572'/*!*/; # at 801049314 #170124 8:28:21 server id 224 end_log_pos 801049394 CRC32 0xa764097f Query thread_id=1013 exec_time=0 error_code=0 SET TIMESTAMP=1485217701/*!*/; BEGIN /*!*/; # at 801049394 # at 801050168 #170124 8:28:21 server id 224 end_log_pos 801050295 CRC32 0x6364d6b6 Table_map: `test`.`test_table` mapped to number 258 # at 801050295 #170124 8:28:21 server id 224 end_log_pos 801050669 CRC32 0x897bf9ab Write_rows: table id 258 flags: STMT_END_F
- 登录备库
--由于gtid是连续的,所以只能通过注入空事务的方式跳过出错的事务 stop slave; set gtid_next='cdc2c1ce-dfbc-11e6-85d0-e41d2d5336a4:1943572'; begin;commit; set gtid_next='automatic'; start slave;