深刻理解gtid_executed和gtid_purged

Mysql IT敢客 7个月前 (05-22) 2313次浏览 已收录 0个评论 扫描二维码
文章目录[隐藏]

gtid_executed 和 gtid_purged 这玩意是 mysql5.7 后才出现的,在之前的文章页阐述过GTID的原理和传统 binlog 的不同之处以及优势地方,详情可以参考之前的文章 MySQL 主从复制与 GTID 复制原理。不过相信许多人看了之后,对于 GTID 还不是很明白清楚。这里对 GTID 做一个详细的说明。

一、官方给出的释义

1.1、gtid_executed、gtid_purged

官方的链接在这里:https://dev.mysql.com/doc/refman/5.7/en/replication-options-gtids.html#sysvar_gtid_executed

gtid_executed(global):MySQL 数据库已经执行过的 Gtid 事务,处于内存中。show master status/show slave status 中的 Executed_Gtid_Set 也取自这里
gtid_purged(global):由于 binlog 文件的删除(如 purge binary logs 或者超过 expire_logs_days 设置)已经丢失的 Gtid 事务,它是 gtid_executed 的子集

1.2、binlog_gtid_simple_recovery

官方的链接在这里:https://dev.mysql.com/doc/refman/5.7/en/replication-options-gtids.html#sysvar_binlog_gtid_simple_recovery

数据库服务启动时,gtid_executed 和 gtid_purged 按下面方式初始化
binlog_gtid_simple_recovery=FALSE
• gtid_executed:从 mysql-bin.index 的末行往首行所对应的 binlog 查找,直到首个被找到包含 Previous_gtids_log_event 的 binlog。然后读取这个 binlog 的 Previous_gtids_log_event 和 Gtid_log_events 中的所有 Gtid 集合保存到内部变量 gtids_in_binlog。然后使用 gtids_in_binlog 和 mysql.gtid_executed 表的并集初始化 gtid_executed 变量
如果你有大量非 GTID 的 binlog(比如 gtid_mode=off 的情况下创建),初始化 gtid_executed 的过程会消耗较长的时间
• gtid_purged:从 mysql-bin.index 的首行往末行所对应的 binlog 查找,直到首个被找到包含非空 Previous_gtids_log_event 或者 Gtid_log_event 的 binlog。然后读取这个 binlog 的 Previous_gtids_log_event,将 gtids_in_binlog – Previous_gtids_log_event 得到的集合保存到内部变量 gtids_in_binlog_not_purged。最后使用 gtid_executed – gtids_in_binlog_not_purged 初始化 gtid_purged 变量
binlog_gtid_simple_recovery=TRUE(MySQL5.7.7 及以上默认)
只迭代 mysql-bin.index 的首行和末行所对应的 binlog,gtid_executed 和 gtid_purged 的值就是取这两个 binlog 中的 Previous_gtids_log_event/Gtid_log_event 计算,当然 gtid_executed 变量的值还要结合 mysql.gtid_executed

1.3、mysql.gtid_executed

官方的链接在这里:https://dev.mysql.com/doc/refman/5.7/en/replication-gtids-concepts.html#replication-gtids-gtid-executed-table

如果没有开启 log_bin,每个事务提交前,会执行一个 insert mysql.gtid_executed 操作;如果从库没有开启 log_slave_updates,从库应用 relay-log 中的每个事务会执行一个 insert 操作
如果开启了 log_bin,在 binlog 发生 rotate(flush binary logs/达到 max_binlog_size)或者关闭服务时,会把所有写入到 binlog 中的 Gtid 信息写入到 mysql.gtid_executed 表
通俗点理解就是事务不记录到 binlog 就及时更新到 mysql.gtid_executed 表,记录到 binlog 就等待满足触发条件再更新到 mysql.gtid_executed 表
如果发生异常 crash,当前 binlog 中的 Gtids 信息没能写入到 mysql.gtid_executed 表,在恢复过程通过读取 binlog 中的 Previous_gtids_log_event/Gtid_log_event 信息把这些 Gtids 添加到 mysql.gtid_executed 表和 gtid_executed 系统变量

二、实验

基本环境:官方社区版 MySQL 5.7.19

[mysqld]
gtid-mode = on
binlog_gtid_simple_recovery = true
log-bin = /data/mysql/mysql3306/logs/mysql-bin

2.1、the oldest and newest file

前面初始化 gtid_executed 和 gtid_purged 时需迭代 the newest 和 the oldest file. 最新和最旧分别对应的是哪个 binary log?
2.1.1、binary log 与 index file 一致

# 置空所有 Gtid 信息
mydba@192.168.85.132,3308 [(none)]> reset master;
Query OK, 0 rows affected (0.04 sec)
# 关闭服务
mydba@192.168.85.132,3308 [(none)]> shutdown;

# 将另一实例下的 binlog 拷贝到当前实例的/data/mysql/mysql3308/logs/目录,并修改文件属主

# binlog 列表
[root@ZST1 logs]# ll
total 32
-rw-r-----. 1 mysql mysql 2012 Jan 19 15:33 mysql-bin.000202
-rw-r-----. 1 mysql mysql 1528 Jan 19 15:33 mysql-bin.000203
-rw-r-----. 1 mysql mysql  545 Jan 19 15:33 mysql-bin.000204
-rw-r-----. 1 mysql mysql  873 Jan 19 15:33 mysql-bin.000205
-rw-r-----. 1 mysql mysql  217 Jan 19 15:33 mysql-bin.000206
-rw-r-----. 1 mysql mysql 1056 Jan 19 15:33 mysql-bin.000207
-rw-r-----. 1 mysql mysql  545 Jan 19 15:33 mysql-bin.000208
-rw-r--r--. 1 mysql mysql  308 Jan 19 15:40 mysql-bin.index
[root@ZST1 logs]# cat mysql-bin.index 
/data/mysql/mysql3308/logs/mysql-bin.000202
/data/mysql/mysql3308/logs/mysql-bin.000203
/data/mysql/mysql3308/logs/mysql-bin.000204
/data/mysql/mysql3308/logs/mysql-bin.000205
/data/mysql/mysql3308/logs/mysql-bin.000206
/data/mysql/mysql3308/logs/mysql-bin.000207
/data/mysql/mysql3308/logs/mysql-bin.000208
[root@ZST1 logs]# 
# 启动后登录
mydba@192.168.85.132,3308 [(none)]> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000202 |      2012 |
| mysql-bin.000203 |      1528 |
| mysql-bin.000204 |       545 |
| mysql-bin.000205 |       873 |
| mysql-bin.000206 |       217 |
| mysql-bin.000207 |      1056 |
| mysql-bin.000208 |       545 |
| mysql-bin.000209 |       194 |
+------------------+-----------+
rows in set (0.00 sec)
# 查看 mysql.gtid_executed
mydba@192.168.85.132,3308 [(none)]> select * from mysql.gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid                          | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| 8ab82362-9c37-11e7-a858-000c29c1025c |              1 |       507528 |
+--------------------------------------+----------------+--------------+
row in set (0.00 sec)
# 查看当前位置
mydba@192.168.85.132,3308 [(none)]> show master status;
+------------------+----------+--------------+------------------+-----------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                             |
+------------------+----------+--------------+------------------+-----------------------------------------------+
| mysql-bin.000209 |      194 |              |                  | 8ab82362-9c37-11e7-a858-000c29c1025c:1-507528 |
+------------------+----------+--------------+------------------+-----------------------------------------------+
row in set (0.00 sec)
# 查看 gtid_purged
mydba@192.168.85.132,3308 [(none)]> show variables like 'gtid_purged';
+---------------+-----------------------------------------------+
| Variable_name | Value                                         |
+---------------+-----------------------------------------------+
| gtid_purged   | 8ab82362-9c37-11e7-a858-000c29c1025c:1-507510 |
+---------------+-----------------------------------------------+
row in set (0.04 sec)

解析 mysql-bin.000208、mysql-bin.000202

# 解析 mysql-bin.000208
[root@ZST1 logs]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000208 |more
...
#180119  9:27:10 server id 1323306  end_log_pos 194 CRC32 0xed94846f    Previous-GTIDs
# 8ab82362-9c37-11e7-a858-000c29c1025c:1-507527
# at 194
#180119  9:41:15 server id 1323306  end_log_pos 259 CRC32 0x8b964e9a    GTID    last_committed=0        sequence_number=1       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '8ab82362-9c37-11e7-a858-000c29c1025c:507528'/*!*/;
# at 259
#180119  9:41:15 server id 1323306  end_log_pos 344 CRC32 0x15e5bb70    Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1516326075/*!*/;
BEGIN
/*!*/;
# at 344
#180119  9:41:15 server id 1323306  end_log_pos 407 CRC32 0x16207d9b    Table_map: `replcrash`.`py_user` mapped to number 253
# at 407
#180119  9:41:15 server id 1323306  end_log_pos 491 CRC32 0x4fedbc9d    Write_rows: table id 253 flags: STMT_END_F
### INSERT INTO `replcrash`.`py_user`
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='d6366f0e-fcb9-11e7-ad55-000c29' /* VARSTRING(96) meta=96 nullable=1 is_null=0 */
###   @3='2018-01-19 09:41:15' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @4='1323306' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
# at 491
#180119  9:41:15 server id 1323306  end_log_pos 522 CRC32 0x46352a20    Xid = 55
COMMIT/*!*/;
# at 522
#180119 10:31:53 server id 1323306  end_log_pos 545 CRC32 0x6e53eee8    Stop
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*/;
[root@ZST1 logs]# 

gtids_in_binlog:1-507528
mysql.gtid_executed:空
gtid_executed 变量:gtids_in_binlog ∪ mysql.gtid_executed = 1-507528

# 解析 mysql-bin.000202
[root@ZST1 logs]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000202 |more
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180115 15:11:02 server id 1323306  end_log_pos 123 CRC32 0xbb10fc9f    Start: binlog v 4, server v 5.7.19-log created 180115 15:11:02 at startup
ROLLBACK/*!*/;
# at 123
#180115 15:11:02 server id 1323306  end_log_pos 194 CRC32 0x1e8fe6f0    Previous-GTIDs
# 8ab82362-9c37-11e7-a858-000c29c1025c:1-507510
# at 194
...

gtids_in_binlog:1-507528
oldest Previous_gtids_log_event:1-507510
gtids_in_binlog_not_purged:gtids_in_binlog - Previous_gtids_log_event = 507511-507528
gtid_purged 变量:gtid_executed - gtids_in_binlog_not_purged = (1-507528) - (507511-507528) = 1-507510

binlog 解析得到的数值和库中查询结果一致~

2.1.2、binary log 与 index file 一致

# 置空所有 Gtid 信息
mydba@192.168.85.132,3308 [(none)]> reset master;
Query OK, 0 rows affected (0.04 sec)
# 关闭服务
mydba@192.168.85.132,3308 [(none)]> shutdown;

# 将另一实例下的 binlog 拷贝到当前实例的/data/mysql/mysql3308/logs/目录,并修改文件属主
# 并调整 mysql-bin.index 中的顺序

# binlog 列表
[root@ZST1 logs]# ll
total 32
-rw-r-----. 1 mysql mysql 2012 Jan 19 17:11 mysql-bin.000202
-rw-r-----. 1 mysql mysql 1528 Jan 19 17:11 mysql-bin.000203
-rw-r-----. 1 mysql mysql  545 Jan 19 17:11 mysql-bin.000204
-rw-r-----. 1 mysql mysql  873 Jan 19 17:11 mysql-bin.000205
-rw-r-----. 1 mysql mysql  217 Jan 19 17:11 mysql-bin.000206
-rw-r-----. 1 mysql mysql 1056 Jan 19 17:11 mysql-bin.000207
-rw-r-----. 1 mysql mysql  545 Jan 19 17:11 mysql-bin.000208
-rw-r-----. 1 mysql mysql  308 Jan 19 17:12 mysql-bin.index
[root@ZST1 logs]# cat mysql-bin.index 
/data/mysql/mysql3308/logs/mysql-bin.000203
/data/mysql/mysql3308/logs/mysql-bin.000202
/data/mysql/mysql3308/logs/mysql-bin.000204
/data/mysql/mysql3308/logs/mysql-bin.000205
/data/mysql/mysql3308/logs/mysql-bin.000206
/data/mysql/mysql3308/logs/mysql-bin.000208
/data/mysql/mysql3308/logs/mysql-bin.000207
[root@ZST1 logs]# 
# 启动后登录
mydba@192.168.85.132,3308 [(none)]> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000203 |      1528 |
| mysql-bin.000202 |      2012 |
| mysql-bin.000204 |       545 |
| mysql-bin.000205 |       873 |
| mysql-bin.000206 |       217 |
| mysql-bin.000208 |       545 |
| mysql-bin.000207 |      1056 |
| mysql-bin.000209 |       194 |
+------------------+-----------+
rows in set (0.00 sec)
# 查看 mysql.gtid_executed
mydba@192.168.85.132,3308 [(none)]> select * from mysql.gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid                          | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| 8ab82362-9c37-11e7-a858-000c29c1025c |              1 |       507527 |
+--------------------------------------+----------------+--------------+
row in set (0.00 sec)
# 查看当前位置
mydba@192.168.85.132,3308 [(none)]> show master status;
+------------------+----------+--------------+------------------+-----------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                             |
+------------------+----------+--------------+------------------+-----------------------------------------------+
| mysql-bin.000209 |      194 |              |                  | 8ab82362-9c37-11e7-a858-000c29c1025c:1-507527 |
+------------------+----------+--------------+------------------+-----------------------------------------------+
row in set (0.00 sec)
# 查看 gtid_purged
mydba@192.168.85.132,3308 [(none)]> show variables like 'gtid_purged';
+---------------+-----------------------------------------------+
| Variable_name | Value                                         |
+---------------+-----------------------------------------------+
| gtid_purged   | 8ab82362-9c37-11e7-a858-000c29c1025c:1-507517 |
+---------------+-----------------------------------------------+
row in set (0.01 sec)

解析 mysql-bin.000207、mysql-bin.000203

# 解析 mysql-bin.000207
[root@ZST1 logs]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000207 |more
...
COMMIT/*!*/;
# at 681
#180119  9:26:55 server id 1323306  end_log_pos 746 CRC32 0x91996947    GTID    last_committed=2        sequence_number=3       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '8ab82362-9c37-11e7-a858-000c29c1025c:507527'/*!*/;
# at 746
#180119  9:26:55 server id 1323306  end_log_pos 831 CRC32 0x41cdb52a    Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1516325215/*!*/;
BEGIN
/*!*/;
# at 831
#180119  9:26:55 server id 1323306  end_log_pos 894 CRC32 0xfcf11bdb    Table_map: `replcrash`.`py_user` mapped to number 253
# at 894
#180119  9:26:55 server id 1323306  end_log_pos 978 CRC32 0x0550d2ab    Write_rows: table id 253 flags: STMT_END_F
### INSERT INTO `replcrash`.`py_user`
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='d5986a46-fcb7-11e7-ad55-000c29' /* VARSTRING(96) meta=96 nullable=1 is_null=0 */
###   @3='2018-01-19 09:26:55' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @4='1323306' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
# at 978
#180119  9:26:55 server id 1323306  end_log_pos 1009 CRC32 0x15796ccd   Xid = 51
COMMIT/*!*/;
# at 1009
#180119  9:27:10 server id 1323306  end_log_pos 1056 CRC32 0xaf26375b   Rotate to mysql-bin.000208  pos: 4
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*/;
[root@ZST1 logs]# 

gtids_in_binlog:1-507527
mysql.gtid_executed:空
gtid_executed 变量:gtids_in_binlog ∪ mysql.gtid_executed = 1-507527

# 解析 mysql-bin.000203
[root@ZST1 logs]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000203 |more
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180117  9:16:20 server id 1323306  end_log_pos 123 CRC32 0xd61e82fe    Start: binlog v 4, server v 5.7.19-log created 180117  9:16:20 at startup
ROLLBACK/*!*/;
# at 123
#180117  9:16:20 server id 1323306  end_log_pos 194 CRC32 0x962d8c48    Previous-GTIDs
# 8ab82362-9c37-11e7-a858-000c29c1025c:1-507517
# at 194
...

gtids_in_binlog:1-507527
oldest Previous_gtids_log_event:1-507517
gtids_in_binlog_not_purged:gtids_in_binlog - Previous_gtids_log_event = 507518-507527
gtid_purged 变量:gtid_executed - gtids_in_binlog_not_purged = (1-507527) - (507518-507527) = 1-507517

binlog 解析得到的数值和库中查询结果一致~
上面的结果说明 mysql.index 中的首行和末行对应的就是 oldest 和 newest

2.2、gtid_executed 是来自 gtids_in_binlog 和 mysql.gtid_executed 的并集

前面的例子在服务启动时,读取最新的 binlog 中的 Previous_gtids_log_event/Gtid_log_event,使用这些 Gtid 信息初始化 gtid_executed,并将其写入到 mysql.gtid_executed 表
此时不再重置 Gtid 信息,而是继续缩小 mysql-bin.index 首行和末行的范围

# 关闭服务
mydba@192.168.85.132,3308 [(none)]> shutdown;

# 将另一实例下的 binlog 拷贝到当前实例的/data/mysql/mysql3308/logs/目录,并修改文件属主
# 调整 mysql-bin.index 文件中列表顺序

# binlog 列表
[root@ZST1 logs]# ll
total 32
-rw-r-----. 1 mysql mysql 2012 Jan 19 17:11 mysql-bin.000202
-rw-r-----. 1 mysql mysql 1528 Jan 19 17:11 mysql-bin.000203
-rw-r-----. 1 mysql mysql  545 Jan 19 17:11 mysql-bin.000204
-rw-r-----. 1 mysql mysql  873 Jan 19 17:11 mysql-bin.000205
-rw-r-----. 1 mysql mysql  217 Jan 19 17:11 mysql-bin.000206
-rw-r-----. 1 mysql mysql 1056 Jan 19 17:11 mysql-bin.000207
-rw-r-----. 1 mysql mysql  545 Jan 19 17:11 mysql-bin.000208
-rw-r-----. 1 mysql mysql  308 Jan 19 17:31 mysql-bin.index
[root@ZST1 logs]# cat mysql-bin.index 
/data/mysql/mysql3308/logs/mysql-bin.000204
/data/mysql/mysql3308/logs/mysql-bin.000203
/data/mysql/mysql3308/logs/mysql-bin.000202
/data/mysql/mysql3308/logs/mysql-bin.000205
/data/mysql/mysql3308/logs/mysql-bin.000207
/data/mysql/mysql3308/logs/mysql-bin.000208
/data/mysql/mysql3308/logs/mysql-bin.000206
[root@ZST1 logs]# 
# 启动后登录
mydba@192.168.85.132,3308 [(none)]> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000204 |       545 |
| mysql-bin.000203 |      1528 |
| mysql-bin.000202 |      2012 |
| mysql-bin.000205 |       873 |
| mysql-bin.000207 |      1056 |
| mysql-bin.000208 |       545 |
| mysql-bin.000206 |       217 |
| mysql-bin.000209 |       194 |
+------------------+-----------+
rows in set (0.03 sec)
# 查看 mysql.gtid_executed
mydba@192.168.85.132,3308 [(none)]> select * from mysql.gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid                          | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| 8ab82362-9c37-11e7-a858-000c29c1025c |              1 |       507527 |
+--------------------------------------+----------------+--------------+
row in set (0.00 sec)
# 查看当前位置
mydba@192.168.85.132,3308 [(none)]> show master status;
+------------------+----------+--------------+------------------+-----------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                             |
+------------------+----------+--------------+------------------+-----------------------------------------------+
| mysql-bin.000209 |      194 |              |                  | 8ab82362-9c37-11e7-a858-000c29c1025c:1-507527 |
+------------------+----------+--------------+------------------+-----------------------------------------------+
row in set (0.00 sec)
# 查看 gtid_purged
mydba@192.168.85.132,3308 [(none)]> show variables like 'gtid_purged';
+---------------+-------------------------------------------------------------+
| Variable_name | Value                                                       |
+---------------+-------------------------------------------------------------+
| gtid_purged   | 8ab82362-9c37-11e7-a858-000c29c1025c:1-507521:507525-507527 |
+---------------+-------------------------------------------------------------+
row in set (0.00 sec)

解析 mysql-bin.000206、mysql-bin.000204

# 解析 mysql-bin.000206
[root@ZST1 logs]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000206 |more
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180117 11:20:53 server id 1323306  end_log_pos 123 CRC32 0x17238df5    Start: binlog v 4, server v 5.7.19-log created 180117 11:20:53 at startup
ROLLBACK/*!*/;
# at 123
#180117 11:20:53 server id 1323306  end_log_pos 194 CRC32 0x3c03e609    Previous-GTIDs
# 8ab82362-9c37-11e7-a858-000c29c1025c:1-507524
# at 194
#180117 18:09:00 server id 1323306  end_log_pos 217 CRC32 0x53b3b407    Stop
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*/;
[root@ZST1 logs]# 

gtids_in_binlog:1-507524
mysql.gtid_executed:1-507527
gtid_executed 变量:gtids_in_binlog ∪ mysql.gtid_executed = 1-507527

# 解析 mysql-bin.000204
[root@ZST1 logs]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000204 |more
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180117 10:26:57 server id 1323306  end_log_pos 123 CRC32 0xee45d7bf    Start: binlog v 4, server v 5.7.19-log created 180117 10:26:57
# at 123
#180117 10:26:57 server id 1323306  end_log_pos 194 CRC32 0xf8024ebe    Previous-GTIDs
# 8ab82362-9c37-11e7-a858-000c29c1025c:1-507521
# at 194
...

gtids_in_binlog:1-507524
oldest Previous_gtids_log_event:1-507521
gtids_in_binlog_not_purged:gtids_in_binlog - Previous_gtids_log_event = 507522-507524
gtid_purged 变量:gtid_executed - gtids_in_binlog_not_purged = (1-507527) - (507522-507524) = 1-507521:507525-507527

上面的结果说明 gtid_executed 变量取的是 gtids_in_binlog 和 mysql.gtid_executed 的并集,并不是单纯来自某一个值~

2.3、binlog 在 Rotate 或者关闭服务时,会把 Gtid 信息写入到 mysql.gtid_executed 表

2.3.1、关闭服务
写入一批数据,关闭服务,删除 binlog 后再启动服务,查看 mysql.gtid_executed 表

# 置空所有 Gtid 信息
mydba@192.168.85.132,3306 [replcrash]> reset master;
Query OK, 0 rows affected (0.01 sec)
# 清空测试数据表
mydba@192.168.85.132,3306 [replcrash]> truncate table py_user;
Query OK, 0 rows affected (1.49 sec)
# 写入数据(执行两次)
mydba@192.168.85.132,3306 [replcrash]> insert into py_user(name,server_id) select left(uuid(),30),@@server_id;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
# 查看表中数据
mydba@192.168.85.132,3306 [replcrash]> select * from py_user;
+-----+--------------------------------+---------------------+-----------+
| uid | name                           | add_time            | server_id |
+-----+--------------------------------+---------------------+-----------+
|   1 | 5a57574a-0242-11e8-9655-000c29 | 2018-01-26 10:41:04 | 1323306   |
|   2 | 65e773f6-0242-11e8-9655-000c29 | 2018-01-26 10:41:23 | 1323306   |
+-----+--------------------------------+---------------------+-----------+
rows in set (0.00 sec)
# 查看 mysql.gtid_executed
mydba@192.168.85.132,3306 [replcrash]> select * from mysql.gtid_executed;
Empty set (0.00 sec)
# 查看当前位置
mydba@192.168.85.132,3306 [replcrash]> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 |      969 |              |                  | 60863f8d-01af-11e8-bfdf-000c29c1025c:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
row in set (0.00 sec)
# 查看 gtid_purged
mydba@192.168.85.132,3306 [replcrash]> show variables like 'gtid_purged';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_purged   |       |
+---------------+-------+
row in set (0.00 sec)
# 关闭服务
mydba@192.168.85.132,3306 [replcrash]> shutdown;

# 此时的 binlog
[root@ZST1 logs]# pwd
/data/mysql/mysql3306/logs
[root@ZST1 logs]# ll
total 8
-rw-r----- 1 mysql mysql 992 Jan 26 10:43 mysql-bin.000001
-rw-r----- 1 mysql mysql  44 Jan 26 10:38 mysql-bin.index
[root@ZST1 logs]# cat mysql-bin.index 
/data/mysql/mysql3306/logs/mysql-bin.000001
[root@ZST1 logs]# 
mysql-bin.000001 记录 60863f8d-01af-11e8-bfdf-000c29c1025c:1-3 的信息
# 删除 binlog
[root@ZST1 logs]# cp -r ../logs ../logs_bak
[root@ZST1 logs]# rm -rf *

# 启动服务
[root@ZST1 logs]# sh ~/start3306.sh
# 查看 mysql.gtid_executed
mydba@192.168.85.132,3306 [replcrash]> select * from mysql.gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid                          | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| 60863f8d-01af-11e8-bfdf-000c29c1025c |              1 |            3 |
+--------------------------------------+----------------+--------------+
row in set (0.00 sec)
# 查看当前位置
mydba@192.168.85.132,3306 [replcrash]> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 |      154 |              |                  | 60863f8d-01af-11e8-bfdf-000c29c1025c:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
row in set (0.00 sec)
# 查看 gtid_purged
mydba@192.168.85.132,3306 [replcrash]> show variables like 'gtid_purged';
+---------------+------------------------------------------+
| Variable_name | Value                                    |
+---------------+------------------------------------------+
| gtid_purged   | 60863f8d-01af-11e8-bfdf-000c29c1025c:1-3 |
+---------------+------------------------------------------+
row in set (0.01 sec)

重启前 mysql.gtid_executed 的 Gtid 为空,关闭服务后删除所有 binlog,重启后 mysql.gtid_executed 的 Gtid:1-3。它的信息不可能来自 binlog,只有可能在关闭服务时 Gtid 已写入到 mysql.gtid_executed
2.3.2、flush binary logs
写入一批数据,flush binary logs,查看 mysql.gtid_executed 表,并解析 binlog 日志

# 写入数据(执行两次)
mydba@192.168.85.132,3306 [replcrash]> insert into py_user(name,server_id) select left(uuid(),30),@@server_id;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0
# 查看 mysql.gtid_executed
mydba@192.168.85.132,3306 [replcrash]> select * from mysql.gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid                          | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| 60863f8d-01af-11e8-bfdf-000c29c1025c |              1 |            3 |
+--------------------------------------+----------------+--------------+
row in set (0.00 sec)
# 查看当前位置
mydba@192.168.85.132,3306 [replcrash]> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 |      810 |              |                  | 60863f8d-01af-11e8-bfdf-000c29c1025c:1-5 |
+------------------+----------+--------------+------------------+------------------------------------------+
row in set (0.00 sec)
# 查看 mysql.gtid_purged
mydba@192.168.85.132,3306 [replcrash]> show variables like 'gtid_purged';
+---------------+------------------------------------------+
| Variable_name | Value                                    |
+---------------+------------------------------------------+
| gtid_purged   | 60863f8d-01af-11e8-bfdf-000c29c1025c:1-3 |
+---------------+------------------------------------------+
row in set (0.01 sec)
写入数据,mysql.gtid_executed 表并不会时时更新

# 切换日志
mydba@192.168.85.132,3306 [replcrash]> flush binary logs;
Query OK, 0 rows affected (0.01 sec)
# 查看 mysql.gtid_executed
mydba@192.168.85.132,3306 [replcrash]> select * from mysql.gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid                          | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| 60863f8d-01af-11e8-bfdf-000c29c1025c |              1 |            5 |
+--------------------------------------+----------------+--------------+
row in set (0.00 sec)
# 查看当前位置
mydba@192.168.85.132,3306 [replcrash]> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000002 |      194 |              |                  | 60863f8d-01af-11e8-bfdf-000c29c1025c:1-5 |
+------------------+----------+--------------+------------------+------------------------------------------+
row in set (0.00 sec)
# 查看 mysql.gtid_purged
mydba@192.168.85.132,3306 [replcrash]> show variables like 'gtid_purged';
+---------------+------------------------------------------+
| Variable_name | Value                                    |
+---------------+------------------------------------------+
| gtid_purged   | 60863f8d-01af-11e8-bfdf-000c29c1025c:1-3 |
+---------------+------------------------------------------+
row in set (0.01 sec)
flush logs 后 gtid_purged 变量的值马上更新到 mysql.gtid_executed 表

mysql.gtid_executed 表并不是时时更新,flush logs 后 gtid_executed 变量的值马上更新到 mysql.gtid_executed 表
解析 binlog 日志

# 解析切换后的日志
[root@ZST1 logs]# ll
total 12
-rw-r----- 1 mysql mysql 857 Jan 26 11:13 mysql-bin.000001
-rw-r----- 1 mysql mysql 194 Jan 26 11:13 mysql-bin.000002
-rw-r----- 1 mysql mysql  88 Jan 26 11:13 mysql-bin.index
[root@ZST1 logs]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180126 11:13:02 server id 1323306  end_log_pos 123 CRC32 0xea188782    Start: binlog v 4, server v 5.7.21-log created 180126 11:13:02
# Warning: this binlog is either in use or was not closed properly.
# at 123
#180126 11:13:02 server id 1323306  end_log_pos 194 CRC32 0xdf719f1d    Previous-GTIDs
# 60863f8d-01af-11e8-bfdf-000c29c1025c:4-5
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*/;
[root@ZST1 logs]#

注意 Rotate 后的 binlog 中 Previous-GTIDs 是 4-5 ,而不是 1-5。因为 Rotate 只把 binary log 中的 Gtid 写入到新 binary log,同时更新 mysql.gtid_executed 表

2.4、如何通过 binlog 和 mysql.gtid_executed 得到 gtid_executed、gtid_purged

搞清楚服务启动前 mysql.gtid_executed 的值。对于 5.7.7 及以后默认只需迭代最新和最旧 binlog 中的 Previous_gtids_log_event/Gtid_log_event,计算 gtids_in_binlog、gtids_in_binlog_not_purged,然后根据公式计算 gtid_executed、gtid_purged 变量。可以查看前面解析 binlog 的例子

三、参考文档

Global Transaction ID Options and Variables:https://dev.mysql.com/doc/refman/5.7/en/replication-options-gtids.html
MySQL 5.7 Gtid 内部学习(五) mysql.gtid_executed 表/gtid_executed 变量/gtid_purged 变量的更改时机:https://yq.aliyun.com/articles/294004
[MySQL 5.6] GTID 实现、运维变化及存在的 bug:http://www.cnblogs.com/MYSQLZOUQI/p/3850578.html

gtid_executed 和 gtid_purged 变量是如何初始化的 https://www.cnblogs.com/Uest/p/8319134.html 


IT 敢客 , 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权
转载请注明原文链接:深刻理解 gtid_executed 和 gtid_purged
喜欢 (8)
[313176056@qq.com]
分享 (0)
IT敢客
关于作者:
“我所做的一切都是为了方便我的生活~~~“
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址