17.1.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 (the 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:

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.

mysql.gtid_executed Table

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:

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.

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:

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.

Note

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:

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.