• Post author:
  • Post category:mysql
  • Post comments:0评论
Like
Like Love Haha Wow Sad Angry

一、简介

  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,推荐阅读。

Like
Like Love Haha Wow Sad Angry

发表评论