MySQL GTID 主从复制错误修复方法
MySQL传统复制恢复方法:
root@(none) >stop slave; Query OK, 0 rows affected (0.00 sec) root@(none) >SET GLOBAL SQL_SLAVE_SKIP_COUNTER = N; #跳过N个事务 Query OK, 0 rows affected (0.00 sec) root@(none) >start slave; Query OK, 0 rows affected, 1 warning (0.03 sec)
如上述方法在GTID模式下执行会报错,如下:
ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction
示例:
root@(none) >stop slave; Query OK, 0 rows affected (0.00 sec) root@(none) >set global sql_slave_skip_counter = 1; ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction
依据错误提示,在发生错误的GTID事务号上插入一个空事务,实现跳过错误,方法如下:
root@(none) >show slave status\G *************************** 1. row *************************** Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Cannot replicate anonymous transaction when AUTO_POSITION = 1, at file /log/mysqlbin/mysql_bin.000012, position 194.; the first event '' at 4, the last event read from '/log/mysqlbin/mysql_bin.000012' at 259, the last byte read from '/log/mysqlbin/mysql_bin.000012' at 259.' ... Executed_Gtid_Set: 291f68c6-a5f4-11ed-a531-0242ac110003:1-170840
找到目前从库复制错误的GTID进行空事务插入
stop slave; # 关闭 slave 复制 SET GTID_NEXT='$GTID_NO'; # 设置 GTID_NEXT 为复制出错的 GTID_NO begin; commit; # 向 $GTID_NO 写入空事务 SET GTID_NEXT="AUTOMATIC"; # 重新设置 GTID_NEXT 为自动获取 start slave; # 启动 slave 复制
示例如下:
root@(none) >stop slave; Query OK, 0 rows affected (0.00 sec) root@(none) >SET GTID_NEXT='291f68c6-a5f4-11ed-a531-0242ac110003:170840'; Query OK, 0 rows affected (0.00 sec) root@(none) >begin; Query OK, 0 rows affected (0.00 sec) root@(none) >commit; Query OK, 0 rows affected (0.00 sec) root@(none) >SET GTID_NEXT="AUTOMATIC"; Query OK, 0 rows affected (0.00 sec) root@(none) >start slave; Query OK, 0 rows affected, 1 warning (0.03 sec) 再次验证 show slave status\G 双线程YES,Seconds_Behind_Master无延迟,error部分消除,基于GTID复制修复完成。 注意:在设置下个事务时取gtid的信息对应为uuid:tid (某个实例的事务号),而非某个实例的一组tid,如下: SET GTID_NEXT='291f68c6-a5f4-11ed-a531-0242ac110003:1-170840'; × SET GTID_NEXT='291f68c6-a5f4-11ed-a531-0242ac110003:170840';√标签:主从复制,slave,none,OK,MySQL,affected,root,GTID From: https://www.cnblogs.com/arua-cc719720/p/17480744.html