526互联

MySQL GTID 主从复制错误修复方法

发布时间 2023-06-14 17:00:45作者: 阿rua

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';√