gtid_executed,已经执行的gtid集合(gtid-sets)。
gtid_purged,已经清除的gtid集合。
先解释gtid_purged
首先看测试库上的gtid_purged变量值:
mysql> SHOW GLOBAL VARIABLES LIKE 'gtid_purged';
+---------------+----------------------------------------------+
| Variable_name | Value |
+---------------+----------------------------------------------+
| gtid_purged | e2e2f927-e75c-11e5-ac89-5c260a17ccde:1-13926 |
+---------------+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql>
e2e2f927-e75c-11e5-ac89-5c260a17ccde:1-13926,这个值是怎么计算出来的?
根据文档描述,不管参数binlog_gtid_simple_recovery设置成何值,gtid_purged与gtid_executed都是通过遍历binlog文件来计算的。
而当binlog_gtid_simple_recovery=1时,gtid_purged与gtid_executed时通过读取最老的binlog文件和最新的binlog文件来计算的(一共也就两个文件)。
gtid_purged,是读取最老的binlog文件计算而来。比如,测试库目前最老的binlog文件是:mysql-bin.000050
那么我们看看mysql-bin.000050文件的内容:
D:\Program Files\mysql-5.7.11-winx64\data>mysqlbinlog -v mysql-bin.000050 |more
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160328 8:18:11 server id 2 end_log_pos 123 CRC32 0x28e6f298 Start: binlog v 4, server v 5.7.11-log created 160328 8:18:11
BINLOG '
Q3j4Vg8CAAAAdwAAAHsAAAAAAAQANS43LjExLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AZjy5ig=
'/*!*/;
# at 123
#160328 8:18:11 server id 2 end_log_pos 194 CRC32 0xd012876d Previous-GTIDs
# e2e2f927-e75c-11e5-ac89-5c260a17ccde:1-13926
# at 194
#160328 8:18:13 server id 2 end_log_pos 259 CRC32 0x4e1c4842 GTID last_committed=0 sequence_number=1
SET @@SESSION.GTID_NEXT= 'e2e2f927-e75c-11e5-ac89-5c260a17ccde:13927'/*!*/;
# at 259
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160328 8:18:11 server id 2 end_log_pos 123 CRC32 0x28e6f298 Start: binlog v 4, server v 5.7.11-log created 160328 8:18:11
BINLOG '
Q3j4Vg8CAAAAdwAAAHsAAAAAAAQANS43LjExLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AZjy5ig=
'/*!*/;
# at 123
#160328 8:18:11 server id 2 end_log_pos 194 CRC32 0xd012876d Previous-GTIDs
# e2e2f927-e75c-11e5-ac89-5c260a17ccde:1-13926
# at 194
#160328 8:18:13 server id 2 end_log_pos 259 CRC32 0x4e1c4842 GTID last_committed=0 sequence_number=1
SET @@SESSION.GTID_NEXT= 'e2e2f927-e75c-11e5-ac89-5c260a17ccde:13927'/*!*/;
# at 259
注意红色部分
Previous-GTIDs,这个表示该binlog文件之前(之前已经被purge,清除掉了)的gtids。
该值就是接下来的这行:
# e2e2f927-e75c-11e5-ac89-5c260a17ccde:1-13926
数据库启动时,就会读取这个值,并赋值给gtid_purged。
每个binlog文件的开头都有这个值。
并且,如果发生binlog清除,也就是最老的binlog文件发生变化时,该值就会被重新计算。
gtid_executed呢?
数据库实例已经执行了的gtid集合。
所以该变量在数据库工作期间一直变化。比如:
mysql> SHOW GLOBAL VARIABLES LIKE 'gtid_executed';
+---------------+----------------------------------------------+
| Variable_name | Value |
+---------------+----------------------------------------------+
| gtid_executed | e2e2f927-e75c-11e5-ac89-5c260a17ccde:1-16876 |
+---------------+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| t2 |
+-----------------+
1 row in set (0.00 sec)
mysql> drop table if exists t2;
Query OK, 0 rows affected (0.80 sec)
mysql> SHOW GLOBAL VARIABLES LIKE 'gtid_executed';
+---------------+----------------------------------------------+
| Variable_name | Value |
+---------------+----------------------------------------------+
| gtid_executed | e2e2f927-e75c-11e5-ac89-5c260a17ccde:1-16877 |
+---------------+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql>
+---------------+----------------------------------------------+
| Variable_name | Value |
+---------------+----------------------------------------------+
| gtid_executed | e2e2f927-e75c-11e5-ac89-5c260a17ccde:1-16876 |
+---------------+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| t2 |
+-----------------+
1 row in set (0.00 sec)
mysql> drop table if exists t2;
Query OK, 0 rows affected (0.80 sec)
mysql> SHOW GLOBAL VARIABLES LIKE 'gtid_executed';
+---------------+----------------------------------------------+
| Variable_name | Value |
+---------------+----------------------------------------------+
| gtid_executed | e2e2f927-e75c-11e5-ac89-5c260a17ccde:1-16877 |
+---------------+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql>
可见,我们删除t2表之后,该值从
e2e2f927-e75c-11e5-ac89-5c260a17ccde:1-16876增加到了
e2e2f927-e75c-11e5-ac89-5c260a17ccde:1-16877。
那么这个值在数据库启动时时怎么被赋值的呢?
根据文档描述,在数据库启动时,会读取最新的binlog文件(注意不是启动后切换的那个最新的binlog文件,而是切换之前的那个最新的binlog文件)。
然后描述文件尾部(最后一个),如:
#160331 10:59:51 server id 2 end_log_pos 259 CRC32 0xcc6c5acb
GTID last_committed=0 sequence_number=1
SET @@SESSION.GTID_NEXT= 'e2e2f927-e75c-11e5-ac89-5c260a17ccde:16878'/*!*/;
SET @@SESSION.GTID_NEXT= 'e2e2f927-e75c-11e5-ac89-5c260a17ccde:16878'/*!*/;
此时,将e2e2f927-e75c-11e5-ac89-5c260a17ccde:16878值和gtid_purged值做“UNION”操作,得出gtid_executed值。
比如:gtid_purged=e2e2f927-e75c-11e5-ac89-5c260a17ccde:1-13926
并且GTID=e2e2f927-e75c-11e5-ac89-5c260a17ccde:16878
那么gtid_executed=e2e2f927-e75c-11e5-ac89-5c260a17ccde:1-16878。
如果gtid_purged有其他的uuid,那么此时计算出来得gtid_executed也包含其他的uuid。
而数据库切换到新的binlog文件后,会把gtid_executed值写入新的binlog文件的Previous-GTIDs。
并同时将mysql.gtid_executed表相应更新。
在更新mysql.gtid_executed表的同时,会对该表的gtid进行一次压缩。
比如,更新前是:
SELECT * FROM mysql.`gtid_executed`
source_uuid interval_start interval_end
------------------------------------ -------------- --------------
1cced57b-e75e-11e5-b742-5c260a17ccde 1 26
1cced57b-e75e-11e5-b742-5c260a17ccde 27 27
1cced57b-e75e-11e5-b742-5c260a17ccde 28 29
e2e2f927-e75c-11e5-ac89-5c260a17ccde 1 16085
e2e2f927-e75c-11e5-ac89-5c260a17ccde 16086 16804
e2e2f927-e75c-11e5-ac89-5c260a17ccde 16805 16818
source_uuid interval_start interval_end
------------------------------------ -------------- --------------
1cced57b-e75e-11e5-b742-5c260a17ccde 1 26
1cced57b-e75e-11e5-b742-5c260a17ccde 27 27
1cced57b-e75e-11e5-b742-5c260a17ccde 28 29
e2e2f927-e75c-11e5-ac89-5c260a17ccde 1 16085
e2e2f927-e75c-11e5-ac89-5c260a17ccde 16086 16804
e2e2f927-e75c-11e5-ac89-5c260a17ccde 16805 16818
那么更新后将是:
mysql> flush logs;
Query OK, 0 rows affected (0.88 sec)
mysql> SELECT * FROM mysql.`gtid_executed`;
+--------------------------------------+----------------+--------------+
| source_uuid | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| 1cced57b-e75e-11e5-b742-5c260a17ccde | 1 | 31 |
| e2e2f927-e75c-11e5-ac89-5c260a17ccde | 1 | 16879 |
+--------------------------------------+----------------+--------------+
2 rows in set (0.00 sec)
mysql>
Query OK, 0 rows affected (0.88 sec)
mysql> SELECT * FROM mysql.`gtid_executed`;
+--------------------------------------+----------------+--------------+
| source_uuid | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| 1cced57b-e75e-11e5-b742-5c260a17ccde | 1 | 31 |
| e2e2f927-e75c-11e5-ac89-5c260a17ccde | 1 | 16879 |
+--------------------------------------+----------------+--------------+
2 rows in set (0.00 sec)
mysql>
发生binlog切换时会压缩mysql.`gtid_executed`表,每隔gtid_executed_compression_period次事务之后也会压缩该表。
压缩是通过如下后台线程实现的:
mysql> SELECT * FROM PERFORMANCE_SCHEMA.THREADS WHERE NAME LIKE '%gtid%'\G
*************************** 1. row ***************************
THREAD_ID: 21
NAME: thread/sql/compress_gtid_table
TYPE: FOREGROUND
PROCESSLIST_ID: 139635685943104
PROCESSLIST_USER: NULL
PROCESSLIST_HOST: NULL
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Daemon
PROCESSLIST_TIME: 611
PROCESSLIST_STATE: Suspending
PROCESSLIST_INFO: NULL
PARENT_THREAD_ID: 1
ROLE: NULL
INSTRUMENTED: YES