寫這篇筆記的時候，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 [localhost] {msandbox} (test) > CHANGE master TO MASTER_DELAY=600;
ERROR 1198 (HY000): This operation cannot be performed WITH a running slave; run STOP SLAVE FIRST
mysql [localhost] {msandbox} (test) >
mysql [localhost] {msandbox} (test) > stop slave;
Query OK, 0 ROWS affected (0.05 sec)
mysql [localhost] {msandbox} (test) > CHANGE master TO MASTER_DELAY=600;
Query OK, 0 ROWS affected (0.27 sec)
mysql [localhost] {msandbox} (test) > START slave;
Query OK, 0 ROWS affected, 1 warning (0.06 sec)
master 原本是正常的， 然後意外地執行了 truncate table:
mysql [localhost] {msandbox} (test) > INSERT INTO t SET title='c';
Query OK, 1 ROW affected (0.03 sec)
mysql [localhost] {msandbox} (test) > INSERT INTO t SET title='d';
Query OK, 1 ROW affected (0.05 sec)
mysql [localhost] {msandbox} (test) > SHOW master STATUS \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
1 ROW IN SET (0.00 sec)
mysql [localhost] {msandbox} (test) > TRUNCATE TABLE t;
Query OK, 0 ROWS affected (0.15 sec)
mysql [localhost] {msandbox} (test) > SHOW master STATUS \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
1 ROW IN SET (0.00 sec)
slave有延遲， 雖然已經獲取到了gtid及對應的events, 但是並未執行：
mysql [localhost] {msandbox} (test) > SHOW slave STATUS \G
*************************** 1. ROW ***************************
Slave_IO_State: Waiting FOR master TO 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
1 ROW IN SET (0.00 sec)
要想辦法在slave中跳過0c005b76-d3c7-11e2-a27d-274c063b18c4:11， 也就是那條truncate table語句 。
辦法就是設置GTID_NEXT，然後提交一個空的事務。
mysql [localhost] {msandbox} (test) >
mysql [localhost] {msandbox} (test) > stop slave;
Query OK, 0 ROWS affected (0.03 sec)
mysql [localhost] {msandbox} (test) > SET gtid_next='0c005b76-d3c7-11e2-a27d-274c063b18c4:11';
Query OK, 0 ROWS affected (0.00 sec)
mysql [localhost] {msandbox} (test) > BEGIN; commit;
Query OK, 0 ROWS affected (0.00 sec)
Query OK, 0 ROWS affected (0.01 sec)
mysql [localhost] {msandbox} (test) > SET GTID_NEXT='AUTOMATIC";
'> ';
ERROR 1231 (42000): Variable 'gtid_next' can't be SET TO the VALUE OF 'AUTOMATIC";
'
mysql [localhost] {msandbox} (test) > SET GTID_NEXT='AUTOMATIC';
Query OK, 0 ROWS affected (0.00 sec)
mysql [localhost] {msandbox} (test) > START slave;
Query OK, 0 ROWS affected, 1 warning (0.07 sec)
mysql [localhost] {msandbox} (test) > SHOW slave STATUS \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 [localhost] {msandbox} (test) > SHOW slave STATUS \G
*************************** 1. ROW ***************************
Slave_IO_State: Waiting FOR master TO 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
1 ROW IN SET (0.00 sec)
mysql [localhost] {msandbox} (test) > SELECT * FROM t;
+----+-------+
| id | title |
+----+-------+
| 1 | a;
|
| 2 | b |
| 3 | c |
| 4 | d |
+----+-------+
4 ROWS IN SET (0.00 sec)
成功跳過 truncate table, 當然此時主從的數據已經不一致了。
