MySQL GTID你知多少
MySQL在5.6的版本推出了GTID复制，相比传统的复制，GTID复制对于运维更加友好，这个事务是谁产⽣，产⽣多少事务，⾮常直接的标识出来，当然GTID也有限制，对于什么是GTID可以参考我之前的文章：MySQL 5.6 GTID Replication，那么今天主要是想和同学们分享一下关于从库show slave status中的Retrieved_Gtid_Set和Executed_Gtid_Set。
[root@localhost][db1]> show variables like '%uuid%'; +---------------+--------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------+ | server_uuid | 2a09ee6e-645d-11e7-a96c-000c2953a1cb | +---------------+--------------------------------------+ 1 row in set (0.00 sec) [root@localhost][db1]>
slave：
[root@localhost][(none)]> show variables like '%uuid%'; +---------------+--------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------+ | server_uuid | 8ce853fc-6f8a-11e7-8940-000c29e3f5ab | +---------------+--------------------------------------+ 1 row in set (0.01 sec) [root@localhost][(none)]>
其中主库的server-id是10，从库的server-id是20
搭建好主从以后如果没有数据写入，那么show slave status是下面这样的：
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
Master_UUID: 2a09ee6e-645d-11e7-a96c-000c2953a1cb
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
如果在主库创建表，并且写入2条数据，那么是下面这样的：
[root@localhost][db1]> create table t2 ( id int); Query OK, 0 rows affected (0.07 sec) [root@localhost][db1]> insert into t2 select 1; Query OK, 1 row affected (0.07 sec) Records: 1 Duplicates: 0 Warnings: 0 [root@localhost][db1]> insert into t2 select 2; Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 [root@localhost][db1]>
从库：
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
Master_UUID: 2a09ee6e-645d-11e7-a96c-000c2953a1cb
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-3
Executed_Gtid_Set: 2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-3
Auto_Position: 1
主库：
[root@localhost][db1]> show master status; +------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------+ | mysql-bin.000001 | 912 | | | 2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-3 | +------------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec)
其中主库的Executed_Gtid_Set为2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-3
可以看见Retrieved_Gtid_Set: 2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-3，Executed_Gtid_Set: 2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-3，也就是说主库产生了3个事务，从库接受到了来自主库的3个事务，并且都已经执行。
其中2a09ee6e-645d-11e7-a96c-000c2953a1cb是主库的server-uuid。那么我们可以解析从库的binlog再看看
# at 154 #170823 0:38:38 server id 10 end_log_pos 219 CRC32 0x6268641f GTID last_committed=0 sequence_number=1 SET @@SESSION.GTID_NEXT= '2a09ee6e-645d-11e7-a96c-000c2953a1cb:1'/*!*/; # at 219 #170823 0:38:38 server id 10 end_log_pos 316 CRC32 0x6c837618 Query thread_id=103 exec_time=0 error_code=0 use `db1`/*!*/; SET TIMESTAMP=1503419918/*!*/; SET @@session.pseudo_thread_id=103/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1436549152/*!*/; 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 table t2 ( id int) /*!*/;
可以看见server-id为10，gtid-next为2a09ee6e-645d-11e7-a96c-000c2953a1cb:1，执行了建表。剩下的2-3是执行的数据插入，我这里没写出来。
这也体现了文章开始提到的：这个事务是谁产⽣，产⽣多少事务，⾮常直接的标识出来
那么对于文章开头那个诡异的gtid是怎么出来的呢？先说说已经执行的事务：
Executed_Gtid_Set: 2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-33,
8ce853fc-6f8a-11e7-8940-000c29e3f5ab:1
这里的2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-33肯定很好理解，就是已经执行主库的1-33的事务，那么8ce853fc-6f8a-11e7-8940-000c29e3f5ab:1呢？这个其实也简单，有两种情况：
第一种情况：从库有数据写入( 从库插入数据 )
[root@localhost][db1]> insert into t2 select 1; Query OK, 1 row affected (0.03 sec) Records: 1 Duplicates: 0 Warnings: 0
show slave status
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
Master_UUID: 2a09ee6e-645d-11e7-a96c-000c2953a1cb
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-3
Executed_Gtid_Set: 2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-3,
8ce853fc-6f8a-11e7-8940-000c29e3f5ab:1
Auto_Position: 1
Replicate_Rewrite_DB:
可以看见已经执行的事务有来自主库的2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-3，也有从库刚自己写入的数据：8ce853fc-6f8a-11e7-8940-000c29e3f5ab:1。我们可以解析binlog看看。
mysqlbinlog -vv mysql-bin.000001 --include-gtids='8ce853fc-6f8a-11e7-8940-000c29e3f5ab:1'
# at 896 #170823 0:59:19 server id 20 end_log_pos 961 CRC32 0x0492528a GTID last_committed=3 sequence_number=4 SET @@SESSION.GTID_NEXT= '8ce853fc-6f8a-11e7-8940-000c29e3f5ab:1'/*!*/; # at 961 #170823 0:59:19 server id 20 end_log_pos 1032 CRC32 0xbf545cca Query thread_id=25 exec_time=0 error_code=0 SET TIMESTAMP=1503421159/*!*/; SET @@session.pseudo_thread_id=25/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1436549152/*!*/; 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 1032 #170823 0:59:19 server id 20 end_log_pos 1079 CRC32 0x2f2de3ec Rows_query # insert into t2 select 1 # at 1079 #170823 0:59:19 server id 20 end_log_pos 1123 CRC32 0x18fe1c5c Table_map: `db1`.`t2` mapped to number 109 # at 1123 #170823 0:59:19 server id 20 end_log_pos 1163 CRC32 0x163a708e Write_rows: table id 109 flags: STMT_END_F BINLOG ' 52KcWR0UAAAALwAAADcEAACAABdpbnNlcnQgaW50byB0MiBzZWxlY3QgMezjLS8= 52KcWRMUAAAALAAAAGMEAAAAAG0AAAAAAAEAA2RiMQACdDIAAQMAAVwc/hg= 52KcWR4UAAAAKAAAAIsEAAAAAG0AAAAAAAEAAgAB//4BAAAAjnA6Fg== '/*!*/; ### INSERT INTO `db1`.`t2` ### SET ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ # at 1163 #170823 0:59:19 server id 20 end_log_pos 1194 CRC32 0xe3347ac1 Xid = 68 COMMIT/*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
从binlog中可以清楚的看到是从库进行了写入。下面说第二组情况
第二种情况：主从切换（我这里使用MHA切换主从）
Master_Server_Id: 20
Master_UUID: 8ce853fc-6f8a-11e7-8940-000c29e3f5ab
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 8ce853fc-6f8a-11e7-8940-000c29e3f5ab:1
Executed_Gtid_Set: 2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-3,
8ce853fc-6f8a-11e7-8940-000c29e3f5ab:1
Auto_Position: 1
可以看到在切换以后主库的server-id是20。这里的意思是接收到主库8ce853fc-6f8a-11e7-8940-000c29e3f5ab:1，并且已经执行这个事务，那么这个事务其实就是之前在从库写入的那条数据。对于2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-3这个是之前作为主库执行。如果此时在主库再插入1条数据，那么又会变化如下：
Retrieved_Gtid_Set: 8ce853fc-6f8a-11e7-8940-000c29e3f5ab:1-2
Executed_Gtid_Set: 2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-3,
8ce853fc-6f8a-11e7-8940-000c29e3f5ab:1-2
下面说说文章开头提到的gtid不连续的问题，类似2a09ee6e-645d-11e7-a96c-000c2953a1cb:37-45，这个是由于binlog被清理以后导致的，我们可以测试一下。然后查看gtid_purged变量。
binlog不可能永远驻留在服务上，需要定期进行清理(通过expire_logs_days可以控制定期清理间隔)，否则迟早它会把磁盘用尽。gtid_purged用于记录已经被清除了的binlog事务集合，它是gtid_executed的子集。只有gtid_executed为空时才能手动设置该变量，此时会同时更新gtid_executed为和gtid_purged相同的值。gtid_executed为空意味着要么之前没有启动过基于GTID的复制，要么执行过RESET MASTER。执行RESET MASTER时同样也会把gtid_purged置空，即始终保持gtid_purged是gtid_executed的子集。
从库：
[root@localhost][db1]> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 3530 | +------------------+-----------+ 1 row in set (0.00 sec) [root@localhost][db1]> flush logs; Query OK, 0 rows affected (0.05 sec) [root@localhost][db1]> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 3577 | | mysql-bin.000002 | 234 | +------------------+-----------+ 2 rows in set (0.00 sec) [root@localhost][db1]> PURGE BINARY LOGS TO 'mysql-bin.000002'; Query OK, 0 rows affected (0.01 sec) [root@localhost][db1]> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000002 | 234 | +------------------+-----------+ 1 row in set (0.00 sec)
然后只要从库有重新启动，才会读取。MySQL服务器启动时，通过读binlog文件，初始化gtid_executed和gtid_purged,使它们的值能和上次MySQL运行时一致。
gtid_executed被设置为最新的binlog文件中Previous_gtids_log_event和所有Gtid_log_event的并集。
gtid_purged为最老的binlog文件中Previous_gtids_log_event。
没启动前：
Retrieved_Gtid_Set: 8ce853fc-6f8a-11e7-8940-000c29e3f5ab:1-9
Executed_Gtid_Set: 2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-3,
8ce853fc-6f8a-11e7-8940-000c29e3f5ab:1-9
重启以后并且插入数据：
Master_Server_Id: 20
Master_UUID: 8ce853fc-6f8a-11e7-8940-000c29e3f5ab
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 8ce853fc-6f8a-11e7-8940-000c29e3f5ab:10
Executed_Gtid_Set: 2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-3,
8ce853fc-6f8a-11e7-8940-000c29e3f5ab:1-10
Auto_Position: 1
[root@localhost][(none)]> show variables like 'gtid_purged'; +---------------+------------------------------------------------------------------------------------+ | Variable_name | Value | +---------------+------------------------------------------------------------------------------------+ | gtid_purged | 2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-3, 8ce853fc-6f8a-11e7-8940-000c29e3f5ab:1-9 | +---------------+------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)
到这里相信聪明的你一定看懂了。最后顺道说说gtid跳过复制错误的方法，对于跳过一个错误，找到无法执行事务的编号，比如是2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-10，那么操作如下：
stop slave; set gtid_next='2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-10'; begin; commit; set gtid_next='AUTOMATIC'; start slave;
上面方法只能跳过一个事务，那么对于一批如何跳过？在主库执行show master status，看主库执行到了哪里，比如：2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-33，那么操作如下：
stop slave; reset master; set global gtid_purged='2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-33'; start slave;
