gtid_executed和gtid_purged变量是如何初始化的

一、官方释义

1.1、gtid_executed、gtid_purged

https://dev.mysql.com/doc/refman/5.7/en/replication-options-gtids.html#sysvar_gtid_executed
• gtid_executed
When used with global scope, this variable contains a representation of the set of all transactions executed on the server and GTIDs that have been set by a SET gtid_purged statement. This is the same as the value of the Executed_Gtid_Set column in the output of SHOW MASTER STATUS and SHOW SLAVE STATUS.
• gtid_purged
The set of all transactions that have been purged from the binary log. This is a subset of the set of transactions in 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
binlog_gtid_simple_recovery=FALSE
• 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 non-empty (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.
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.

数据库服务启动时，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
GTIDs are stored in the mysql.gtid_executed table only when gtid_mode is ON or ON_PERMISSIVE. The point at which GTIDs are stored depends on whether binary logging is enabled or disabled:
• If binary logging is disabled (log_bin is OFF), or if log_slave_updates is disabled, the server stores the GTID belonging to each transaction together with the transaction in the table. In addition, the table is compressed periodically at a user-configurable rate; see mysql.gtid_executed Table Compression, for more information. This situation can only apply on a replication slave where binary logging or slave update logging is disabled. It does not apply on a replication master, because on a master, binary logging must be enabled for replication to take place.
• If binary logging is enabled (log_bin is ON), whenever the binary log is rotated or the server is shutdown, the server writes GTIDs for all transactions that were written into the previous binary log into the mysql.gtid_executed table. This situation applies on a replication master, or a replication slave where binary logging is enabled.
In the event of the server stopping unexpectedly, the set of GTIDs from the current binary log is not saved in the mysql.gtid_executed table. In this case, these GTIDs are added to the table and to the set of GTIDs in the gtid_executed system variable during recovery.
When binary logging is enabled, the mysql.gtid_executed table does not provide a complete record of the GTIDs for all executed transactions. That information is provided by the global value of the gtid_executed system variable.

如果没有开启log_bin或者没有开启log_slave_updates，从库在应用relay-log中的每个事务会执行一个insert mysql.gtid_executed操作。这只针对从库而言~
如果开启log_bin，在binlog发生rotate(flush binary logs/达到max_binlog_size)或者关闭服务时，会把所有写入到binlog中的Gtid信息写入到mysql.gtid_executed表。这适用于主库和从库~
log_bin=on，MySQL 8.0.17 起每个事务提交时会更新mysql.gtid_executed表
例子：从库log_bin=on，log_slave_updates=off，那么在应用relay-log时会实时写入mysql.gtid_executed，而在从库直接写入数据，需要等到发生rotate或者关闭服务才写入~
mysql.gtid_executed压缩：log-bin=off，每gtid_executed_compression_period压缩一次；log-bin=on，日志切换时压缩~
如果发生异常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

View Code

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 |
+------------------+-----------+
8 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 |
+--------------------------------------+----------------+--------------+
1 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 |
+------------------+----------+--------------+------------------+-----------------------------------------------+
1 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 |
+---------------+-----------------------------------------------+
1 row in set (0.04 sec)

View Code

解析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 - oldest Previous_gtids_log_event = [1-507528] - [1-507510] = [507511-507528]
gtid_purged变量：gtid_executed - gtids_in_binlog_not_purged = [1-507528] - [507511-507528] = [1-507510]

View Code

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 |
+------------------+-----------+
8 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 |
+--------------------------------------+----------------+--------------+
1 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 |
+------------------+----------+--------------+------------------+-----------------------------------------------+
1 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 |
+---------------+-----------------------------------------------+
1 row in set (0.01 sec)

View Code

解析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 - oldest Previous_gtids_log_event = [1-507527] - [1-507517] = [507518-507527]
gtid_purged变量：gtid_executed - gtids_in_binlog_not_purged = [1-507527] - [507518-507527] = [1-507517]

View Code

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 |
+------------------+-----------+
8 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 |
+--------------------------------------+----------------+--------------+
1 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 |
+------------------+----------+--------------+------------------+-----------------------------------------------+
1 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 |
+---------------+-------------------------------------------------------------+
1 row in set (0.00 sec)

View Code

解析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 - oldest Previous_gtids_log_event = [1-507524] - [1-507521] = [507522-507524]
gtid_purged变量：gtid_executed - gtids_in_binlog_not_purged = [1-507527] - [507522-507524] = [1-507521:507525-507527]

View Code

上面的结果说明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   |
+-----+--------------------------------+---------------------+-----------+
2 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 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 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   |       |
+---------------+-------+
1 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 |
+--------------------------------------+----------------+--------------+
1 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 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 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 |
+---------------+------------------------------------------+
1 row in set (0.01 sec)

View Code

重启前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 |
+--------------------------------------+----------------+--------------+
1 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 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 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 |
+---------------+------------------------------------------+
1 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 |
+--------------------------------------+----------------+--------------+
1 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 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 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 |
+---------------+------------------------------------------+
1 row in set (0.01 sec)
flush logs后gtid_purged变量的值马上更新到mysql.gtid_executed表

View Code

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]#

View Code

注意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
binlog rotate引发的MySQL阻塞事件：https://mp.weixin.qq.com/s/XSnFkuYzIlGWMaXIl-oPeQ

posted @ 2018-01-19 22:56 Uest 阅读(9878) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

	【作者】：醒嘞
	【出处】： http://www.cnblogs.com/Uest/
	【声明】：本文内容仅代表个人观点。如需转载请保留此段声明，且在文章页面明显位置给出原文链接！

gtid_executed和gtid_purged变量是如何初始化的

一、官方释义

1.1、gtid_executed、gtid_purged

1.2、binlog_gtid_simple_recovery

1.3、mysql.gtid_executed

二、实验

2.1、the oldest and newest file

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

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

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

三、参考文档

公告