On the composition and Storage of GTID-GTID
What is GTID?
GTID (Global Transaction Identifier) is the unique identity that a transaction gets when it commits for the first time, which is unique throughout the replication topology.
GTID composition
GTID = source_id:transaction_id (c4e5d4a0-a9be-11e9-b444-080027f22add:7)
Intuitively, GTID consists of two parts, source_id and transaction_id. Source_id represents the Master instance in which the transaction is committed, which is generally represented by the global system variable server_uuid of the instance; transaction_id represents the order in which transactions are committed on the instance, which is a positive increasing sequence greater than or equal to 1.
GTID collection
A collection is a whole made up of one or more definite elements. The GTID collection, as its name implies, in which the element is GTID, consists of a single, or multiple GTID, or a range of GTID. For example, 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-3:11:47-49. In an example, the global system variables gtid_executed and gtid_purged tend to store the GTID collection.
The GTID collection is also formatted as follows:
Gtid_set:
Uuid_set [, uuid_set]...
|''|
Uuid_set:
Uuid:interval [: interval]...
Uuid:
Hhhhhhhh-hhhh-hhhh-hhhh-hhhhhhhhhhhh
H:
[0-9 | Amurf]
Interval:
N [- n]
(n > = 1)
GTID storage
GTID is stored in the system table mysql.gtid_executed, where a row of records represents a single GTID, or collection of GTID.
+-+
| | source_uuid | interval_start | interval_end | |
+-+
| | aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa | 1 | 72 | |
| | c4e5d4a0-a9be-11e9-b444-080027f22add | 101005 | 188707 | |
+-+
When will GTID be written into the mysql.gtid_executed table? Depending on whether the binary log is turned on
When binary logging is disabled (which usually occurs on Slave), MySQL writes the GTID to the mysql.gtid_ executed table after performing (playback) the GTID (transaction). In version 5.7, the procedure is atomic for DML statements, but not atomic for DDL statements (in version 8.0, both DML and DDL statements are atomic. ).
When the binary log is enabled, when the binary log is rotated, or when the instance is closed, MySQL will write all the GTID in the previous binary log to the mysql.gtid_ executed table. If the MySQL shuts down unexpectedly, during the recovery phase, the unwritten GTID will be written to the mysql.gtid_executed again (of course, the binary log is closed, the unwritten GTID cannot be restored, and the replication cannot continue later).
It is not difficult to see that when binary logging is enabled, the GTID in the mysql.gtid_ execute table does not represent all transactions, and this information is provided by the global system variable @ @ GLOBAL.gtid_executed.
[root@mysql.sock] [(none)] > select * from mysql.gtid_executed
+-+
| | source_uuid | interval_start | interval_end | |
+-+
| | aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa | 1 | 72 | |
| | c4e5d4a0-a9be-11e9-b444-080027f22add | 101005 | 188707 | |
+-+
4 rows in set (0.00 sec)
[root@mysql.sock] [(none)] > select @ @ global.gtid_executed
+
| | @ @ global.gtid_executed |
+
| aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-76:
C4e5d4a0-a9be-11e9-b444-080027f22add:101005-338847 |
+
1 row in set (0.00 sec)
Note that reset master clears the mysql.gtid_executed table.
[root@mysql.sock] [(none)] > select * from mysql.gtid_executed
+-+
| | source_uuid | interval_start | interval_end | |
+-+
| | c4e5d4a0-a9be-11e9-b444-080027f22add | 1 | 188708 | |
+-+
1 row in set (0.00 sec)
[root@mysql.sock] [(none)] > select @ @ global.gtid_executed
+-- +
| | @ @ global.gtid_executed |
+-- +
| | c4e5d4a0-a9be-11e9-b444-080027f22add:1-888712 | |
+-- +
1 row in set (0.00 sec)
[root@mysql.sock] [(none)] > select @ @ global.gtid_purged
+-- +
| | @ @ global.gtid_purged |
+-- +
| | c4e5d4a0-a9be-11e9-b444-080027f22add:1-101004 | |
+-- +
1 row in set (0.00 sec)
[root@mysql.sock] [(none)] > reset master
Query OK, 0 rows affected (0.04 sec)
[root@mysql.sock] [(none)] > select * from mysql.gtid_executed
Empty set (0.00 sec)
[root@mysql.sock] [(none)] > select @ @ global.gtid_executed
+-- +
| | @ @ global.gtid_executed |
+-- +
| | |
+-- +
1 row in set (0.00 sec)
[root@mysql.sock] [(none)] > select @ @ global.gtid_purged
+-+
| | @ @ global.gtid_purged |
+-+
| | |
+-+
1 row in set (0.00 sec)
