MySQL运维之 binlog_gtid_simple_recovery(GTID)

本文涉及的产品
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介:

binlog_gtid_simple_recovery 是什么

  • 官方解释
This variable controls how binary log files are iterated during the search for GTIDs when MySQL starts or restarts.
In MySQL version 5.7.5, this variable was added as simplified_binlog_gtid_recovery and in MySQL version 5.7.6 it was renamed to binlog_gtid_simple_recovery.

When binlog_gtid_simple_recovery=FALSE, the method of iterating the binary log files is:

To initialize gtid_executed, binary log files are iterated from the newest file, stopping at the first binary log that has any Previous_gtids_log_event.
All GTIDs from Previous_gtids_log_event and Gtid_log_events are read from this binary log file.
This GTID set is stored internally and called gtids_in_binlog.
The value of gtid_executed is computed as the union of this set and the GTIDs stored in the mysql.gtid_executed table.

This process could take a long time if you had a large number of binary log files without GTID events, for example created when gtid_mode=OFF.

To initialize gtid_purged, binary log files are iterated from the oldest to the newest,
stopping at the first binary log that contains either a Previous_gtids_log_event that is nonempty (that has at least one GTID)
or that has at least one Gtid_log_event. From this binary log it reads Previous_gtids_log_event.
This GTID set is subtracted from gtids_in_binlog and the result stored in the internal variable gtids_in_binlog_not_purged.
The value of gtid_purged is initialized to the value of gtid_executed, minus gtids_in_binlog_not_purged.

When binlog_gtid_simple_recovery=TRUE, which is the default in MySQL 5.7.7 and later,
the server iterates only the oldest and the newest binary log files
and the values of gtid_purged and gtid_executed are computed based only on Previous_gtids_log_event or Gtid_log_event found in these files.
This ensures only two binary log files are iterated during server restart or when binary logs are being purged.
  • 官方注意点
Note

If this option is enabled, gtid_executed and gtid_purged may be initialized incorrectly in the following situations:

The newest binary log was generated by MySQL 5.7.5 or older, and gtid_mode was ON for some binary logs but OFF for the newest binary log.

A SET GTID_PURGED statement was issued on a MySQL version prior to 5.7.7, and the binary log that was active at the time of the SET GTID_PURGED has not yet been purged.

If an incorrect GTID set is computed in either situation, it will remain incorrect even if the server is later restarted, regardless of the value of this option.
  • 个人理解与总结
1. 这个变量用于在MySQL重启或启动的时候寻找GTIDs过程中,控制binlog 如何遍历的算法?
2. 当binlog_gtid_simple_recovery=FALSE 时:
    为了初始化 gtid_executed,算法是: 从newest_binlog -> oldest_binlog 方向遍历读取,如果发现有Previous_gtids_log_event , 那么就停止遍历
    为了初始化 gtid_purged,算法是:   从oldest_binlog -> newest_binlog 方向遍历读取, 如果发现有Previous_gtids_log_event(not empty)或者 至少有一个Gtid_log_event的文件,那么就停止遍历
3. 当binlog_gtid_simple_recovery=TRUE 时:
    为了初始化 gtid_executed , 算法是: 只需要读取newest_binlog
    为了初始化 gtid_purged, 算法是: 只需要读取oldest_binlog
4. 当设置binlog_gtid_simple_recovery=TRUE , 如果MySQL版本低于5.7.7 , 可能会有gitd计算出错的可能,具体参考官方文档详细描述

根据以上解读,那么如果存在非gtid的binlog比较多的时候,会非常影响性能的。
接下来,我们就来好好测试这种场景

测试案例

重点测试non-gtid和gtid混合的情况: This process could take a long time if you had a large number of binary log files without GTID events, for example created when gtid_mode=OFF.
测试当删除binlog的时候,是如何重置gtid_purged值的

  • 环境
    MySQL5.7.13
    binlog_gtid_simple_recovery = false   => 这是重点
    GTID升级:non-GTID -> GTID 后,purge binary logs  => 这也是重点
  • binlog
-rw-r-----  1 mysql mysql        177 May  3 11:23 tjtx-126-164.000001
-rw-r-----  1 mysql mysql 1074589597 May  3 11:29 tjtx-126-164.000002
-rw-r-----  1 mysql mysql 1074589060 May  3 11:30 tjtx-126-164.000003
-rw-r-----  1 mysql mysql 1074589063 May  3 11:31 tjtx-126-164.000004
-rw-r-----  1 mysql mysql 1074589065 May  3 11:32 tjtx-126-164.000005
-rw-r-----  1 mysql mysql 1074589051 May  3 11:33 tjtx-126-164.000006
-rw-r-----  1 mysql mysql 1074589045 May  3 11:33 tjtx-126-164.000007
-rw-r-----  1 mysql mysql 1074589047 May  3 11:34 tjtx-126-164.000008
-rw-r-----  1 mysql mysql 1074589050 May  3 11:35 tjtx-126-164.000009
-rw-r-----  1 mysql mysql 1074589052 May  3 11:36 tjtx-126-164.000010
-rw-r-----  1 mysql mysql 1074589062 May  3 11:37 tjtx-126-164.000011
-rw-r-----  1 mysql mysql 1074589068 May  3 11:37 tjtx-126-164.000012
-rw-r-----  1 mysql mysql 1074589045 May  3 11:38 tjtx-126-164.000013
-rw-r-----  1 mysql mysql 1074589038 May  3 11:39 tjtx-126-164.000014
-rw-r-----  1 mysql mysql 1074589055 May  3 11:40 tjtx-126-164.000015
-rw-r-----  1 mysql mysql 1074589050 May  3 11:41 tjtx-126-164.000016
-rw-r-----  1 mysql mysql 1074589063 May  3 11:41 tjtx-126-164.000017
-rw-r-----  1 mysql mysql 1074589055 May  3 11:42 tjtx-126-164.000018
-rw-r-----  1 mysql mysql 1074589048 May  3 11:43 tjtx-126-164.000019
-rw-r-----  1 mysql mysql 1074515950 May  3 11:45 tjtx-126-164.000020
-rw-r-----  1 mysql mysql 1074589069 May  3 11:46 tjtx-126-164.000021
-rw-r-----  1 mysql mysql 1074589051 May  3 11:47 tjtx-126-164.000022
-rw-r-----  1 mysql mysql 1074589063 May  3 11:47 tjtx-126-164.000023
-rw-r-----  1 mysql mysql 1074589051 May  3 11:48 tjtx-126-164.000024
-rw-r-----  1 mysql mysql  321034919 May  3 13:53 tjtx-126-164.000025
-rw-r-----  1 mysql mysql        204 May  3 13:53 tjtx-126-164.000026
-rw-r-----  1 mysql mysql        204 May  3 13:53 tjtx-126-164.000027
-rw-r-----  1 mysql mysql       1092 May  3 13:55 tjtx-126-164.000028
-rw-r-----  1 mysql mysql        194 May  3 13:55 tjtx-126-164.000029

tjtx-126-164.000001 ~ tjtx-126-164.000028

    Previous-GTIDs
    # [empty]


tjtx-126-164.000029

    #180503 13:55:05 server id 1261261646  end_log_pos 194 CRC32 0xb77b80b7     Previous-GTIDs
    # 0923e916-3c36-11e6-82a5-ecf4bbf1f518:1-3
  • 测试开始
<master>

dba:lc> purge binary logs to 'tjtx-126-164.000005';
Query OK, 0 rows affected (1 min 14.41 sec)   --执行时间竟然长达一分钟

dba:lc> insert into t select 300;  --master的事务卡住
Query OK, 1 row affected (1 min 9.42 sec)
Records: 1  Duplicates: 0  Warnings: 0

  • strace跟踪
在从头到尾遍历binlog  ,从而再次验证了我们之前的算法理论。


63639 14:07:50.394945 read(55, "05488963387-39206410793-66801786"..., 8192) = 8192 <0.000011>
63639 14:07:50.395005 read(55, "-66498258471-55447794725-7620591"..., 8192) = 8192 <0.000010>
63639 14:07:50.395065 read(55, "7;10709822844-35491948145-283531"..., 8192) = 8192 <0.000012>
63639 14:07:50.395129 read(55, "17-05336385032;74931753923-32217"..., 8192) = 8192 <0.000011>
63639 14:07:50.395191 read(55, "053-81565945575-96536403914;8342"..., 8192) = 8192 <0.000011>
63639 14:07:50.395250 read(55, "7139-77543559499-90858749831-907"..., 8192) = 8192 <0.000010>
63639 14:07:50.395310 read(55, "07981-10898305107-65423962210-93"..., 8192) = 8192 <0.000011>
63639 14:07:50.395371 read(55, "009985-68038808770-60998915978-7"..., 8192) = 8192 <0.000010>
63639 14:07:50.395430 read(55, "3665266-98504623794-11513728759-"..., 8192) = 8192 <0.000011>
63639 14:07:50.395491 read(55, "54495717-21332716078-74081433759"..., 8192) = 8192 <0.000010>
63639 14:07:50.395550 read(55, "873221923-40252274459-8633934300"..., 8192) = 8192 <0.000010>
63639 14:07:50.395610 read(55, "2609904861-91693621073-471178324"..., 8192) = 8192 <0.000010>
63639 14:07:50.125372 open("/data/mysql.bin/tjtx-126-164.~rec~", O_RDWR|O_CREAT, 0640) = 53 <0.000039>
63639 14:07:50.125769 open("/data/mysql.bin/tjtx-126-164.index_crash_safe", O_RDWR|O_CREAT, 0640) = 55 <0.000031>
63639 14:07:50.126150 open("/data/mysql.bin/tjtx-126-164.index", O_RDWR|O_CREAT, 0640) = 3 <0.000013>

。。。。。。。。。。。。。。。。。
63639 14:07:50.126554 open("/data/mysql.bin/tjtx-126-164.000005", O_RDONLY) = 55 <0.000012>
63639 14:07:53.857069 open("/data/mysql.bin/tjtx-126-164.000006", O_RDONLY) = 55 <0.000018>
63639 14:07:57.516826 open("/data/mysql.bin/tjtx-126-164.000007", O_RDONLY) = 55 <0.000016>
63639 14:08:01.169413 open("/data/mysql.bin/tjtx-126-164.000008", O_RDONLY) = 55 <0.000018>
63639 14:08:04.815608 open("/data/mysql.bin/tjtx-126-164.000009", O_RDONLY) = 55 <0.000015>
63639 14:08:08.473808 open("/data/mysql.bin/tjtx-126-164.000010", O_RDONLY) = 55 <0.000015>
63639 14:08:12.449964 open("/data/mysql.bin/tjtx-126-164.000011", O_RDONLY) = 55 <0.000018>
63639 14:08:16.251054 open("/data/mysql.bin/tjtx-126-164.000012", O_RDONLY) = 55 <0.000019>
63639 14:08:19.686003 open("/data/mysql.bin/tjtx-126-164.000013", O_RDONLY) = 55 <0.000015>
63639 14:08:23.341291 open("/data/mysql.bin/tjtx-126-164.000014", O_RDONLY) = 55 <0.000017>
63639 14:08:27.014210 open("/data/mysql.bin/tjtx-126-164.000015", O_RDONLY) = 55 <0.000016>
63639 14:08:30.625242 open("/data/mysql.bin/tjtx-126-164.000016", O_RDONLY) = 55 <0.000016>
63639 14:08:34.192385 open("/data/mysql.bin/tjtx-126-164.000017", O_RDONLY) = 55 <0.000015>
63639 14:08:37.862750 open("/data/mysql.bin/tjtx-126-164.000018", O_RDONLY) = 55 <0.000016>
63639 14:08:41.533869 open("/data/mysql.bin/tjtx-126-164.000019", O_RDONLY) = 55 <0.000016>
63639 14:08:45.202949 open("/data/mysql.bin/tjtx-126-164.000020", O_RDONLY) = 55 <0.000017>
63639 14:08:48.792088 open("/data/mysql.bin/tjtx-126-164.000021", O_RDONLY) = 55 <0.000017>
63639 14:08:52.266700 open("/data/mysql.bin/tjtx-126-164.000022", O_RDONLY) = 55 <0.000017>
63639 14:08:55.932879 open("/data/mysql.bin/tjtx-126-164.000023", O_RDONLY) = 55 <0.000017>
63639 14:08:59.594761 open("/data/mysql.bin/tjtx-126-164.000024", O_RDONLY) = 55 <0.000015>
63639 14:09:03.256451 open("/data/mysql.bin/tjtx-126-164.000025", O_RDONLY) = 55 <0.000015>
63639 14:09:04.349108 open("/data/mysql.bin/tjtx-126-164.000026", O_RDONLY) = 55 <0.000014>
63639 14:09:04.349280 open("/data/mysql.bin/tjtx-126-164.000027", O_RDONLY) = 55 <0.000010>
63639 14:09:04.349434 open("/data/mysql.bin/tjtx-126-164.000028", O_RDONLY) = 55 <0.000010>


查看fd=55的句柄:

[root@tjtx-126-164 tmp]# ll /proc/62382/fd | grep 55
lr-x------ 1 root root 64 May  3 14:17 55 -> /data/mysql.bin/tjtx-126-164.000009
[root@tjtx-126-164 tmp]# ll /proc/62382/fd | grep 55
lr-x------ 1 root root 64 May  3 14:17 55 -> /data/mysql.bin/tjtx-126-164.000010
[root@tjtx-126-164 tmp]# ll /proc/62382/fd | grep 55
lr-x------ 1 root root 64 May  3 14:17 55 -> /data/mysql.bin/tjtx-126-164.000010
[root@tjtx-126-164 tmp]# ll /proc/62382/fd | grep 55
lr-x------ 1 root root 64 May  3 14:17 55 -> /data/mysql.bin/tjtx-126-164.000010



测试二

  • 环境

    MySQL5.7.13
    binlog_gtid_simple_recovery = true
    non-GTID -> GTID 后,purge binary logs
  • 模拟开始
dba:(none)> purge binary logs to 'tjtx-126-164.000007';
Query OK, 0 rows affected (4.06 sec)  --非常快


dba:(none)> show global variables like '%gtid%';
+----------------------------------+------------------------------------------+
| Variable_name                    | Value                                    |
+----------------------------------+------------------------------------------+
| binlog_gtid_simple_recovery      | ON                                       |
| enforce_gtid_consistency         | ON                                       |
| gtid_executed                    | 0923e916-3c36-11e6-82a5-ecf4bbf1f518:1-7 |
| gtid_executed_compression_period | 1000                                     |
| gtid_mode                        | ON                                       |
| gtid_owned                       |                                          |
| gtid_purged                      |                                          |
| session_track_gtids              | OFF                                      |
+----------------------------------+------------------------------------------+
8 rows in set (0.00 sec)

strace分析:只读取了oldest的binlog 文件

115529 14:31:31.096480 open("/data/mysql.bin/tjtx-126-164.~rec~", O_RDWR|O_CREAT, 0640) = 51 <0.000031>
115529 14:31:31.096777 open("/data/mysql.bin/tjtx-126-164.index_crash_safe", O_RDWR|O_CREAT, 0640) = 52 <0.000029>
115529 14:31:31.097111 open("/data/mysql.bin/tjtx-126-164.index", O_RDWR|O_CREAT, 0640) = 3 <0.000023>
115529 14:31:31.097502 open("/data/mysql.bin/tjtx-126-164.000007", O_RDONLY) = 52 <0.000012>


dba:(none)> purge binary logs to 'tjtx-126-164.000029';
Query OK, 0 rows affected (0.00 sec)

dba:(none)> show global variables like '%gtid%';
+----------------------------------+------------------------------------------+
| Variable_name                    | Value                                    |
+----------------------------------+------------------------------------------+
| binlog_gtid_simple_recovery      | ON                                       |
| enforce_gtid_consistency         | ON                                       |
| gtid_executed                    | 0923e916-3c36-11e6-82a5-ecf4bbf1f518:1-7 |
| gtid_executed_compression_period | 1000                                     |
| gtid_mode                        | ON                                       |
| gtid_owned                       |                                          |
| gtid_purged                      | 0923e916-3c36-11e6-82a5-ecf4bbf1f518:1-3 |  --直到000029这个binlog文件读取,才能初始化gitid_purged值,否则为空
| session_track_gtids              | OFF                                      |
+----------------------------------+------------------------------------------+
8 rows in set (0.00 sec)



算法总结

1. MySQL重启
    当binlog_gtid_simple_recovery=FALSE 时:
        为了初始化 gtid_executed,算法是: 从newest_binlog -> oldest_binlog 方向遍历读取,如果发现有Previous_gtids_log_event , 那么就停止遍历。
        为了初始化 gtid_purged,算法是:   从oldest_binlog -> newest_binlog 方向遍历读取, 如果发现有Previous_gtids_log_event(not empty)或者 至少有一个Gtid_log_event的文件,那么就停止遍历
    当binlog_gtid_simple_recovery=TRUE 时:
           为了初始化 gtid_executed , 算法是: 只需要读取newest_binlog。 如果没有,则为空
        为了初始化 gtid_purged, 算法是: 只需要读取oldest_binlog。如果没有,则为空

2. binlog rotate(expire_logs_day , purge binary logs to '' 等)
    当binlog_gtid_simple_recovery=FALSE 时:
        为了初始化 gtid_purged , 从oldest_binlog -> newest_binlog 方向遍历读取, 如果发现有Previous_gtids_log_event(not empty)或者 至少有一个Gtid_log_event的文件,那么就停止遍历
    当binlog_gtid_simple_recovery=TRUE 时:
        为了初始化 gtid_purged, 算法是: 只需要读取oldest_binlog。 如果没有,则为空

需要注意的点

  • 在线GTID升级的时候,binlog_gtid_simple_recovery = TRUE 必须打开,否则在binlog 删除的时候,会发生阻塞状况
  • 在线GTID升级的时候,尽量将非GTID的binlog备份好,然后删除掉,以免出现莫名其妙的错误
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
15天前
|
存储 安全 关系型数据库
Mysql 的binlog日志的优缺点
MySQL的binlog(二进制日志)是一个记录数据库更改的日志文件,它包含了所有对数据库执行的更改操作,如INSERT、UPDATE和DELETE等。binlog的主要目的是复制和恢复。以下是binlog日志的优缺点: ### 优点: 1. **数据恢复**:当数据库出现意外故障或数据丢失时,可以利用binlog进行点恢复(point-in-time recovery),将数据恢复到某一特定时间点。 2. **主从复制**:binlog是实现MySQL主从复制功能的核心组件。主服务器将binlog中的事件发送到从服务器,从服务器再重放这些事件,从而实现数据的同步。 3. **审计**:b
|
24天前
|
SQL 关系型数据库 MySQL
mysql的binlog恢复数据
mysql的binlog恢复数据
26 0
|
1月前
|
存储 关系型数据库 MySQL
RDS MySQL 数据库运维简述
从运维的视角,汇总云数据库RDS MySQL使用的避坑指南。文章初版,维护更新,欢迎指点。
766 3
|
2月前
|
存储 SQL 安全
浅谈MySQL Binlog
浅谈MySQL Binlog
45 0
|
2月前
|
监控 关系型数据库 MySQL
MySQL Binlog实战:在生产环境中的应用与最佳实践【实战应用】
MySQL Binlog实战:在生产环境中的应用与最佳实践【实战应用】
35 0
|
2月前
|
SQL 监控 关系型数据库
MySQL Binlog深度解析:进阶应用与实战技巧【进阶应用】
MySQL Binlog深度解析:进阶应用与实战技巧【进阶应用】
44 0
|
4月前
|
缓存 关系型数据库 MySQL
MySQL Binlog--事务日志和BINLOG落盘参数对磁盘IO的影响
MySQL Binlog--事务日志和BINLOG落盘参数对磁盘IO的影响
47 0
|
2月前
|
SQL 存储 关系型数据库
解析MySQL Binlog:从零开始的入门指南【binlog入门指南】
解析MySQL Binlog:从零开始的入门指南【binlog入门指南】
1025 0
|
3月前
|
关系型数据库 MySQL 数据库
Mysql数据库redo log及binlog的写入
Mysql数据库redo log及binlog的写入
|
5月前
|
关系型数据库 MySQL Shell
如何用shell脚本获取mysql的binlog日志
如何用shell脚本获取mysql的binlog日志
63 1