mysql binlog日志中的GTID的应用

最新推荐文章于 2024-10-03 10:54:05 发布

啊哈？？

最新推荐文章于 2024-10-03 10:54:05 发布

阅读量2.9k

点赞数 2

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/supahero/article/details/109490083

版权

mysql 专栏收录该内容

23 篇文章 0 订阅

订阅专栏

原文链接：https://www.cpweb.top/1000

一、简介

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                 |
+--------------------+