MySQL GTID是在傳統的mysql主從復制的基礎之上演化而來的產物，即通過UUID加上事務ID的方式來確保每一個事物的唯一性。這樣的操作方式使得我們不再需要關心所謂的log_file和log_Pos，只是簡單的告訴從庫，從哪個服務器上去找主庫就OK了。簡化了主從的搭建以及failover的過程，同時比傳統的復制更加安全可靠。由于GTID是連續沒有空洞的，因此主從庫出現數據沖突時，可以通過注入空事物的方式進行跳過。本文主要講述GTID主從架構的錯誤處理方式。

一、GTID的相關特性

配置MySQL GTID 主從復制
基于mysqldump搭建gtid主從

二、GTID如何跳過事務沖突

    很多無法預料的情形導致mysql主從發生事務沖突，主從失敗或停止的情形，即需要修復主從      對于GTID方式的主從架構而言，更多的是處理事務沖突來修復主從      GTID不支持通過傳統設置sql_slave_skip_counter方法來跳過事務      方法：通過注入空事務來填補事務空洞，等同于傳統復制的(set global sql_slave_skip_counter = 1)      步驟：              stop slave；              set gtid_next='xxxxxxx:N'; --指定下一個事務執行的版本，即想要跳過的GTID              begin;              commit;  --注入一個空事物              set gtid_next='AUTOMATIC' --自動的尋找GTID事務。              start slave; --開始同步

三、GTID事務沖突的幾種常見類型

    1、主庫新增記錄，從庫提示主鍵沖突      2、主庫對象可更新，從庫無對應的對象可更新      3、主庫對象可刪除，從庫無對應的對象可刪除      4、通過延遲從修復主庫意外刪除的對象      5、主庫日志被purged的情形

四、示例演示

當前演示的主從架構圖  #?mysqlrplshow?--master=root:pass@192.168.1.233:3306?--discover-slaves-login=root:pass?--verboseWARNING:?Using?a?password?on?the?command?line?interface?can?be?insecure.  #?master?on?192.168.1.233:?...?connected.  #?Finding?slaves?for?master:?192.168.1.233:3306    #?Replication?Topology?Graph  192.168.1.233:3306?(MASTER)???|???+---?192.168.1.245:3306?[IO:?Yes,?SQL:?Yes]?-?(SLAVE)???|???+---?192.168.1.247:3306?[IO:?Yes,?SQL:?Yes]?-?(SLAVE)(root@192.168.1.233)[tempdb]&gt;show?slave?hosts;+-----------+---------------+------+-----------+--------------------------------------+|?Server_id?|?Host??????????|?Port?|?Master_id?|?Slave_UUID???????????????????????????|  +-----------+---------------+------+-----------+--------------------------------------+|???????245?|?192.168.1.245?|?3306?|???????233?|?78336cdc-8cfb-11e6-ba9f-000c29328504?||???????247?|?192.168.1.247?|?3306?|???????233?|?13a26fc1-555a-11e6-b5e0-000c292e1642?|  +-----------+---------------+------+-----------+--------------------------------------+--演示的mysql版本  (root@192.168.1.233)[tempdb]&gt;show?variables?like?'version';+---------------+------------+|?Variable_name?|?Value??????|  +---------------+------------+|?version???????|?5.7.12-log?|  +---------------+------------+--查看gtid是否開啟  (root@192.168.1.233)[tempdb]&gt;show?variables?like?'%gtid_mode%';+---------------+-------+|?Variable_name?|?Value?|  +---------------+-------+|?gtid_mode?????|?ON????|  +---------------+-------+--主庫上面可以看到基于gtid的dump線程，如下  (root@192.168.1.233)[tempdb]&gt;show?processlist;+----+------+-----------------------+--------+------------------+------+|?Id?|?User?|?Host??????????????????|?db?????|?Command??????????|?Time?|  +----+------+-----------------------+--------+------------------+------+|?17?|?repl?|?node245.edq.com:52685?|?NULL???|?Binlog?Dump?GTID?|?2738?|  |?18?|?repl?|?node247.edq.com:33516?|?NULL???|?Binlog?Dump?GTID?|?2690?||?24?|?root?|?localhost?????????????|?tempdb?|?Query????????????|????0?|  +----+------+-----------------------+--------+------------------+------+

1、從庫報主鍵重復(Errno: 1062)

(root@Master)[tempdb]&gt;create?table?t1?(  ????????????-&gt;?id?tinyint?not?null?primary?key,ename?varchar(20),blog?varchar(50));    (root@Master)[tempdb]&gt;insert?into?t1?  ????????????-&gt;?values(1,'leshami','http://blog.csdn.net/leshami');    (root@Master)[tempdb]&gt;insert?into?t1?  ????????????-&gt;?values(2,'robin','http://blog.csdn.net/robinson_0612');    (root@Master)[tempdb]&gt;set?sql_log_bin=off;    (root@Master)[tempdb]&gt;delete?from?t1?where?ename='robin';    (root@Master)[tempdb]&gt;set?sql_log_bin=on;    (root@Master)[tempdb]&gt;insert?into?t1?  ????????????-&gt;?values(2,'robin','http://blog.csdn.net/robinson_0612');    --?從庫狀態報錯，提示重復的primary?key  (root@Slave)[tempdb]&gt;show?slave?status?G  ***************************?1.?row?***************************Last_Errno:?1062Last_Error:?Could?not?execute?Write_rows?event?on?table?tempdb.t1;?Duplicate?entry?'2'?for?key?'PRIMARY',?  ????????????????????????Error_code:?1062;?handler?error?HA_ERR_FOUND_DUPP_KEY;?  ????????????????????????the?event's?master?log?node233-binlog.000004,?end_log_pos?4426  Retrieved_Gtid_Set:?1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:76-90  ?Executed_Gtid_Set:?1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-89  ?????Auto_Position:?1    --?如下解決方案，可以通過刪除重庫的這條記錄  (root@Slave)[tempdb]&gt;stop?slave;    (root@Slave)[tempdb]&gt;delete?from?t1?where?ename='robin';    (root@Slave)[tempdb]&gt;start?slave;    (root@Slave)[tempdb]&gt;show?slave?status?G  ***************************?1.?row?***************************  ???????????Retrieved_Gtid_Set:?1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:76-90  ????????????Executed_Gtid_Set:?1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-90,  ?78336cdc-8cfb-11e6-ba9f-000c29328504:1??--這里多了一個GTID，注意這個是從庫上執行的，這里的UUID跟IP?245的UUID一致  ????????????????Auto_Position:?1  ?????????Replicate_Rewrite_DB:?  ?????????????????Channel_Name:?  ???????????Master_TLS_Version:?    (root@Slave)[tempdb]&gt;show?variables?like?'%uuid%';  +---------------+--------------------------------------+  |?Variable_name?|?Value????????????????????????????????|  +---------------+--------------------------------------+  |?server_uuid???|?78336cdc-8cfb-11e6-ba9f-000c29328504?|  +---------------+--------------------------------------+

2、從庫報找不到對應的被更新的記錄(Errno: 1032)

--首先在從庫上刪除leshami這條記錄  (root@Slave)[tempdb]&gt;delete?from?t1?where?ename='leshami';    --接下來再主庫嘗試更新leshami這條記錄  (root@Master)[tempdb]&gt;update?t1?set?  ????????????-&gt;?blog='http://blog.csdn.net/robinson_0612'?where?ename='leshami';Query?OK,?1?row?affected?(0.02?sec)  Rows?matched:?1??Changed:?1??Warnings:?0    --?查看從庫狀態  (root@Slave)[tempdb]&gt;show?slave?status?G***************************?1.?row?***************************Last_SQL_Errno:?1032  Last_SQL_Error:?Could?not?execute?Update_rows?event?on?table?tempdb.t1;?Can't?find?record?in?'t1',????????????????????????????????Error_code:?1032;?handler?error?HA_ERR_KEY_NOT_FOUND;????????????????????????????the?event's?master?log?node233-binlog.000004,?end_log_pos?4769Retrieved_Gtid_Set:?1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:76-91  Executed_Gtid_Set:?1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-90,????????78336cdc-8cfb-11e6-ba9f-000c29328504:1-2--?通過mysqlbinlog在主服務器上尋找報錯的binglog日志文件及位置，找到對應的SQL語句，如下所示  --?update中的where后面的部分為更新前的數據，set部分為更新后的數據，因此可以將更新前的數據插入到從庫#?mysqlbinlog?--no-defaults?-v?-v?--base64-output=DECODE-ROWS?/data/node233-binlog.000004|grep?-A?'10'?4769#161009?13:46:34?server?id?233?end_log_pos?4769?CRC32?0xb60df74e?Update_rows:?table?id?147?flags:?STMT_END_F###?UPDATE?`tempdb`.`t1`###?WHERE###???@1=1?/*?TINYINT?meta=0?nullable=0?is_null=0?*/###???@2='leshami'?/*?VARSTRING(20)?meta=20?nullable=1?is_null=0?*/###???@3='http://blog.csdn.net/leshami'?/*?VARSTRING(50)?meta=50?nullable=1?is_null=0?*/###?SET###???@1=1?/*?TINYINT?meta=0?nullable=0?is_null=0?*/###???@2='leshami'?/*?VARSTRING(20)?meta=20?nullable=1?is_null=0?*/###???@3='http://blog.csdn.net/robinson_0612'?/*?VARSTRING(50)?meta=50?nullable=1?is_null=0?*/#?at?4769#161009?13:46:34?server?id?233??end_log_pos?4800?CRC32?0xa9669811???????Xid?=?1749COMMIT/*!*/;  SET?@@SESSION.GTID_NEXT=?'AUTOMATIC'?/*?added?by?mysqlbinlog?*/?/*!*/;  DELIMITER?;#?End?of?log?file/*!50003?SET?COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;  /*!50530?SET?@@SESSION.PSEUDO_SLAVE_MODE=0*/;???    (root@Slave)[tempdb]&gt;select?*?from?t1;  +----+-------+------------------------------------+  |?id?|?ename?|?blog???????????????????????????????|  +----+-------+------------------------------------+  |??2?|?robin?|?http://www.php.cn/?|  +----+-------+------------------------------------+    (root@Slave)[tempdb]&gt;stop?slave?sql_thread;    (root@Slave)[tempdb]&gt;insert?into?t1?values(1,'leshami','http://blog.csdn.net/leshami');    (root@Slave)[tempdb]&gt;start?slave?sql_thread;    (root@Slave)[tempdb]&gt;show?slave?status?G***************************?1.?row?***************************???????????Retrieved_Gtid_Set:?1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:76-91????????????Executed_Gtid_Set:?1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-91,???????????????????????????????78336cdc-8cfb-11e6-ba9f-000c29328504:1-3????????????????Auto_Position:?1

3、從庫找不到對應的被刪除的記錄(Errno: 1032)

--?如果是在主庫上刪除記錄，而從庫上找不到對應的記錄，則可以直接跳過該事務  --?下面我們首選在從庫上刪除一條記錄  (root@Slave)[tempdb]&gt;delete?from?t1?where?ename='robin';    --?接下來在主庫上刪除該記錄  (root@Master)[tempdb]&gt;delete?from?t1?where?ename='robin';    --?從庫端提示無法找到對應的記錄  (root@Slave)[tempdb]&gt;show?slave?status?G***************************?1.?row?***************************Last_SQL_Error:?Could?not?execute?Delete_rows?event?on?table?tempdb.t1;?Can't?find?record?in?'t1',????????????????Error_code:?1032;?handler?error?HA_ERR_KEY_NOT_FOUND;?????????????????the?event's?master?log?node233-binlog.000004,?end_log_pos?5070Last_SQL_Error_timestamp:?161009?15:08:06????Master_SSL_Crl:?Master_SSL_Crlpath:?  Retrieved_Gtid_Set:?1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:76-92  ?Executed_Gtid_Set:?1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-91,????????????????????78336cdc-8cfb-11e6-ba9f-000c29328504:1-4?????Auto_Position:?1??????--?下面通過注入空事務來跳過  (root@Slave)[tempdb]&gt;stop?slave?sql_thread;    (root@Slave)[tempdb]&gt;set?gtid_next='1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:92';    (root@Slave)[tempdb]&gt;begin;commit;    (root@Slave)[tempdb]&gt;set?gtid_next='AUTOMATIC';    (root@Slave)[tempdb]&gt;start?slave?sql_thread;    (root@Slave)[tempdb]&gt;show?slave?status?G***************************?1.?row?***************************???????????Retrieved_Gtid_Set:?1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:76-92????????????Executed_Gtid_Set:?1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-92,???????????????????????????????78336cdc-8cfb-11e6-ba9f-000c29328504:1-4????????????????Auto_Position:?1?????????Replicate_Rewrite_DB:??????????????????Channel_Name:????????????Master_TLS_Version:

4、延遲從修復主庫意外truncate

--?主庫上面新增表及記錄?????????????  (root@Master)[tempdb]&gt;create?table?t2?(id?tinyint?not?null?primary?key,?  ????????-&gt;?ename?varchar(20),blog?varchar(50));(root@Master)[tempdb]&gt;insert?into?t2??  ????????????-&gt;?values(1,'leshami','http://blog.csdn.net/leshami');(root@Master)[tempdb]&gt;insert?into?t2??  ????????????-&gt;?values(2,'robin','http://blog.csdn.net/robinson_0612');(root@Master)[tempdb]&gt;select?*?from?t2;  +----+---------+------------------------------------+  |?id?|?ename???|?blog???????????????????????????????|  +----+---------+------------------------------------+  |??1?|?leshami?|?http://www.php.cn/???????|  |??2?|?robin???|?http://www.php.cn/?|  +----+---------+------------------------------------+    --先將從庫配置為延遲從  (root@Slave)[tempdb]&gt;stop?slave?sql_thread;  Query?OK,?0?rows?affected?(0.01?sec)    (root@Slave)[tempdb]&gt;CHANGE?MASTER?TO?MASTER_DELAY?=?300;  Query?OK,?0?rows?affected?(0.00?sec)    (root@Slave)[tempdb]&gt;start?slave?sql_thread;  Query?OK,?0?rows?affected?(0.02?sec)    (root@Slave)[tempdb]&gt;show?slave?status?G***************************?1.?row?***************************?????????????Slave_IO_Running:?Yes????????????Slave_SQL_Running:?Yes????????????????????SQL_Delay:?300??root@Slave)[tempdb]&gt;show?slave?status?G***************************?1.?row?***************************???????????Retrieved_Gtid_Set:?1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:76-99????????????Executed_Gtid_Set:?1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-99,???????????????????????????????78336cdc-8cfb-11e6-ba9f-000c29328504:1-4????????????????Auto_Position:?1--查看主庫上的binglog?gtid  (root@Master)[tempdb]&gt;show?master?statusG***************************?1.?row?***************************?????????????File:?node233-binlog.000004?????????Position:?6970?????Binlog_Do_DB:?  ?Binlog_Ignore_DB:?  Executed_Gtid_Set:?1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-99  1?row?in?set?(0.00?sec)    --在主庫上truncate?t2  (root@Master)[tempdb]&gt;truncate?table?t2;  Query?OK,?0?rows?affected?(0.03?sec)    --再次查看主庫上的binglog?gtid，有99變成了100，這個100即是我們需要跳過的ID  (root@Master)[tempdb]&gt;show?master?statusG***************************?1.?row?***************************?????????????File:?node233-binlog.000004?????????Position:?7121?????Binlog_Do_DB:?  ?Binlog_Ignore_DB:?  Executed_Gtid_Set:?1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-100  1?row?in?set?(0.00?sec)    --從庫上跳過被意外truncate的事務  (root@Slave)[tempdb]&gt;stop?slave?sql_thread;  Query?OK,?0?rows?affected?(0.01?sec)    (root@Slave)[tempdb]&gt;set?gtid_next='1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:100';  Query?OK,?0?rows?affected?(0.00?sec)    (root@Slave)[tempdb]&gt;begin;commit;  Query?OK,?0?rows?affected?(0.00?sec)    Query?OK,?0?rows?affected?(0.01?sec)    (root@Slave)[tempdb]&gt;set?gtid_next='AUTOMATIC';  Query?OK,?0?rows?affected?(0.00?sec)    (root@Slave)[tempdb]&gt;start?slave?sql_thread;  Query?OK,?0?rows?affected?(0.02?sec)    (root@Slave)[tempdb]&gt;show?slave?status?G***************************?1.?row?***************************???????????????Slave_IO_State:?Waiting?for?master?to?send?event??????????????????Master_Host:?Master??????????????????Master_User:?repl??????????????????Master_Port:?3306????????????????Connect_Retry:?60??????????????Master_Log_File:?node233-binlog.000004??????????Read_Master_Log_Pos:?7121???????????????Relay_Log_File:?node245-relay-bin.000003????????????????Relay_Log_Pos:?2982????????Relay_Master_Log_File:?node233-binlog.000004?????????????Slave_IO_Running:?Yes????????????Slave_SQL_Running:?Yes?????????????...........................????????????????????Retrieved_Gtid_Set:?1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:76-100????????????Executed_Gtid_Set:?1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-100,?????????????????????????????????????????????????????????????78336cdc-8cfb-11e6-ba9f-000c29328504:1-4????????????????Auto_Position:?1--?很多時候我們并不知道表何時被truncate，因此可以從binlog日志得到其gtid  --?如下所示，可以得到這串?SET?@@SESSION.GTID_NEXT=?'1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:100'  --?100即為這個truncate對應的gtid的事務號#?mysqlbinlog?--no-defaults?-v?-v?--base64-output=DECODE-ROWS?/data/node233-binlog.000004|grep?-i?&gt;?"truncate?table?t2"?-A3?-B10??###???@3='http://blog.csdn.net/robinson_0612'?/*?VARSTRING(50)?meta=50?nullable=1?is_null=0?*/#?at?6939#161009?18:04:58?server?id?233??end_log_pos?6970?CRC32?0x71c5121c?????Xid?=?1775COMMIT/*!*/;#?at?6970#161009?18:08:42?server?id?233?end_log_pos?7035?CRC32?0x00ba9437?GTID?last_committed=26?sequence_number=27SET?@@SESSION.GTID_NEXT=?'1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:100'/*!*/;#?at?7035#161009?18:08:42?server?id?233?end_log_pos?7121?CRC32?0x5a8b9723?Query?thread_id=26?exec_time=0?error_code=0SET?TIMESTAMP=1476007722/*!*/;  truncate?table?t2  /*!*/;  SET?@@SESSION.GTID_NEXT=?'AUTOMATIC'?/*?added?by?mysqlbinlog?*/?/*!*/;  DELIMITER?;

5、主庫binlog被purge的情形(Errno: 1236)

--?首先停止從庫，模擬從庫被意外宕機  (root@Slave)[tempdb]&gt;stop?slave;  Query?OK,?0?rows?affected?(0.08?sec)    --在主庫上進行相應的操作  --此時主庫上的gtid_purged為空  (root@Master)[tempdb]&gt;show?variables?like?'%gtid_purged%';  +---------------+-------+  |?Variable_name?|?Value?|  +---------------+-------+  |?gtid_purged???|???????|  +---------------+-------+    --查看主庫binlog  (root@Master)[tempdb]&gt;show?binary?logs;  +-----------------------+-----------+  |?Log_name??????????????|?File_size?|  +-----------------------+-----------+  |?node233-binlog.000001?|???1362104?|  |?node233-binlog.000002?|??????1331?|  |?node233-binlog.000003?|???????217?|  |?node233-binlog.000004?|??????7121?|  +-----------------------+-----------+    (root@Master)[tempdb]&gt;select?*?from?t1;  +----+---------+------------------------------------+  |?id?|?ename???|?blog???????????????????????????????|  +----+---------+------------------------------------+  |??1?|?leshami?|?http://www.php.cn/?|  |??2?|?robin???|?http://www.php.cn/???????|  +----+---------+------------------------------------+    --從主庫刪除記錄  (root@Master)[tempdb]&gt;delete?from?t1;    --切換日志  (root@Master)[tempdb]&gt;flush?logs;    --新增記錄  (root@Master)[tempdb]&gt;insert?into?t1?values(1,????-&gt;?'xuputi','http://blog.csdn.net/leshami');(root@Master)[tempdb]&gt;show?binary?logs;  +-----------------------+-----------+  |?Log_name??????????????|?File_size?|  +-----------------------+-----------+  |?node233-binlog.000001?|???1362104?|  |?node233-binlog.000002?|??????1331?|  |?node233-binlog.000003?|???????217?|  |?node233-binlog.000004?|??????7513?|  |?node233-binlog.000005?|???????490?|  +-----------------------+-----------+    --清理binlog  (root@Master)[tempdb]&gt;purge?binary?logs?to?'node233-binlog.000005';  Query?OK,?0?rows?affected?(0.01?sec)    (root@Master)[tempdb]&gt;show?binary?logs;  +-----------------------+-----------+  |?Log_name??????????????|?File_size?|  +-----------------------+-----------+  |?node233-binlog.000005?|???????490?|  +-----------------------+-----------+    --此時可以看到相應的gtid_purged值  (root@Master)[tempdb]&gt;show?variables?like?'%gtid_purged%';  +---------------+--------------------------------------------+  |?Variable_name?|?Value??????????????????????????????????????|  +---------------+--------------------------------------------+  |?gtid_purged???|?1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-101?|  +---------------+--------------------------------------------+    --下面啟動從庫  (root@Slave)[tempdb]&gt;start?slave;  Query?OK,?0?rows?affected?(0.00?sec)    --從庫狀態提示有日志被purged  (root@Slave)[tempdb]&gt;show?slave?statusG***************************?1.?row?***************************???????????????Slave_IO_State:???????????????????Master_Host:?Master??????????????????Master_User:?repl??????????????????Master_Port:?3306????????????????Connect_Retry:?60??????????????Master_Log_File:?node233-binlog.000004??????????Read_Master_Log_Pos:?7121???????????????Relay_Log_File:?node245-relay-bin.000003????????????????Relay_Log_Pos:?3133????????Relay_Master_Log_File:?node233-binlog.000004?????????????Slave_IO_Running:?No????????????Slave_SQL_Running:?Yes????????????????????...............????????????????Last_IO_Errno:?1236????????????????Last_IO_Error:?Got?fatal?error?1236?from?master?when?reading?data?from?binary?log:????????????????'The?slave?is?connecting?using?CHANGE?MASTER?TO?MASTER_AUTO_POSITION?=?1,??????????????????but?the?master?has?purged?binary?logs?containing?GTIDs?that?the?slave?requires.'???????????????????????..................???????????Retrieved_Gtid_Set:?1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:76-100????????????Executed_Gtid_Set:?1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-100,???????????????????????????????78336cdc-8cfb-11e6-ba9f-000c29328504:1-4????????????????Auto_Position:?1--?從庫上gtid_purged參數，此時為75  (root@Slave)[tempdb]&gt;show?variables?like?'%gtid_purged%';  +---------------+-------------------------------------------+  |?Variable_name?|?Value?????????????????????????????????????|  +---------------+-------------------------------------------+  |?gtid_purged???|?1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-75?|  +---------------+-------------------------------------------+????????????????    --停止從庫  (root@Slave)[tempdb]&gt;stop?slave;  Query?OK,?0?rows?affected?(0.01?sec)    --下面嘗試使用gtid_purged進行跳過事務，，如下，提示僅僅當GLOBAL.GTID_EXECUTED為空才能被設置  (root@Slave)[tempdb]&gt;set?global?gtid_purged?=?'1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-101';  ERROR?1840?(HY000):?@@GLOBAL.GTID_PURGED?can?only?be?set?when?@@GLOBAL.GTID_EXECUTED?is?empty.    --如下查看，已經存在被執行的gtid，即gtid_executed肯定是不為空，且這些gtid記錄在從庫的binary?log中  (root@Slave)[tempdb]&gt;show?global?variables?like?'%gtid_executed%'G***************************?1.?row?***************************Variable_name:?gtid_executed????????Value:?1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-100,???????????????78336cdc-8cfb-11e6-ba9f-000c29328504:1-4***************************?2.?row?***************************Variable_name:?gtid_executed_compression_period????????Value:?1000--下面我們在從庫上reset?master，即清空從庫binlog  (root@Slave)[tempdb]&gt;reset?master;  Query?OK,?0?rows?affected?(0.05?sec)    --再次查看gtid_executed已經為空值  (root@Slave)[tempdb]&gt;show?global?variables?like?'%gtid_executed%'G***************************?1.?row?***************************Variable_name:?gtid_executed????????Value:?***************************?2.?row?***************************Variable_name:?gtid_executed_compression_period????????Value:?1000--此時再次設置gtid_purged的值  (root@Slave)[tempdb]&gt;set?global?gtid_purged?=?'1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-101';  Query?OK,?0?rows?affected?(0.01?sec)    --啟動從庫  (root@Slave)[tempdb]&gt;start?slave;  Query?OK,?0?rows?affected?(0.03?sec)    --提示有重復記錄，如下所示  --是由于我們在從庫停止期間delete這個事務沒有被從庫的relay?log接受到  --其次主從的binlog又被purged，而且從庫啟動后，執行了gtid_purged，因此主庫上新增的記錄在從庫上提示主鍵重復  (root@Slave)[tempdb]&gt;show?slave?status?G***************************?1.?row?***************************???????????????Slave_IO_State:?Waiting?for?master?to?send?event??????????????????Master_Host:?Master??????????????????Master_User:?repl??????????????????Master_Port:?3306????????????????Connect_Retry:?60??????????????Master_Log_File:?node233-binlog.000005??????????Read_Master_Log_Pos:?490???????????????Relay_Log_File:?node245-relay-bin.000004????????????????Relay_Log_Pos:?417????????Relay_Master_Log_File:?node233-binlog.000005?????????????Slave_IO_Running:?Yes????????????Slave_SQL_Running:?No????????????????................???????????????Last_SQL_Error:?Could?not?execute?Write_rows?event?on?table?tempdb.t1;?  ?Duplicate?entry?'1'?for?key?'PRIMARY',?Error_code:?1062;  ?handler?error?HA_ERR_FOUND_DUPP_KEY;?the?event's?master?log?node233-binlog.000005,?end_log_pos?459???????????Retrieved_Gtid_Set:?1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:76-100:102????????????Executed_Gtid_Set:?1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-101????????????????Auto_Position:?1--在從庫上刪除id為1的記錄  (root@Slave)[tempdb]&gt;delete?from?t1?where?id=1;  Query?OK,?1?row?affected?(0.05?sec)    --啟動從庫的sql_thread線程  (root@Slave)[tempdb]&gt;start?slave?sql_thread;  Query?OK,?0?rows?affected?(0.02?sec)    --再次查看正常  (root@Slave)[tempdb]&gt;show?slave?status?G***************************?1.?row?***************************???????????????Slave_IO_State:?Waiting?for?master?to?send?event??????????????????Master_Host:?Master??????????????????Master_User:?repl??????????????????Master_Port:?3306????????????????Connect_Retry:?60??????????????Master_Log_File:?node233-binlog.000005??????????Read_Master_Log_Pos:?490???????????????Relay_Log_File:?node245-relay-bin.000004????????????????Relay_Log_Pos:?713????????Relay_Master_Log_File:?node233-binlog.000005?????????????Slave_IO_Running:?Yes????????????Slave_SQL_Running:?Yes--上面的這個示例，主要是演示我們使用gtid_purged方式來達到跳過事務的目的  --事實上，主從的數據已經不一致了，應根據實際的需要考慮是否進行相應的修復

五、小結

1、GTID是全局事務ID，簡化了主從架構的部署使得從庫不再需要關心log_file和log_pos
2、由于事務ID的唯一性，使得將其他從庫的GTID應用到其它從庫成為可能，即提供了便利的failover
3、GTID是連續的，非空洞性的，因此，對于沖突的情形，需要注入空的事務來實現
4、可以通過配置延遲從來避免主庫上意外的刪除對象導致的人為錯誤