写在前面：
本文主要讲解，GTID复制模式的参数配置、搭建过程中遇到的一些问题及其解决方法；针对一些常见问题场景，进行故障模拟，然后解决。
如果对GTID的其他方面的知识想要做个了解，如：GTID优点、使用限制、实现原理等，可以移步到笔者的另一篇文章 GTID模式介绍
环境：
MySQL5.6.16版本
CentOS release 6.5
主从参数配置：
对于GTID参数的含义，请参阅MySQL官网
Master配置GTID参数：
gtid_mode=on
enforce_gtid_consistency=on
log_slave_updates=1
log_bin = /home/data/mysql3306/binlog
binlog_format = row
server_id=1
... 以上参数是必须的，其他参数自行配置
重启Master，因为MySQL5.6不支持动态配置GTID
Slave配置GTID参数：
gtid_mode=on
enforce_gtid_consistency=on
log_slave_updates=1
log_bin = /home/data/mysql3306/binlog
binlog_format = row
server_id=2
relay_log = /home/data/mysql3306/relaylog
replicate_do_db=edusoho_e
（开启log_slave_updates参数，是把relay-log里的日志内容再记录到slave本地的binlog里，MySQL5.7版本以后Slave可以不开启binlog了，可以节省空间提高性能，5.6版本必须开启binlog，因为GTID存储在binlog中，只有开启binlog才能使用GTID的功能。5.7中通过GTID系统表来记录GITD(表mysql.gtid_executed)，每个事务提交时，将GTID插入到表中）
... 以上参数是必须的，其他参数自行配置
重启Slave，因为MySQL5.6不支持动态配置GTID
授权复制连接用户：
mysql> grant replication slave on *.*to repliter@'192.168.32.2' identified by PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';
Query OK, 0 rows affected, 2 warnings (0.00 sec)
配置主从复制过滤规则：
[root@slave mysql3306]# cat my.cnf
replicate_do_db=edusoho_e
准备复制数据：
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 | 151 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
创建statistic库：
mysql> create database statistic;
Query OK, 1 row affected (0.01 sec)
创建statistic.t1表：
CREATE TABLE `statistic`.`t1` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`xname` VARCHAR(20) NOT NULL DEFAULT '',
`address` CHAR(20) NOT NULL DEFAULT '',
`sex` TINYINT(1) NOT NULL DEFAULT '1',
`hobby` VARCHAR(30) NOT NULL DEFAULT '',
`age` TINYINT(2) DEFAULT '18',
PRIMARY KEY (`id`),
KEY `idx_name` (`xname`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
创建edusoho_e库：
mysql> create database edusoho_e;
Query OK, 1 row affected (0.01 sec)
创建edusoho_e.t1表：
CREATE TABLE `edusoho_e`.`t1` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`xname` VARCHAR(20) NOT NULL DEFAULT '',
`address` CHAR(20) NOT NULL DEFAULT '',
`sex` TINYINT(1) NOT NULL DEFAULT '1',
`hobby` VARCHAR(30) NOT NULL DEFAULT '',
`age` TINYINT(2) DEFAULT '18',
PRIMARY KEY (`id`),
KEY `idx_name` (`xname`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
插入测试数据：
INSERT INTO `statistic`.`t1` (`xname`, `address`, `hobby`) VALUES ('statistic', '北京', '游戏');
INSERT INTO `edusoho_e`.`t1` (`xname`, `address`, `hobby`) VALUES ('edusoho_e', '上海', '开发');
mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000001 | 2139 | | | b6af5b5c-666f-11e9-bed3-000c29b85ea6:1-6 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
导出复制数据：
[root@master mysql3306]# mysqldump -uroot -p -B edusoho_e > `date +%F`.sql（参数什么含义自己查阅文档吧）
Enter password:
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
Slave主机导入复制数据：
[root@slave mysql3306]# mysql -uroot -p < 2019-05-28.sql
Enter password:
开启数据复制：
mysql> CHANGE MASTER TO MASTER_HOST='192.168.32.3',MASTER_PORT=3306,MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected (0.02 sec)
MASTER_AUTO_POSITION=1，表示MySQl会通过内部机制自动找点同步，不在需要人工确认file和position
mysql> start slave user='repliter' password='123456';
Query OK, 0 rows affected, 1 warning (0.03 sec)
为了复制安全，不在master.info文件保存复制数据时的账号和密码
查看主从复制状态时，如果出现如下的错误，则是因为binlog_format不一致导致。
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_Errno: 1666
Last_Error: Error executing row event: 'Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.'
查看Master、Slave的binlog_format是否一致：（因为笔者的Master上binlog为ROW格式，而Slave的binlog为STATEMENT格式，所以语句不能够被执行）
SELECT @@binlog_format;
STATEMENT
修改Slave my.cnf 文件后，重启Slave数据库
再次查看主从复制状态，如果出现如下错误，则是因为Slave重启的时候，会自动开启复制状态（即 start slave，会根据master.info文件信息进行复制），因为笔者为了复制安全，在进行主从复制时才指定了账号和密码，所以出现了此错误
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_Running: No
Slave_SQL_Running: Yes
Last_IO_Errno: 1593
Last_IO_Error: Fatal error: Invalid (empty) username when attempting to connect to the master server. Connection attempt terminated.
mysql> stop slave;
Query OK, 0 rows affected (0.02 sec)
mysql> start slave user='repliter' password='123456';
Query OK, 0 rows affected, 1 warning (0.05 sec)
数据测试：
INSERT INTO `statistic`.`t1` (`xname`, `address`, `hobby`) VALUES ('GTID-statistic', '北京', '游戏');
INSERT INTO `edusoho_e`.`t1` (`xname`, `address`, `hobby`) VALUES ('GTID-edusoho_e', '上海', '开发');
至此，基于MySQL5.6的GTID复制模式搭建完成。
主从复制模式常见的一些故障：
针对复制过程中常见的1032和1062错误进行故障模拟和恢复
模拟1032（delete或update）故障，原理相通，这里只模拟delete故障：
Slave主机：
DELETE FROM `edusoho_e`.`t1` WHERE id=29;
mysql> show slave status\G;
*************************** 1. row ***************************
Master_UUID: b6af5b5c-666f-11e9-bed3-000c29b85ea6
Retrieved_Gtid_Set: b6af5b5c-666f-11e9-bed3-000c29b85ea6:1-2
Executed_Gtid_Set: a951db35-340a-11e8-b0e0-000c291e8778:1-3,
b6af5b5c-666f-11e9-bed3-000c29b85ea6:1-2
[root@slave ~]#cat auto.cnf
[auto]
server-uuid=a951db35-340a-11e8-b0e0-000c291e8778
可以看到，在Slave执行了此语句
Master主机：
DELETE FROM `edusoho_e`.`t1` WHERE id=29;
Slave主机：
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_Errno: 1032
Last_Error: Could not execute Delete_rows event on table edusoho_e.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000001, end_log_pos 1044
Exec_Master_Log_Pos: 795
在Master主机上分析日志：
[root@master mysql3306]# mysqlbinlog -v --base64-output=decode mysql-bin.000001 --start-position=795
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 795
#190518 13:46:06 server id 1 end_log_pos 843 CRC32 0x8e4fa01b GTID [commit=yes]
SET @@SESSION.GTID_NEXT= 'b6af5b5c-666f-11e9-bed3-000c29b85ea6:3'/*!*/;
# at 843
#190518 13:46:06 server id 1 end_log_pos 920 CRC32 0x757be441 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1558158366/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 920
#190518 13:46:06 server id 1 end_log_pos 981 CRC32 0x7df5817b Table_map: `edusoho_e`.`t1` mapped to number 216
# at 981
#190518 13:46:06 server id 1 end_log_pos 1044 CRC32 0xcdff2b35 Delete_rows: table id 216 flags: STMT_END_F
### DELETE FROM `edusoho_e`.`t1`
### WHERE
### @1=29
### @2='小三'
### @3='斗罗'
### @4=1
### @5='小舞'
### @6=18
# at 1044
#190518 13:46:06 server id 1 end_log_pos 1075 CRC32 0xcbc14f3a Xid = 442
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
查看Master事件：
mysql> show binlog events in 'mysql-bin.000001' from 795;
+------------------+------+-------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+-------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000001 | 795 | Gtid | 1 | 843 | SET @@SESSION.GTID_NEXT= 'b6af5b5c-666f-11e9-bed3-000c29b85ea6:3' |
| mysql-bin.000001 | 843 | Query | 1 | 920 | BEGIN |
| mysql-bin.000001 | 920 | Table_map | 1 | 981 | table_id: 216 (edusoho_e.t1) |
| mysql-bin.000001 | 981 | Delete_rows | 1 | 1044 | table_id: 216 flags: STMT_END_F |
| mysql-bin.000001 | 1044 | Xid | 1 | 1075 | COMMIT /* xid=442 */ |
+------------------+------+-------------+-----------+-------------+-------------------------------------------------------------------+
5 rows in set (0.01 sec)
在Slave上使用 sql_slave_skip_counter 语句跳过复制错误：
mysql> 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复制模式不支持 sql_slave_skip_counter 语法
根据 "show binlog events in 'mysql-bin.000001' from 795;" 结果，跳过问题GTID语句
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> set gtid_next='b6af5b5c-666f-11e9-bed3-000c29b85ea6:3';
Query OK, 0 rows affected (0.00 sec)
mysql> begin;commit;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> set gtid_next='automatic';
Query OK, 0 rows affected (0.00 sec)
mysql> start slave user='repliter' password='123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)
模拟1062（Duplicate entry）故障和1032（delete）故障：
Slave主机：
INSERT INTO `edusoho_e`.`t1` (`xname`, `address`, `hobby`) VALUES ('孙悟空', '西游记', '吃桃');
DELETE FROM `edusoho_e`.`t1` WHERE id=21;
Master主机：
INSERT INTO `edusoho_e`.`t1` (`xname`, `address`, `hobby`) VALUES ('孙悟空', '西游记', '吃桃');
DELETE FROM `edusoho_e`.`t1` WHERE id=21;
Slave主机查看主从复制状态：
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_Errno: 1062
Last_Error: Could not execute Write_rows event on table edusoho_e.t1; Duplicate entry '40' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000001, end_log_pos 1660
Exec_Master_Log_Pos: 1405
Master_UUID: b6af5b5c-666f-11e9-bed3-000c29b85ea6
Retrieved_Gtid_Set: b6af5b5c-666f-11e9-bed3-000c29b85ea6:1-6
Executed_Gtid_Set: a951db35-340a-11e8-b0e0-000c291e8778:1-5,
b6af5b5c-666f-11e9-bed3-000c29b85ea6:1-4
到Master上分析binlog日志：
[root@master mysql3306]# mysqlbinlog -v --base64-output=decode mysql-bin.000001 --start-position=1405
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 1405
#190518 14:10:11 server id 1 end_log_pos 1453 CRC32 0x82d40d30 GTID [commit=yes]
SET @@SESSION.GTID_NEXT= 'b6af5b5c-666f-11e9-bed3-000c29b85ea6:5'/*!*/;
# at 1453
#190518 14:10:11 server id 1 end_log_pos 1530 CRC32 0x705ae8d6 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1558159811/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 1530
#190518 14:10:11 server id 1 end_log_pos 1591 CRC32 0xb8b8a906 Table_map: `edusoho_e`.`t1` mapped to number 216
# at 1591
#190518 14:10:11 server id 1 end_log_pos 1660 CRC32 0x17c98361 Write_rows: table id 216 flags: STMT_END_F
### INSERT INTO `edusoho_e`.`t1`
### SET
### @1=40
### @2='孙悟空'
### @3='西游记'
### @4=1
### @5='吃桃'
### @6=18
# at 1660
#190518 14:10:11 server id 1 end_log_pos 1691 CRC32 0xec40ec8e Xid = 462
COMMIT/*!*/;
# at 1691
#190518 14:10:11 server id 1 end_log_pos 1739 CRC32 0x663e798e GTID [commit=yes]
SET @@SESSION.GTID_NEXT= 'b6af5b5c-666f-11e9-bed3-000c29b85ea6:6'/*!*/;
# at 1739
#190518 14:10:11 server id 1 end_log_pos 1816 CRC32 0x6fc29929 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1558159811/*!*/;
BEGIN
/*!*/;
# at 1816
#190518 14:10:11 server id 1 end_log_pos 1877 CRC32 0x640b0fb0 Table_map: `edusoho_e`.`t1` mapped to number 216
# at 1877
#190518 14:10:11 server id 1 end_log_pos 1958 CRC32 0x79ae56b5 Delete_rows: table id 216 flags: STMT_END_F
### DELETE FROM `edusoho_e`.`t1`
### WHERE
### @1=21
### @2='我是谁'
### @3='不知道'
### @4=1
### @5='吃了睡睡了吃'
### @6=18
# at 1958
#190518 14:10:11 server id 1 end_log_pos 1989 CRC32 0xbeb28116 Xid = 471
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
查看Master事件：
mysql> show binlog events in 'mysql-bin.000001' from 1405;
+------------------+------+-------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+-------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000001 | 1405 | Gtid | 1 | 1453 | SET @@SESSION.GTID_NEXT= 'b6af5b5c-666f-11e9-bed3-000c29b85ea6:5' |
| mysql-bin.000001 | 1453 | Query | 1 | 1530 | BEGIN |
| mysql-bin.000001 | 1530 | Table_map | 1 | 1591 | table_id: 216 (edusoho_e.t1) |
| mysql-bin.000001 | 1591 | Write_rows | 1 | 1660 | table_id: 216 flags: STMT_END_F |
| mysql-bin.000001 | 1660 | Xid | 1 | 1691 | COMMIT /* xid=462 */ |
| mysql-bin.000001 | 1691 | Gtid | 1 | 1739 | SET @@SESSION.GTID_NEXT= 'b6af5b5c-666f-11e9-bed3-000c29b85ea6:6' |
| mysql-bin.000001 | 1739 | Query | 1 | 1816 | BEGIN |
| mysql-bin.000001 | 1816 | Table_map | 1 | 1877 | table_id: 216 (edusoho_e.t1) |
| mysql-bin.000001 | 1877 | Delete_rows | 1 | 1958 | table_id: 216 flags: STMT_END_F |
| mysql-bin.000001 | 1958 | Xid | 1 | 1989 | COMMIT /* xid=471 */ |
+------------------+------+-------------+-----------+-------------+-------------------------------------------------------------------+
10 rows in set (0.00 sec)
Slave上先跳过第一个问题GTID：
mysql> set GTID_NEXT= 'b6af5b5c-666f-11e9-bed3-000c29b85ea6:5';begin;commit;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> set gtid_next='automatic';
Query OK, 0 rows affected (0.00 sec)
mysql> start slave sql_thread;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_Errno: 1032
Last_Error: Could not execute Delete_rows event on table edusoho_e.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000001, end_log_pos 1958
Exec_Master_Log_Pos: 1691
Master_UUID: b6af5b5c-666f-11e9-bed3-000c29b85ea6
Retrieved_Gtid_Set: b6af5b5c-666f-11e9-bed3-000c29b85ea6:1-6
Executed_Gtid_Set: a951db35-340a-11e8-b0e0-000c291e8778:1-5,
b6af5b5c-666f-11e9-bed3-000c29b85ea6:1-5
从上面主从复制状态就可以看出已经成功跳过了第一个问题GTID，接下来跳过第二个
mysql> SET GTID_NEXT= 'b6af5b5c-666f-11e9-bed3-000c29b85ea6:6';begin;commit;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> set gtid_next='automatic';
Query OK, 0 rows affected (0.00 sec)
mysql> start slave sql_thread;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Retrieved_Gtid_Set: b6af5b5c-666f-11e9-bed3-000c29b85ea6:1-6
Executed_Gtid_Set: a951db35-340a-11e8-b0e0-000c291e8778:1-5,
b6af5b5c-666f-11e9-bed3-000c29b85ea6:1-6
可以看到，主从间的复制故障已经成功跳过，主从恢复正常的复制状态
模拟Slave尚未同步Master的GTID前，Master将这部分的GTID清除了（purge）：
Master主机：
CREATE TABLE `bbs`.`t1` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`xname` VARCHAR(20) NOT NULL DEFAULT '',
`address` CHAR(20) NOT NULL DEFAULT '',
`sex` TINYINT(1) NOT NULL DEFAULT '1',
`hobby` VARCHAR(30) NOT NULL DEFAULT '',
`age` TINYINT(2) DEFAULT '18',
PRIMARY KEY (`id`),
KEY `idx_name` (`xname`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
FLUSH LOGS;INSERT INTO `bbs`.`t1` (`xname`, `address`, `hobby`) VALUES ('lzb', '石家庄', 'MySQL');
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000002 | 455 | | | b6af5b5c-666f-11e9-bed3-000c29b85ea6:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
Slave主机：
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.00 sec)
Master：
FLUSH LOGS;INSERT INTO `bbs`.`t1` (`xname`, `address`, `hobby`) VALUES ('Python', '北京', '游戏');
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000003 | 456 | | | b6af5b5c-666f-11e9-bed3-000c29b85ea6:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
FLUSH LOGS;INSERT INTO `bbs`.`t1` (`xname`, `address`, `hobby`) VALUES ('PHP', '上海', '开发');
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000004 | 453 | | | b6af5b5c-666f-11e9-bed3-000c29b85ea6:1-4 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
在Slave主机上GTID只是执行了1-2事务，而Master主机的3-4事务还没有执行，现在模拟Slave未同步Master的3-4事务前，Master将日志purge了
Master主机：
mysql> purge binary logs to 'mysql-bin.000004';
Query OK, 0 rows affected (0.04 sec)
mysql> show global variables like '%gtid%';
+--------------------------+------------------------------------------+
| Variable_name | Value |
+--------------------------+------------------------------------------+
| enforce_gtid_consistency | ON |
| gtid_executed | b6af5b5c-666f-11e9-bed3-000c29b85ea6:1-4 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | b6af5b5c-666f-11e9-bed3-000c29b85ea6:1-3 |
+--------------------------+------------------------------------------+
5 rows in set (0.00 sec)
Slave主机：
mysql> start slave io_thread user='repliter' password='123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
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.' （Slave未同步Master的3-4事务前，Master将日志purge了）
Retrieved_Gtid_Set: b6af5b5c-666f-11e9-bed3-000c29b85ea6:1-2
Executed_Gtid_Set: b6af5b5c-666f-11e9-bed3-000c29b85ea6:1-2
现在在Slave主机上忽略掉purge掉的日志部分，在进行数据复制：
因为Slave_IO_Running: No 状态为NO，所以直接跳过purge掉的日志部分就好
mysql> set global gtid_purged = 'b6af5b5c-666f-11e9-bed3-000c29b85ea6:3';
ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty. （想要进行gtid_purged，必须gtid_executed 为空）
mysql> show global variables like '%gtid%';
+--------------------------+------------------------------------------+
| Variable_name | Value |
+--------------------------+------------------------------------------+
| enforce_gtid_consistency | ON |
| gtid_executed | b6af5b5c-666f-11e9-bed3-000c29b85ea6:1-2 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
+--------------------------+------------------------------------------+
5 rows in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| slave-bin.000001 | 892 | | | b6af5b5c-666f-11e9-bed3-000c29b85ea6:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> reset master;（将Slave的 Executed_Gtid_Set 清空）
Query OK, 0 rows affected (0.02 sec)
mysql> set global gtid_purged = 'b6af5b5c-666f-11e9-bed3-000c29b85ea6:1-3';
Query OK, 0 rows affected (0.03 sec)
开始复制：
start slave io_thread user='repliter' password='123456';
测试：
INSERT INTO `bbs`.`t1` (`xname`, `address`, `hobby`) VALUES ('DBA', '石家庄', '电影');
记录 create dbname.tablename 语句不同步GTID的问题：
为了更清楚的展示，先将Slave重置
mysql> stop slave;
Query OK, 0 rows affected (0.02 sec)
mysql> reset slave;
Query OK, 0 rows affected (0.02 sec)
mysql> reset master;
Query OK, 0 rows affected (0.03 sec)
SET @@session.sql_log_bin=0;
DROP DATABASE `edusoho_e`;
[root@slave mysql3306]# ls binlog.* relaylog.*
binlog.000001 binlog.index relaylog.000001 relaylog.index
可以看到Slave已经重置
将Master也重置：
mysql> reset master;
Query OK, 0 rows affected (0.04 sec)
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 | 151 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
SET @@session.sql_log_bin=0;
DROP DATABASE `edusoho_e`;
现在环境重置完了，我们不导出Master的复制数据到SLave，而是直接让SLave从Master复制数据
创建edusoho_e库：
mysql> create database edusoho_e;
Query OK, 1 row affected (0.01 sec)
创建edusoho_e.t1表：
mysql> CREATE TABLE `edusoho_e`.`t1` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`xname` VARCHAR(20) NOT NULL DEFAULT '',
`address` CHAR(20) NOT NULL DEFAULT '',
`sex` TINYINT(1) NOT NULL DEFAULT '1',
`hobby` VARCHAR(30) NOT NULL DEFAULT '',
`age` TINYINT(2) DEFAULT '18',
PRIMARY KEY (`id`),
KEY `idx_name` (`xname`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.05 sec)
插入edusoho.t1测试数据：
mysql> INSERT INTO `edusoho_e`.`t1` (`xname`, `address`, `hobby`) VALUES ('edusoho_e', '上海', '开发');
Query OK, 1 row affected (0.01 sec)
mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000001 | 1057 | | | b6af5b5c-666f-11e9-bed3-000c29b85ea6:1-3 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
Slave开始复制：
[root@slave mysql3306]# ls binlog.* relaylog.*
binlog.000001 binlog.index relaylog.000001 relaylog.index
mysql> change master to master_auto_position=1,master_host='192.168.32.3',master_port=3306;
Query OK, 0 rows affected (0.06 sec)
[root@slave mysql3306]# ls binlog.* relaylog.*
binlog.000001 binlog.index relaylog.000001 relaylog.index
mysql> start slave user='repliter' password='123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)
[root@slave mysql3306]# ls binlog.* relaylog.*
binlog.000001 binlog.index relaylog.000001 relaylog.000002 relaylog.index
发生了什么，你没看错，relay log，在开始复制线程之后，自动滚动了一个（至于为什么，笔者记得应该是为了relay log的一致性，开启了某参数，在重启Slave之后，自动滚动的）
另外，你就会发现 create dbname.tablename 的GTID没有同步过去，虽然报 'edusoho_e.t1' doesn't exist' 问题，但是去分析 relaylog.000002 文件然后你会发现是有 create dbname.tablename 的语句的，但是Slave却没有执行，Executed_Gtid_Set 已经表明了这一点（关于这一点，笔者也是无意中发现的，不知道是不是bug，笔者用的是5.6.16版本，但是MySQL5.7.24版本的GTID笔者也有测过，也发现了同样的问题，难道是 create dbname.tablename 被限制使用，而笔者没发现这方面的说明？对于这个问题，笔者会持续关注，有了结果，也会及时更新此博客，当然了，如果有业内大神路过，也请指点下，不胜感激！）
mysql> show slave status\G;
*************************** 1. row ***************************
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 1057
Relay_Log_File: relaylog.000002
Relay_Log_Pos: 987
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB: edusoho_e
Last_Errno: 1146
Last_Error: Error executing row event: 'Table 'edusoho_e.t1' doesn't exist'
Exec_Master_Log_Pos: 783
Retrieved_Gtid_Set: b6af5b5c-666f-11e9-bed3-000c29b85ea6:1-3
Executed_Gtid_Set: b6af5b5c-666f-11e9-bed3-000c29b85ea6:1
Auto_Position: 1
[root@slave mysql3306]# mysqlbinlog -v --base64-output=decode relaylog.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190528 18:08:48 server id 2 end_log_pos 120 CRC32 0x3d8251a5 Start: binlog v 4, server v 5.6.16-log created 190528 18:08:48
# at 120
#190528 18:08:48 server id 2 end_log_pos 151 CRC32 0x07d0253d Previous-GTIDs
# [empty]
# at 151
#700101 8:00:00 server id 1 end_log_pos 0 CRC32 0x6ecad2e9 Rotate to binlog.000001 pos: 4
# at 195
#190528 17:59:23 server id 1 end_log_pos 120 CRC32 0x56fa9d7b Start: binlog v 4, server v 5.6.16-log created 190528 17:59:23 at startup
ROLLBACK/*!*/;
# at 311
#190528 18:08:48 server id 0 end_log_pos 355 CRC32 0xbad86777 Rotate to binlog.000001 pos: 151
# at 355
#190528 18:05:21 server id 1 end_log_pos 199 CRC32 0x82870406 GTID [commit=yes]
SET @@SESSION.GTID_NEXT= 'b6af5b5c-666f-11e9-bed3-000c29b85ea6:1'/*!*/;
# at 403
#190528 18:05:21 server id 1 end_log_pos 308 CRC32 0x56a02447 Query thread_id=8 exec_time=0 error_code=0
SET TIMESTAMP=1559037921/*!*/;
SET @@session.pseudo_thread_id=8/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database edusoho_e
/*!*/;
# at 512
#190528 18:05:37 server id 1 end_log_pos 356 CRC32 0xf5ec3898 GTID [commit=yes]
SET @@SESSION.GTID_NEXT= 'b6af5b5c-666f-11e9-bed3-000c29b85ea6:2'/*!*/;
# at 560
#190528 18:05:37 server id 1 end_log_pos 783 CRC32 0x366e81af Query thread_id=8 exec_time=0 error_code=0
SET TIMESTAMP=1559037937/*!*/;
CREATE TABLE `edusoho_e`.`t1` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`xname` VARCHAR(20) NOT NULL DEFAULT '',
`address` CHAR(20) NOT NULL DEFAULT '',
`sex` TINYINT(1) NOT NULL DEFAULT '1',
`hobby` VARCHAR(30) NOT NULL DEFAULT '',
`age` TINYINT(2) DEFAULT '18',
PRIMARY KEY (`id`),
KEY `idx_name` (`xname`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
/*!*/;
# at 987
#190528 18:05:48 server id 1 end_log_pos 831 CRC32 0xcc4e2884 GTID [commit=yes]
SET @@SESSION.GTID_NEXT= 'b6af5b5c-666f-11e9-bed3-000c29b85ea6:3'/*!*/;
# at 1035
#190528 18:05:48 server id 1 end_log_pos 899 CRC32 0x852bcbf9 Query thread_id=8 exec_time=0 error_code=0
SET TIMESTAMP=1559037948/*!*/;
BEGIN
/*!*/;
# at 1103
#190528 18:05:48 server id 1 end_log_pos 960 CRC32 0xe8f450ec Table_map: `edusoho_e`.`t1` mapped to number 544
# at 1164
#190528 18:05:48 server id 1 end_log_pos 1026 CRC32 0xe0c91caf Write_rows: table id 544 flags: STMT_END_F
### INSERT INTO `edusoho_e`.`t1`
### SET
### @1=1
### @2='edusoho_e'
### @3='上海'
### @4=1
### @5='开发'
### @6=18
# at 1230
#190528 18:05:48 server id 1 end_log_pos 1057 CRC32 0xa0f363ed Xid = 1431
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_T
好了，至此基于GTID复制模式搭建、故障模拟、问题解决全部讲解完毕，对于其中不当之处，还请留言指正！
