A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (master). This identifier is unique not only to the server on which it originated, but is unique across all servers in a given replication setup. There is a 1-to-1 mapping between all transactions and all GTIDs.
The following paragraphs provide a basic description of GTIDs. More advanced concepts are covered later in the following sections:
A GTID is represented as a pair of coordinates, separated by a
colon character (
:), as shown here:
source_id identifies the
originating server. Normally, the server's
server_uuid is used for this
transaction_id is a
sequence number determined by the order in which the transaction
was committed on this server; 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
10. It is not possible for a transaction to
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
This format is used to represent GTIDs in the output of statements
SHOW SLAVE STATUS as well
as in the binary log. They can also be seen when viewing the log
file with mysqlbinlog
in the output from
As written in the output of statements such as
SHOW MASTER STATUS or
SLAVE STATUS, a sequence of GTIDs originating from the
same server may be collapsed into a single expression, as shown
The example just shown represents the first through fifth
transactions originating on the MySQL Server whose
This format is also used to supply the argument required by the
START SLAVE options
A GTID set is a set of global transaction identifiers which is represented as shown here:
uuid_set] ... | ''
GTID sets are used in the MySQL Server in several ways. For
example, the values stored by the
gtid_purged system variables
are represented as GTID sets. In addition, the functions
GTID_SUBTRACT() require GTID sets
as input. When GTID sets are returned from server variables,
UUIDs are in alphabetical order and numeric intervals are merged
and in ascending order.
GTIDs are always preserved between master and slave. This means that you can always determine the source for any transaction applied on any slave by examining its binary log. In addition, once a transaction with a given GTID is committed on a given server, any subsequent transaction having the same GTID is ignored by that server. Thus, a transaction committed on the master can be applied no more than once on the slave, which helps to guarantee consistency.
When GTIDs are in use, the slave has no need for any nonlocal
data, such as the name of a file on the master and a position
within that file. All necessary information for synchronizing
with the master is obtained directly from the replication data
stream. GTIDs replace the file-offset pairs previously required
to determine points for starting, stopping, or resuming the flow
of data between master and slave. therefore, do not include
MASTER_LOG_POS options in the
CHANGE MASTER TO statement used
to direct a slave to replicate from a given master; instead it
is necessary only to enable the
MASTER_AUTO_POSITION option. For the exact
steps needed to configure and start masters and slaves using
GTID-based replication, see
Section 22.214.171.124, “Setting Up Replication Using GTIDs”.
The generation and life cycle of a GTID consist of the following steps:
A transaction is executed and committed on the master.
This transaction is assigned a GTID using the master's UUID and the smallest nonzero transaction sequence number not yet used on this server; the GTID is written to the master's binary log (immediately preceding the transaction itself in the log).
After the binary log data is transmitted to the slave and stored in the slave's relay log (using established mechanisms for this process—see Section 17.2, “Replication Implementation”, for details), the slave reads the GTID and sets the value of its
gtid_nextsystem variable as this GTID. This tells the slave that the next transaction must be logged using this GTID.
It is important to note that the slave sets
gtid_nextin a session context.
The slave verifies that this GTID has not already been used to log a transaction in its own binary log. If this GTID has not been used, the slave then writes the GTID, applies the transaction, and writes the transaction to its binary log. By reading and checking the transaction's GTID first, before processing the transaction itself, the slave guarantees not only that no previous transaction having this GTID has been applied on the slave, but also that no other session has already read this GTID but has not yet committed the associated transaction. In other words, multiple clients are not permitted to apply the same transaction concurrently.
gtid_nextis not empty, the slave does not attempt to generate a GTID for this transaction but instead writes the GTID stored in this variable—that is, the GTID obtained from the master—immediately preceding the transaction in its binary log.
Beginning with MySQL 5.7.5, 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 the MySQL Server is installed or
upgraded, using a
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.
GTIDs are stored in the
table only when
are stored in this table without regard to whether binary
logging is enabled. However, the manner in which they are stored
differs depending on whether
If binary logging is disabled (
OFF), the server stores the GTID belonging to each transaction together with the transaction in the table.
In addition, when binary logging is disabled, this table is compressed periodically at a user-configurable rate; see mysql.gtid_executed Table Compression, for more information.
If binary logging is enabled (
ON), then in addition to storing the GTIDs in
mysql.gtid_executed, 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 new binary log.
In the event of the server stopping unexpectedly, the set of GTIDs from the previous binary log is not saved in the
mysql.gtid_executedtable. In this case, these GTIDs are added to the table and to the set of GTIDs in the
gtid_executedsystem variable during recovery.
mysql.gtid_executed table is reset by
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 sequence,
similar to what is shown here:
SELECT * FROM mysql.gtid_executed;+--------------------------------------+----------------+--------------+ | 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 | ...
Considerable space can be saved if this table is compressed 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 | ...
When GTIDs are enabled, the server performs this type of
compression on the
periodically. 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
system variable. This variable's default value is 1000;
this means that, by default, compression of the table is
performed after each 1000 transactions. Setting
executed_gtid_compression_period to 0
prevents the compression from being performed at all; however,
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
is not used and the
mysql.gtid_executed table is compressed on
each binary log rotation.
Compression of the
is performed by a dedicated foreground thread that is created
whenever GTIDs are enabled on the server. This thread is not
listed in the output of
PROCESSLIST, but it can be viewed as a row in the
threads table, as shown here:
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
This thread has the name
thread/sql/compress_gtid_table, and normally
transactions have been executed, then wakes up to perform
compression of the
as described previously. It then sleeps until another
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.