Documentation Home
MySQL Replication
Related Documentation Download this Excerpt
PDF (US Ltr) - 1.4Mb
PDF (A4) - 1.4Mb
HTML Download (TGZ) - 354.2Kb
HTML Download (Zip) - 368.5Kb

2.3.1 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 (master). 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 master, and replicated transactions, which are reproduced on a slave. When a client transaction is committed on the master, 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 slave, or is filtered out on the slave. 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 master can be applied no more than once on the slave, 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

The source_id identifies the originating server. Normally, the server'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 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 assigned a transaction_id of 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:


This format is used to represent GTIDs in the output of statements such as SHOW SLAVE STATUS as well as in the binary log. They can also be seen when viewing the log file with mysqlbinlog --base64-output=DECODE-ROWS or in the output from SHOW BINLOG EVENTS.

As written in the output of statements such as SHOW MASTER STATUS or SHOW SLAVE STATUS, a sequence of GTIDs originating from the same server may be collapsed into a single expression, as shown here.


The example just shown represents the first through fifth transactions originating on the MySQL Server whose server_uuid is 3E11FA47-71CA-11E1-9E33-C80AA9429562.

This format is also used to supply the argument required by the START SLAVE options SQL_BEFORE_GTIDS and SQL_AFTER_GTIDS.


A GTID set is a set of global transaction identifiers which is represented as shown here:

    uuid_set [, uuid_set] ...
    | ''





    (n >= 1)

GTID sets are used in the MySQL Server in several ways. For example, the values stored by the gtid_executed and gtid_purged system variables are represented as GTID sets. In addition, the functions GTID_SUBSET() and 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.

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_FILE or 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 2.3.2, “Setting Up Replication Using GTIDs”.

The generation and life cycle of a GTID consist of the following steps:

  1. 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).

  2. 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 Chapter 5, Replication Implementation, for details), the slave reads the GTID and sets the value of its gtid_next system 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_next in a session context.

  3. 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.

  4. Because gtid_next is 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.

mysql.gtid_executed Table

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.

The mysql.gtid_executed table is created (if it does not already exist) when the 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.

The mysql.gtid_executed table enables a slave to use GTIDs when binary logging is disabled on the slave, and it enables retention of the GTID history when the binary logs have been lost.

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 shut down, 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.

The mysql.gtid_executed table is reset by RESET MASTER.

mysql.gtid_executed Table Compression

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:

mysql> 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 mysql.gtid_executed table 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 gtid_executed_compression_period 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 gtid_executed_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 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
          THREAD_ID: 26
               NAME: thread/sql/compress_gtid_table
               TYPE: FOREGROUND
               ROLE: NULL
            HISTORY: YES
       THREAD_OS_ID: 18677

The 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.

User Comments