MySQL运维之 binlog_gtid_simple_recovery(GTID)

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备份好，然后删除掉，以免出现莫名其妙的错误