MySQL GTID

1 GTID ?
  A global transaction identifier (GTID),实在master上每个事务被提交时候被创建和分配的唯一全局事务标识
 组成：GTID = source_id:transaction_id
 其中sorce_id 来源于datadir下的auto.cnf
2，GTID和Binlog
 2.1 binlog包含版本信息，previous gtid log 然后就是gtid event....
 2.2 gtid 结构包含gtid+begin+event1...eventn commit....
 这里要说明下，不管什么存储引擎，都会添加begin和commit;

3，开启
 5.7.13开启
gtid_mode=ON(必选)
enforce-gtid-consistency（必选）
log_bin=ON（可选）--高可用切换，最好设置ON
log-slave-updates=ON（可选）--高可用切换，最好设置ON

4，查看日志

mysql> show binlog events in 'binlog.000002';
+---------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                                              |
+---------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| binlog.000002 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.15-log, Binlog ver: 4                             |
| binlog.000002 | 123 | Previous_gtids |         1 |         154 |                                                                   |
| binlog.000002 | 154 | Gtid           |         1 |         219 | SET @@SESSION.GTID_NEXT= 'babb165d-978b-11e6-87e7-000c29038eb2:1' |
| binlog.000002 | 219 | Query          |         1 |         290 | BEGIN                                                             |
| binlog.000002 | 290 | Table_map      |         1 |         334 | table_id: 109 (db1.t2)                                            |
| binlog.000002 | 334 | Write_rows     |         1 |         374 | table_id: 109 flags: STMT_END_F                                   |
| binlog.000002 | 374 | Query          |         1 |         446 | COMMIT                                                            |
| binlog.000002 | 446 | Gtid           |         1 |         511 | SET @@SESSION.GTID_NEXT= 'babb165d-978b-11e6-87e7-000c29038eb2:2' |
| binlog.000002 | 511 | Query          |         1 |         582 | BEGIN                                                             |
| binlog.000002 | 582 | Table_map      |         1 |         626 | table_id: 109 (db1.t2)                                            |
| binlog.000002 | 626 | Write_rows     |         1 |         666 | table_id: 109 flags: STMT_END_F                                   |
| binlog.000002 | 666 | Query          |         1 |         738 | COMMIT                                                            |
| binlog.000002 | 738 | Gtid           |         1 |         803 | SET @@SESSION.GTID_NEXT= 'babb165d-978b-11e6-87e7-000c29038eb2:3' |
| binlog.000002 | 803 | Query          |         1 |         874 | BEGIN                                                             |
| binlog.000002 | 874 | Table_map      |         1 |         921 | table_id: 108 (db1.t1)                                            |
| binlog.000002 | 921 | Write_rows     |         1 |         961 | table_id: 108 flags: STMT_END_F                                   |
| binlog.000002 | 961 | Xid            |         1 |         992 | COMMIT /* xid=16 */                                               |
+---------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
17 rows in set (0.00 sec)

mysqlbinlog

[root@centos7 mysql]# mysqlbinlog --start-position=738 --stop-position=1000  /home/mysql/binlog.000002 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170920 14:42:28 server id 1  end_log_pos 123 CRC32 0xa4e01571  Start: binlog v 4, server v 5.7.15-log created 170920 14:42:28 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
1A3CWQ8BAAAAdwAAAHsAAAABAAQANS43LjE1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADUDcJZEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AXEV4KQ=
'/*!*/;
# at 738
#170920 14:56:39 server id 1  end_log_pos 803 CRC32 0xcef8ea21  GTID    last_committed=2        sequence_number=3
SET @@SESSION.GTID_NEXT= 'babb165d-978b-11e6-87e7-000c29038eb2:3'/*!*/;
# at 803
#170920 14:56:39 server id 1  end_log_pos 874 CRC32 0xe1c1fe17  Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1505890599/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
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=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 874
#170920 14:56:39 server id 1  end_log_pos 921 CRC32 0xcdb51cce  Table_map: `db1`.`t1` mapped to number 108
# at 921
#170920 14:56:39 server id 1  end_log_pos 961 CRC32 0x1af6a768  Write_rows: table id 108 flags: STMT_END_F

BINLOG '
JxHCWRMBAAAALwAAAJkDAAAAAGwAAAAAAAEAA2RiMQACdDEAAgMPAloAA84ctc0=
JxHCWR4BAAAAKAAAAMEDAAAAAGwAAAAAAAEAAgAC//5A4gEAaKf2Gg==
'/*!*/;
# at 961
#170920 14:56:39 server id 1  end_log_pos 992 CRC32 0xb5aec8a4  Xid = 16
COMMIT/*!*/;
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*/;

5，作用

全局唯一标识的作用何在？​
Failover 是它最大的特性，如果没有GTID之前是怎样的呢？
我们可以看下在MySQL 5.6的GTID出现以前replication failover的操作过程。
假设我们有一个如下图的环境​

如果，Server A的服务器宕机，需要将业务切换到Server B上。同时，我们又需要将Server C的复制源改成Server B。复制源修改的命令语法很简单即CHANGE MASTER TO MASTER_HOST='xxx', MASTER_LOG_FILE='xxx', MASTER_LOG_POS=nnnn。而难点在于，由于同一个事务在每台机器上所在的binlog名字和位置都不一样，那么怎么找到Server C当前同步停止点，对应Server B的master_log_file和master_log_pos是什么的时候就成为了难题。这也就是为什么M-S复制集群需要使用MMM,MHA这样的额外管理工具的一个重要原因。 这个问题在5.6的GTID出现后，就显得非常的简单。由于同一事务的GTID在所有节点上的值一致，那么根据Server C当前停止点的GTID就能唯一定位到Server B上的GTID。甚至由于MASTER_AUTO_POSITION功能的出现，我们都不需要知道GTID的具体值，直接使用CHANGE MASTER TO MASTER_HOST='xxx', MASTER_AUTO_POSITION命令就可以直接完成failover的工作