1.生成事务
root@(none)>use pxc01
Database changed
root@pxc01>create table tbx(id int);
Query OK, 0 rows affected (0.15 sec)
root@pxc01>insert into tbx values(1);
Query OK, 1 row affected (0.13 sec)
root@pxc01>select * from tbx;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
2.确认当前二进制文件位置
root@pxc01>show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000013 | 1040 | | | 015d4d11-0363-11e9-bb6c-0800279a3030:1-4 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
root@pxc01>
3.查看二进制文件中的事件（Previous_gtids 和 Gtid）
root@pxc01>show binlog events IN 'mysql-bin.000013';
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000013 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.23-23-31.31-log, Binlog ver: 4 |
| mysql-bin.000013 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000013 | 154 | Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= '015d4d11-0363-11e9-bb6c-0800279a3030:1' |
| mysql-bin.000013 | 219 | Query | 1 | 467 | GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'192.168.2.%' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| mysql-bin.000013 | 467 | Gtid | 1 | 532 | SET @@SESSION.GTID_NEXT= '015d4d11-0363-11e9-bb6c-0800279a3030:2' |
| mysql-bin.000013 | 532 | Query | 1 | 619 | flush privileges |
| mysql-bin.000013 | 619 | Gtid | 1 | 684 | SET @@SESSION.GTID_NEXT= '015d4d11-0363-11e9-bb6c-0800279a3030:3' |
| mysql-bin.000013 | 684 | Query | 1 | 784 | use `pxc01`; create table tbx(id int) |
| mysql-bin.000013 | 784 | Gtid | 1 | 849 | SET @@SESSION.GTID_NEXT= '015d4d11-0363-11e9-bb6c-0800279a3030:4' |
| mysql-bin.000013 | 849 | Query | 1 | 922 | BEGIN |
| mysql-bin.000013 | 922 | Table_map | 1 | 969 | table_id: 109 (pxc01.tbx) |
| mysql-bin.000013 | 969 | Write_rows | 1 | 1009 | table_id: 109 flags: STMT_END_F |
| mysql-bin.000013 | 1009 | Xid | 1 | 1040 | COMMIT /* xid=60 */ |
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
13 rows in set (0.00 sec)
4.查看全局变量 gtid_executed
root@pxc01>show global variables like 'gtid%';
+----------------------------------+------------------------------------------+
| Variable_name | Value |
+----------------------------------+------------------------------------------+
| gtid_executed | 015d4d11-0363-11e9-bb6c-0800279a3030:1-4 |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
+----------------------------------+------------------------------------------+
5 rows in set (0.01 sec)
root@pxc01>
GTID的持久化:
1.gtid_executed被设置为最新的binlog文件中Previous_gtids_log_event和所有Gtid_log_event的并集。
2.gtid_purged为最老的binlog文件中Previous_gtids_log_event。
由于这两个重要的变量值记录在binlog中，所以开启gtid_mode时必须同时在主库上开启log_bin在备库上开启log_slave_updates。但是，在MySQL5.7中没有这个限制。MySQL5.7中，
新增加一个系统表mysql.gtid_executed用于持久化已执行的GTID集合。当主库上没有开启log_bin或在备库上没有开启log_slave_updates时，mysql.gtid_executed会跟用户事务一起每次更新。否则只在binlog日志发生rotation时更新mysql.gtid_executed。
《与MySQL传统复制相比，GTID有哪些独特的复制姿势》
GTID和Binlog之间的关系是怎么对应的呢：
* 假设有4个binlog: bin.001,bin.002,bin.003,bin.004
* bin.001 : Previous-GTIDs=empty; binlog_event有：1-40
* bin.002 : Previous-GTIDs=1-40; binlog_event有：41-80
* bin.003 : Previous-GTIDs=1-80; binlog_event有：81-120
* bin.004 : Previous-GTIDs=1-120; binlog_event有：121-160
1. 假设现在我们要找GTID=$A，那么MySQL的扫描顺序为： 从最后一个binlog开始扫描（即：bin.004）
2. bin.004的Previous-GTIDs=1-120，如果$A=140 > Previous-GTIDs,那么肯定在bin.004中
3. bin.004的Previous-GTIDs=1-120，如果$A=88 包含在Previous-GTIDs中,那么继续对比上一个binlog文件 bin.003,然后再循环前面2个步骤，直到找到为止
《MySQL5.7杀手级新特性：GTID原理与实战》https://yq.aliyun.com/articles/57731
重要参数如何持久化：
1) 如何持久化gtid_executed [ log-bin=on,log_slave_update=on ]
1. gtid_executed = mysql.gtid_executed 【normal】
or
2. gtid_executed = mysql.gtid_executed + last_binlog中最后没写到mysql.gtid_executed中的gtid_event 【recover】
2) 如何持久化重置的gtid_purged值?
reset master; set global gtid_purged=$A:a-b;
1. 由于有可能手动设置过gtid_purged=$A:a-b, binlog.index中，last_binlog的Previous-GTIDs并不会包含$A:a-b
2. 由于有可能手动设置过gtid_purged=$A:a-b, binlog.index中，first_binlog的Previous-GTIDs肯定不会出现$A:a-b
3. 重置的gtid_purged = @@global.gtid_executed(mysql.gtid_executed:注意，考虑到这个表的更新触发条件，所以这里用@@global.gtid_executed代替) - last_binlog的Previous-GTIDs - last_binlog所有的gtid_event
4. 下面就用 $reset_gtid_purged 来表示重置的gtid
3）如何持久化gtid_purged [ log-bin=on,log_slave_update=on ]
gtid_purged=binlog.index:first_binlog的Previous-GTIDs + $reset_gtid_purged
开启GTID的必备条件：
MySQL 5.6
gtid_mode=ON(必选)
log_bin=ON(必选)
log-slave-updates=ON(必选)
enforce-gtid-consistency(必选)
MySQL 5.7 or higher
gtid_mode=ON(必选)
enforce-gtid-consistency（必选）
log_bin=ON（可选）--高可用切换，最好设置ON
log-slave-updates=ON（可选）--高可用切换，最好设置ON
GTID的Limitation
不安全的事务
1. CREATE TABLE ... SELECT statements
2. CREATE TEMPORARY TABLE or DROP TEMPORARY TABLE statements inside transactions
3. 同时更新 事务引擎 和 非事务引擎