gtid复制中跳过错误事务的另一种方法

gtid复制中跳过错误事务的另一种方法

replica1 > select @@gtid_mode;
+---------------------+
| @@gtid_mode         |
+---------------------+
|  on                 |
+---------------------+
replica1 > stop slave;
query ok, 0 rows affected (0.02 sec)
 
replica1 > set gtid_mode=on_permissive;
query ok, 0 rows affected (0.04 sec)
 
replica1 > set global sql_slave_skip_counter = 1;
query ok, 0 rows affected (0.00 sec)
 
replica1 > start slave;
query ok, 0 rows affected (0.00 sec)
 
replica1 > pager grep seconds
pager set to 'grep seconds'
replica1 > show slave status\g
        seconds_behind_master: 0
1 row in set (0.00 sec)

关闭主从复制，并将gtid_mode设置成on_permissive(新的事务是gtid事务，复制事务可以是匿名事务、也可以是gtid事务)。使用sql_slave_skip_counter=1跳过事务，然后重启复制。

一旦复制同步后，我们就需要将gtid_mode改会on。

replica1 > select @@gtid_mode;
+---------------------- ----+
| @@gtid_mode               |
+---------------------------+
| on_permissive             |
+---------------------------+
 
replica1 > set global gtid_mode=on;
query ok, 0 rows affected (0.04 sec)

要想将gtid_mode从on设置成on_permissive，必须将sql_slave_skip_counter设置成0。

replica1 > stop slave;
query ok, 0 rows affected (0.02 sec)
 
replica1 > set gtid_mode=on_permissive;
query ok, 0 rows affected (0.04 sec)
 
replica1 > set global sql_slave_skip_counter = 1;
query ok, 0 rows affected (0.00 sec)
 
replica1 > set global gtid_mode=on;
error 3111 (hy000): set @@global.gtid_mode = on is not allowed because @@global.sql_slave_skip_counter is greater than zero.
 
replica1 > start slave;
query ok, 0 rows affected (0.00 sec)
 
replica1 > set global gtid_mode=on;
query ok, 0 rows affected (0.02 sec)

这样跳过后，主从可能会不一致，这就需要使用pt-table-checksum、pt-table-sync来修复一致性了。