18.104.22.168 GTID Format and Storage
A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (the source). This identifier is unique not only to the server on which it originated, but is unique across all servers in a given replication topology.
GTID assignment distinguishes between client transactions, which are committed on the source, and replicated transactions, which are reproduced on a replica. When a client transaction is committed on the source, it is assigned a new GTID, provided that the transaction was written to the binary log. Client transactions are guaranteed to have monotonically increasing GTIDs without gaps between the generated numbers. If a client transaction is not written to the binary log (for example, because the transaction was filtered out, or the transaction was read-only), it is not assigned a GTID on the server of origin.
Replicated transactions retain the same GTID that was assigned to the transaction on the server of origin. The GTID is present before the replicated transaction begins to execute, and is persisted even if the replicated transaction is not written to the binary log on the replica, or is filtered out on the replica. The MySQL system table
mysql.gtid_executed is used to preserve the assigned GTIDs of all the transactions applied on a MySQL server, except those that are stored in a currently active binary log file.
The auto-skip function for GTIDs means that a transaction committed on the source can be applied no more than once on the replica, which helps to guarantee consistency. Once a transaction with a given GTID has been committed on a given server, any attempt to execute a subsequent transaction with the same GTID is ignored by that server. No error is raised, and no statement in the transaction is executed.
If a transaction with a given GTID has started to execute on a server, but has not yet committed or rolled back, any attempt to start a concurrent transaction on the server with the same GTID will block. The server neither begins to execute the concurrent transaction nor returns control to the client. Once the first attempt at the transaction commits or rolls back, concurrent sessions that were blocking on the same GTID may proceed. If the first attempt rolled back, one concurrent session proceeds to attempt the transaction, and any other concurrent sessions that were blocking on the same GTID remain blocked. If the first attempt committed, all the concurrent sessions stop being blocked, and auto-skip all the statements of the transaction.
A GTID is represented as a pair of coordinates, separated by a colon character (
:), as shown here:
GTID = source_id:transaction_id
source_id identifies the originating server. Normally, the source's
server_uuid is used for this purpose. The
transaction_id is a sequence number determined by the order in which the transaction was committed on the source. For example, the first transaction to be committed has
1 as its
transaction_id, and the tenth transaction to be committed on the same originating server is assigned a
10. It is not possible for a transaction to have
0 as a sequence number in a GTID. For example, the twenty-third transaction to be committed originally on the server with the UUID
3E11FA47-71CA-11E1-9E33-C80AA9429562 has this GTID:
The GTID for a transaction is shown in the output from mysqlbinlog, and it is used to identify an individual transaction in the Performance Schema replication status tables, for example,
replication_applier_status_by_worker. The value stored by the
gtid_next system variable (
@@GLOBAL.gtid_next) is a single GTID.
A GTID set is a set comprising one or more single GTIDs or ranges of GTIDs. GTID sets are used in a MySQL server in several ways. For example, the values stored by the
gtid_purged system variables are GTID sets. The
START SLAVE clauses
UNTIL SQL_BEFORE_GTIDS and
UNTIL SQL_AFTER_GTIDS can be used to make a replica process transactions only up to the first GTID in a GTID set, or stop after the last GTID in a GTID set. The built-in functions
GTID_SUBTRACT() require GTID sets as input.
A range of GTIDs originating from the same server can be collapsed into a single expression, as shown here:
The above example represents the first through fifth transactions originating on the MySQL server whose
3E11FA47-71CA-11E1-9E33-C80AA9429562. Multiple single GTIDs or ranges of GTIDs originating from the same server can also be included in a single expression, with the GTIDs or ranges separated by colons, as in the following example:
A GTID set can include any combination of single GTIDs and ranges of GTIDs, and it can include GTIDs originating from different servers. This example shows the GTID set stored in the
gtid_executed system variable (
@@GLOBAL.gtid_executed) of a replica that has applied transactions from more than one source:
When GTID sets are returned from server variables, UUIDs are in alphabetical order, and numeric intervals are merged and in ascending order.
The syntax for a GTID set is as follows:
gtid_set: uuid_set [, uuid_set] ... | '' uuid_set: uuid:interval[:interval]... uuid: hhhhhhhh-hhhh-hhhh-hhhh-hhhhhhhhhhhh h: [0-9|A-F] interval: n[-n] (n >= 1)
GTIDs are stored in a table named
gtid_executed, in the
mysql database. A row in this table contains, for each GTID or set of GTIDs that it represents, the UUID of the originating server, and the starting and ending transaction IDs of the set; for a row referencing only a single GTID, these last two values are the same.
mysql.gtid_executed table is created (if it does not already exist) when MySQL Server is installed or upgraded, using a
CREATE TABLE statement similar to that shown here:
CREATE TABLE gtid_executed ( source_uuid CHAR(36) NOT NULL, interval_start BIGINT(20) NOT NULL, interval_end BIGINT(20) NOT NULL, PRIMARY KEY (source_uuid, interval_start) )
As with other MySQL system tables, do not attempt to create or modify this table yourself.
mysql.gtid_executed table is provided for internal use by the MySQL server. It enables a replica to use GTIDs when binary logging is disabled on the replica, and it enables retention of the GTID state when the binary logs have been lost. Note that the
mysql.gtid_executed table is cleared if you issue
GTIDs are stored in the
mysql.gtid_executed table only when
ON_PERMISSIVE. The point at which GTIDs are stored depends on whether binary logging is enabled or disabled:
If binary logging is disabled (
OFF), or if
log_slave_updatesis 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 replica where binary logging or replica update logging is disabled. It does not apply on a replication source server, because on the source, binary logging must be enabled for replication to take place.
If binary logging is enabled (
ON), whenever the binary log is rotated or the server is shut down, the server writes GTIDs for all transactions that were written into the previous binary log into the
mysql.gtid_executedtable. This situation applies on a replication source server, or a replica where binary logging is enabled.
In the event of the server stopping unexpectedly, the set of GTIDs from the current binary log file is not saved in the
mysql.gtid_executedtable. These GTIDs are added to the table from the binary log file during recovery. The exception to this is if binary logging is not enabled when the server is restarted. In this situation, the server cannot access the binary log file to recover the GTIDs, so replication cannot be started.
When binary logging is enabled, the
mysql.gtid_executedtable does not hold a complete record of the GTIDs for all executed transactions. That information is provided by the global value of the
gtid_executedsystem variable. Always use
@@GLOBAL.gtid_executed, which is updated after every commit, to represent the GTID state for the MySQL server, and do not query the
Over the course of time, the
mysql.gtid_executed table can become filled with many rows referring to individual GTIDs that originate on the same server, and whose transaction IDs make up a range, similar to what is shown here:
+--------------------------------------+----------------+--------------+ | source_uuid | interval_start | interval_end | |--------------------------------------+----------------+--------------| | 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 37 | 37 | | 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 38 | 38 | | 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 39 | 39 | | 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 40 | 40 | | 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 41 | 41 | | 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 42 | 42 | | 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 43 | 43 | ...
To save space, the MySQL server compresses the
mysql.gtid_executed table periodically by replacing each such set of rows with a single row that spans the entire interval of transaction identifiers, like this:
+--------------------------------------+----------------+--------------+ | source_uuid | interval_start | interval_end | |--------------------------------------+----------------+--------------| | 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 37 | 43 | ...
You can control the number of transactions that are allowed to elapse before the table is compressed, and thus the compression rate, by setting the
gtid_executed_compression_period system variable. This variable's default value is 1000, meaning that by default, compression of the table is performed after each 1000 transactions. Setting
gtid_executed_compression_period to 0 prevents the compression from being performed at all, and you should be prepared for a potentially large increase in the amount of disk space that may be required by the
gtid_executed table if you do this.
When binary logging is enabled, the value of
gtid_executed_compression_period is not used and the
mysql.gtid_executed table is compressed on each binary log rotation.
Compression of the
mysql.gtid_executed table is performed by a dedicated foreground thread named
thread/sql/compress_gtid_table. This thread is not listed in the output of
SHOW PROCESSLIST, but it can be viewed as a row in the
threads table, as shown here:
mysql> SELECT * FROM performance_schema.threads WHERE NAME LIKE '%gtid%'\G *************************** 1. row *************************** THREAD_ID: 26 NAME: thread/sql/compress_gtid_table TYPE: FOREGROUND PROCESSLIST_ID: 1 PROCESSLIST_USER: NULL PROCESSLIST_HOST: NULL PROCESSLIST_DB: NULL PROCESSLIST_COMMAND: Daemon PROCESSLIST_TIME: 1509 PROCESSLIST_STATE: Suspending PROCESSLIST_INFO: NULL PARENT_THREAD_ID: 1 ROLE: NULL INSTRUMENTED: YES HISTORY: YES CONNECTION_TYPE: NULL THREAD_OS_ID: 18677
thread/sql/compress_gtid_table thread normally sleeps until
gtid_executed_compression_period transactions have been executed, then wakes up to perform compression of the
mysql.gtid_executed table as described previously. It then sleeps until another
gtid_executed_compression_period transactions have taken place, then wakes up to perform the compression again, repeating this loop indefinitely. Setting this value to 0 when binary logging is disabled means that the thread always sleeps and never wakes up.