mysql5.6主从复制第五部分[如何在从服务器中跳过一条语句/事务]

mysql5.6主从复制第五部分[如何在从服务器中跳过一条语句/事务]

写这篇笔记的时候，mysql已经从5.6.10发展到了5.6.12

SQL_SLAVE_SKIP_COUNTER对于GTID没用。

准备测试环境，按照 http://www.zhaokunyao.com/archives/4131配置主从。
master port 5612
slave port 5613

然后把slave设置成落后于master 600秒,便于测试：

mysql>CHANGE masterTO MASTER_DELAY=600;

ERROR 1198(HY000): This operation cannot be performed WITH a running slave; run STOP SLAVEFIRST

mysql> stop slave;

Query OK,0ROWS affected(0.05 sec)

mysql >CHANGE masterTO MASTER_DELAY=600;

Query OK,0ROWS affected(0.27 sec)

mysql>START slave;

Query OK,0ROWS affected,1 warning (0.06 sec)

master 原本是正常的，然后意外地执行了 truncate table:

mysql >INSERTINTO tSET title='c';

Query OK,1ROW affected(0.03 sec)

mysql >INSERTINTO tSET title='d';

Query OK,1ROW affected(0.05 sec)

mysql >SHOW masterSTATUS \G

***************************1.ROW***************************

File: black-bin.000001

POSITION:2817

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set: 0c005b76-d3c7-11e2-a27d-274c063b18c4:1-10

1ROWINSET(0.00 sec)

 

mysql >TRUNCATETABLE t;

Query OK,0ROWS affected(0.15 sec)

Mysql >SHOW masterSTATUS \G

***************************1.ROW***************************

File: black-bin.000001

POSITION:2948

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set: 0c005b76-d3c7-11e2-a27d-274c063b18c4:1-11

1ROWINSET(0.00 sec)

 

slave有延迟，虽然已经获取到了gtid及对应的events,但是并未执行：

mysql >SHOW slaveSTATUS \G

***************************1.ROW***************************

               Slave_IO_State: WaitingFOR masterTO send event

.......

.......

                    SQL_Delay:600

          SQL_Remaining_Delay:565

      Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event

           Master_Retry_Count:86400

                  Master_Bind:

      Last_IO_Error_Timestamp:

     Last_SQL_Error_Timestamp:

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set: 0c005b76-d3c7-11e2-a27d-274c063b18c4:9-11

            Executed_Gtid_Set: 0c005b76-d3c7-11e2-a27d-274c063b18c4:1-8

                Auto_Position:1

1ROWINSET(0.00 sec)

要想办法在slave中跳过0c005b76-d3c7-11e2-a27d-274c063b18c4:11，也就是那条truncate table语句。
办法就是设置GTID_NEXT，然后提交一个空的事务。

mysql > stop slave;

mysql >SET gtid_next='0c005b76-d3c7-11e2-a27d-274c063b18c4:11';

mysql >BEGIN; commit;

mysql >SET GTID_NEXT='AUTOMATIC";

mysql >START slave;

mysql >SHOW slaveSTATUS \G

***************************1.ROW***************************

           Retrieved_Gtid_Set: 0c005b76-d3c7-11e2-a27d-274c063b18c4:9-11

            Executed_Gtid_Set: 0c005b76-d3c7-11e2-a27d-274c063b18c4:1-8:11

过一段时间之后：

mysql >SHOW slaveSTATUS \G

***************************1.ROW***************************

               Slave_IO_State: WaitingFOR masterTO send event

.......

.......

            Retrieved_Gtid_Set: 0c005b76-d3c7-11e2-a27d-274c063b18c4:9-11

            Executed_Gtid_Set: 0c005b76-d3c7-11e2-a27d-274c063b18c4:1-11

                Auto_Position:1

1ROWINSET(0.00 sec)

mysql >SELECT*FROM t;

+----+-------+

| id| title|

+----+-------+

| 1| a;

   |

| 2| b    |

| 3| c    |

| 4| d    |

+----+-------+

4ROWSINSET(0.00 sec)

成功跳过 truncate table,当然此时主从的数据已经不一致了。

参考：

http://www.zhaokunyao.com/archives/4131

http://www.mysqlperformanceblog.com/2013/03/26/repair-mysql-5-6-gtid-replication-by-injecting-empty-transactions/