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備份好，然後刪除掉，以免出現莫名其妙的錯誤
相關文章
- MySQL運維實戰(7.1) 開啟GTID複製MySql運維
- 深入理解MySQL5.7GTID系列（七）binlog_gtid_simple_recovery引數的影響總結MySql
- 四：GTID中的運維（筆記）運維筆記
- 第3節：GTID模組初始化簡介和引數binlog_gtid_simple_recovery
- MySQL主從複製之GTID複製MySql
- mysql運維案例MySql運維
- IT運維之自動化運維運維
- MySQL基礎運維——percona-toolkit運維工具MySql運維
- MySQL GTID生命週期MySql
- MySQL 5.6 GTID 原理以及使用MySql
- mysql運維利器percona-toolkit工具之pt-query-digestMySql運維
- MySQL運維5-Mycat配置MySql運維
- 智慧運維基礎－運維知識庫之ETL運維
- MySQL運維12-Mycat分庫分表之按天分片MySql運維
- Linux運維必會的100道MySql面試題之（三）Linux運維MySql面試題
- 基於GTID搭建主從MySQLMySql
- MySQL主從複製之GTID模式詳細介紹鞴嬈MySql模式
- 運維安全之NFS安全運維NFS
- Linux運維之程式管理①Linux運維
- MySQL運維11-Mycat分庫分表之應用指定分片MySql運維
- MySQL運維4-Mycat入門MySql運維
- MySQL 資料庫日常運維文件MySql資料庫運維
- mysql從庫gtid間隙問題MySql
- Mysql基於GTID的複製模式MySql模式
- Mysql 基於GTID主從複製MySql
- 第2節：mysql.gtid_executed表/gtid_executed變數/gtid_purged變數的更改時機MySql變數
- MySQL運維實戰系列：MySQL5.7GroupBy問題MySql運維
- MySQL運維實戰（7）建立複製MySql運維
- MySQL運維2-主從複製MySql運維
- MySQL運維6-Mycat垂直分庫MySql運維
- MySQL運維7-Mycat水平分表MySql運維
- 【MySQL】MySQL Replication 一主一備搭建步驟（GTID方式）MySql
- MySQL 8 複製（四）——GTID與複製MySql
- MySQL 8 複製（五）——配置GTID複製MySql
- MySQL 線上開啟&關閉GTID模式MySql模式
- MySQL GTID複製錯誤修復演示MySql
- 大規模MySQL運維陷阱之基於MyCat的偽分散式架構MySql運維分散式架構
- MySQL運維實戰之備份和恢復（8.1）xtrabackup全量備份MySql運維