gtid同步异常处理
分析出现问题时候GTID值
通过分析法获取gtid值
通过查看
mysql> show slave status G;
查看一下信息并记录下来:
Retrieved_Gtid_Set: 8f9e146f-0a18-11e7-810a-0050568833c8:1-4 -- 跳过此事务,这个非常重要
Executed_Gtid_Set: 8f9e146f-0a18-11e7-810a-0050568833c8:1-3,f7c86e19-24fe-11e7-a66c-005056884f03:1-9
或者 通过日志查看 (推荐)
#170421 15:36:28 server id 2 end_log_pos 938 CRC32 0x9f9f38d8 Xid = 140
COMMIT/*!*/;
# at 938
#170421 15:39:10 server id 2 end_log_pos 1003 CRC32 0x20f00692 GTID last_committed=3sequence_number=4
SET @@SESSION.GTID_NEXT= '8f9e146f-0a18-11e7-810a-0050568833c8:4'/*!*/;
# at 1003
#170421 15:39:10 server id 2 end_log_pos 1113 CRC32 0x4b10f015 Query thread_id=25420exec_time=0error_code=0
use `test`/*!*/;
SET TIMESTAMP=1492760350/*!*/;
create unique index i_index on t(id)
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
解决办法:跳过错误(在出现异常的从库中操作)
#(1)停止slave进程 mysql> STOP SLAVE; #(2)设置事务号,事务号从Retrieved_Gtid_Set获取 #在session里设置gtid_next,即跳过这个GTID SET @@SESSION.GTID_NEXT= '8f9e146f-0a18-11e7-810a-0050568833c8:4'; #(3)注入空事物 BEGIN; COMMIT; #(4)恢复事物号 SET SESSION GTID_NEXT = AUTOMATIC; #(5)启动slave进程 START SLAVE;
示例:(多源复制异常处理)
mysql > show slave statusG ...... ...... Master_SSL_Crlpath: Retrieved_Gtid_Set: e37d51d8-f5f3-11e7-9c02-5254002a5bf5:31668873-34329377, e48b7dcc-a742-11e8-a035-525400eb3467:10-1091248, e950f041-a742-11e8-a035-525400764e09:4676-38377703 Executed_Gtid_Set: e37d51d8-f5f3-11e7-9c02-5254002a5bf5:31668873-34329377, e48b7dcc-a742-11e8-a035-525400eb3467:10-1091247, e950f041-a742-11e8-a035-525400764e09:4676-38344952, f9141bf5-a742-11e8-a035-525400c9488f:1-10 Auto_Position: 0 1 row in set (0.00 sec)