1
一、简介
GTID (Global Transaction ID) ，全局事务ID。是一个对于已提交事务的全局唯一的编号，随着事务记录到binlog中，用来标识事务，每一个事务都会有一个。
GTID 实际上是由 server_uuid + transaction_id 组成的：
server_uuid：一个mysql实例的唯一标识，每一台mysql实例的server_uuid都是不同的。在mysql第一次启动时，会自动生成并持久化到auto.cnf文件（存放在mysql的数据目录下）。
transaction_id：该实例上已经提交的事务数量，transaction_id是一个从1开始的自增计数，表示在这台mysql实例上执行的第n个事务，随着事务的增加，此id依次递增。
二、基本配置和查看
[root@db03 ~]# vim /data/mysql57/my.cnf # 在mysqld添加以下配置
gtid_mode=on
enforce_gtid_consistency=on
配置说明：
gtid_mode：是否开启GTID
enforce_gtid_consistency：强制gtid的一致性。主要为了主从环境，可以让我们的gtid在多台mysql机器中全局唯一。
mysql> select @@gtid_mode;
+-------------+
| @@gtid_mode |
+-------------+
| ON |
+-------------+
mysql> select @@server_uuid;
+--------------------------------------+
| @@server_uuid |
+--------------------------------------+
| 65b214ad-1771-11eb-8700-000c29eaf728 |
+--------------------------------------+
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+我们重点看Executed_Gtid_Set列。现在没有信息是因为binlog日志还没有记录事务，我们创建一个测试库再查看一下。
mysql> create database test5;
mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000005 | 316 | | | 65b214ad-1771-11eb-8700-000c29eaf728:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
mysql> show binlog events in 'mysql-bin.000005';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000005 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.28-log, Binlog ver: 4 |
| mysql-bin.000005 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000005 | 154 | Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= '65b214ad-1771-11eb-8700-000c29eaf728:1' |
| mysql-bin.000005 | 219 | Query | 1 | 316 | create database test5 |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
三、通过GTID方式截取和恢复日志
（1）模拟故障
mysql> create table test5.stu(id int);
mysql> insert into test5.stu values(1),(2);
mysql> drop database test5;
（2）截取日志
参数说明：
--include-gtids 截取指定的gtid
--exclude-gtids 排除指定的gtid
--skip-gtids 跳过gtid的幂等性机制的检查，即截取日志的时候不带有gtid的信息
mysql> mysql> show binlog events in 'mysql-bin.000005';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
...... |
| mysql-bin.000005 | 154 | Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= '65b214ad-1771-11eb-8700-000c29eaf728:1' |
| mysql-bin.000005 | 219 | Query | 1 | 316 | create database test5 |
......
| mysql-bin.000005 | 739 | Gtid | 1 | 804 | SET @@SESSION.GTID_NEXT= '65b214ad-1771-11eb-8700-000c29eaf728:4' |
| mysql-bin.000005 | 804 | Query | 1 | 899 | drop database test5 |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
[root@db03 binlog]# mysqlbinlog --skip-gtids \
--include-gtids='65b214ad-1771-11eb-8700-000c29eaf728:1-3' \
mysql-bin.000005 > /tmp/5.sql
mysql> set sql_log_bin=0;
mysql> source /tmp/5.sql;
mysql> select * from test3.stu;
+------+
| id |
+------+
| 1 |
| 2 |
+------+跨binlog日志文件截取日志。当要恢复的数据日志记录在多个binlog文件中，此时我们使用position号截取就不方便了，因为position号在不同的binlog日志文件是独立，而GTID是唯一的，此时我们就可以通过GTID方式来从多个binlog日志文件中截取。
mysql> show binlog events in 'mysql-bin.000002';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.28-log, Binlog ver: 4 |
| mysql-bin.000002 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000002 | 154 | Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= '65b214ad-1771-11eb-8700-000c29eaf728:1' |
| mysql-bin.000002 | 219 | Query | 1 | 307 | create database t1 |
| mysql-bin.000002 | 307 | Gtid | 1 | 372 | SET @@SESSION.GTID_NEXT= '65b214ad-1771-11eb-8700-000c29eaf728:2' |
| mysql-bin.000002 | 372 | Query | 1 | 460 | create database t2 |
| mysql-bin.000002 | 460 | Rotate | 1 | 507 | mysql-bin.000003;pos=4 |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
7 rows in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000003';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000003 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.28-log, Binlog ver: 4 |
| mysql-bin.000003 | 123 | Previous_gtids | 1 | 194 | 65b214ad-1771-11eb-8700-000c29eaf728:1-2 |
| mysql-bin.000003 | 194 | Gtid | 1 | 259 | SET @@SESSION.GTID_NEXT= '65b214ad-1771-11eb-8700-000c29eaf728:3' |
| mysql-bin.000003 | 259 | Query | 1 | 347 | create database t3 |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000003 | 347 | | | 65b214ad-1771-11eb-8700-000c29eaf728:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
[root@db03 binlog]# mysqlbinlog --skip-gtids \
--include-gtids='65b214ad-1771-11eb-8700-000c29eaf728:1-3' \
mysql-bin.000002 mysql-bin.000003 > /tmp/gtid.sql
mysql> set sql_log_bin=0;
mysql> source /tmp/gtid.sql;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
......
| t1 |
| t2 |
| t3 |
+--------------------+
参考文章：https://www.css3er.com/p/260.html，推荐阅读。
1