Global Transaction Identifier (GTID)

April 11, 2023

Global Transaction Identifier (GTID)

The Global Transaction Identifier (GTID) was introduced in MySQL 5.6. It is a unique identifier associated with each transaction committed on the server of origin (master). The identifier is unique not only to the server from which it originated, but also to all servers in a given replication setup. A one-to-one relationship exists between all transactions and GTIDs. As we will explain further down, MySQL and MariaDB have different GTID implementations.

Replication in MySQL 5.5 and Earlier

In MySQL 5.5, resuming a broken replication setup required the determination of the last binary log file and position, which are distinct on each node if binary logging is enabled. If the MySQL master fails, replication will be broken, and the slave will need to switch to another master. It will be necessary to promote the most updated slave node to be a master, and manually determine the position of the last transaction executed by the slave. Another option is to dump the data from the new master node, restore it on the slave, and then start replication with the new master node. Although these options are feasible, they are not very practical in production settings.

How GTID Solves the Problem

The GTID (Global Transaction Identifier) allows better mapping of transactions across nodes. As of MySQL 5.5 and earlier, replication generates different binlog files for each node. Events in the binlog are the same and are arranged in the same order, but the offsets in the binlog files may differ. In GTID, slaves can see a unique transaction coming in from several masters and this can be easily mapped into the slave execution list if replication needs to be restarted or resumed.

Each transaction is identified by a unique identifier that is the same on every server. Regardless of which binary log position a transaction was recorded in, all you need to know is the GTID: ‘966073f3-b6a4-11e4-af2c-080027880ca6:4’. The GTID is composed of two parts – a unique identifier for the server where the transaction was first executed and a sequence number.

Using the above example, we can see that the transaction was executed by the server with the server_uuid of ‘966073f3-b6a4-11e4-af2c-080027880ca6’, and it is the 4th transaction that has been executed on that server. It is sufficient information to perform complex topology changes – MySQL is aware of which transactions have been executed, therefore it knows which transactions need to be executed next. You no longer need binary logs, everything is now stored in the GTID.

The replication stream contains all the information necessary for synchronizing with the master. If you are using GTIDs for replication, you do not need to include the MASTER_LOG_FILE or MASTER_LOG_POS options in the CHANGE MASTER TO statement; instead, you must enable the MASTER_AUTO_POSITION option.

MariaDB GTID vs MySQL GTID

In MariaDB 10.0.2, Global Transaction ID (GTID) is enabled by default. MariaDB GTIDs consist of three separate values:

Domain ID – Replication domain. There is a replication domain, which is a server or a group of servers that generate a single, strictly ordered replication stream.
Server ID – Master and slave servers will be able to identify themselves uniquely using the server identifier number.
Event Group ID – Sequence number for a group of events that are always applied together. A GTID is assigned to each binlog event group (e.g. transaction, DDL, non-transactional statement).
Identifying the server or domain from which the event group originates is easy.
On slaves, binary logging is not necessarily required.
Multi-source replication with distinct domain IDs is supported.
It is not necessary to restart the MariaDB server in order to enable GTID features.
Slave state is recorded in a crash-safe manner.

Despite the differences between these two databases, it is still possible to replicate from MySQL 5.6 to MariaDB 10.0 or vice versa. As a result, you will not be able to use GTID features to automatically select the correct binlog position when switching to a new master. The old-style MySQL replication will work.

Multi-Threaded Slave

If the data is split across several databases, MySQL 5.6 allows you to execute replicated events in parallel. Multi-Threaded Slave (MTS) is a feature that can be enabled by setting slave_parallel_workers to a value greater than 1. The new version of MySQL 5.7 allows it to be used for all workloads, including intra-schema, in contrast to version 5.6 where it could only be used with one thread per schema. As a result of the introduction of write-sets in MySQL 8.0, binary log events are now able to be applied more parallelly.

Crash-Safe Slave

If a slave mysqld/OS crashes, you can recover the slave and continue replication without restoring MySQL databases. Crash-safe slaves require the InnoDB storage engine, and in 5.6 you must set relay_log_info_repository=TABLE and relay_log_recovery=1.

There is no requirement for durability (sync_binlog = 1 and innodb_flush_log_at_trx_commit = 1).

Group Commit

As with any other ACID-compliant database engine, InnoDB flushes its redo log before committing a transaction. InnoDB utilizes group commit functionality in order to avoid having to perform a flush for each commit. With group commits, InnoDB issues a single write to the log file for multiple user transactions that commit simultaneously, resulting in a significant increase in throughput.