Mastering MySQL GTID: From Basics to Advanced Replication and Recovery

What is GTID?

GTID (Global Transaction ID) was introduced in MySQL 5.6 to uniquely identify a transaction across the entire cluster, replacing the traditional binlog‑file‑offset method and simplifying failover and consistency checks.

GTID Syntax

A GTID consists of a source_id (the server UUID) and a transaction_id (an incrementing number starting at 1). e6954592-8dba-11e6-af0e-fa163e1cf111:1 Ranges can be expressed with a hyphen, and multiple GTIDs can be combined into a set separated by commas.

e6954592-8dba-11e6-af0e-fa163e1cf111:1-5:11-18, e6954592-8dba-11e6-af0e-fa163e1cf3f2:1-27

Viewing GTID Variables

gtid_executed – the set of GTIDs that have been executed on the current instance (i.e., recorded in the binlog). Resetting the master clears it.

gtid_purged – the subset of GTIDs that have been removed from the binlog. It can be set only when

gtid_executed</strong> is empty.</li>
<li><strong>gtid_next</strong> – a session variable that determines how the next GTID is generated. Possible values are <code>AUTOMATIC

, ANONYMOUS, or an explicit GTID.

These variables can be inspected with SHOW VARIABLES LIKE 'gtid%'; or via SHOW MASTER STATUS\\G output.

Generating GTID

On a master, gtid_next defaults to AUTOMATIC. Each committed transaction receives the smallest unused GTID from gtid_executed. The generated GTID is written to the binlog as a SET gtid_next='...' event before the actual transaction event.

During slave replay, MySQL first applies the SET gtid_next event, then the transaction, ensuring the same GTID on master and slave.

GTID Persistence in Binlog

Previous_gtids_log_event – appears at the start of each binlog file and records the GTID set that existed before the file.

Gtid_log_event – precedes each transaction and contains the GTID for that transaction.

When MySQL starts, it reads these events to reconstruct gtid_executed and gtid_purged so that the server resumes with the correct GTID state.

Configuring GTID‑Based Replication

Add the following lines to my.cnf on every server that participates in GTID replication:

log_bin = /mysql/binlog/mysql_bin
log_slave_updates = true
gtid_mode = ON
enforce_gtid_consistency = true
relay_log_info_repository = TABLE
relay_log_recovery = ON

On the slave, enable automatic positioning and point to the master:

CHANGE MASTER TO MASTER_HOST='node1', MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_AUTO_POSITION=1;

How GTID Replication Works

When MASTER_AUTO_POSITION=1, the master uses the COM_BINLOG_DUMP_GTID protocol. The slave sends the union of its gtid_executed and the retrieved GTID set (excluding already applied GTIDs). The master then streams only the missing GTID events.

Verify that the slave’s GTID set is a subset of the master’s.

Verify that the master’s purged GTIDs are a subset of the slave’s set.

Locate the first binlog file whose Previous_gtids_log_event is a subset of the slave’s set.

Stream events from that point, skipping any GTIDs already present on the slave.

This process makes the replication position independent of master_log_file and master_log_pos.

Fixing Replication Errors with GTID

In GTID mode the old SQL_SLAVE_SKIP_COUNTER no longer works. To skip a problematic transaction, set gtid_next to that GTID, commit an empty transaction, then reset gtid_next to AUTOMATIC and restart the slave.

SET gtid_next='e10c75be-5c1b-11e6-ab7c-000c296078ae:6';
BEGIN;
COMMIT;
SET gtid_next='AUTOMATIC';
START SLAVE;

To skip a range of GTIDs, set gtid_purged to match the master’s gtid_executed after ensuring data consistency.

GTID and Backup/Restore

When using mysqldump, the dump file contains a SET @@GLOBAL.GTID_PURGED='...'; statement that restores the GTID state.

SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN=0;
... 
SET @@GLOBAL.GTID_PURGED='e10c75be-5c1b-11e6-ab7c-000c296078ae:1-10';
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

Before importing, run RESET MASTER to clear the existing GTID set, then load the dump.

GTID with MHA (MySQL High Availability)

MHA 0.56+ supports GTID‑based failover. The following three conditions must be met for a GTID‑based failover:

All nodes have gtid_mode=ON.

All nodes have a non‑empty Executed_Gtid_Set.

At least one node has MASTER_AUTO_POSITION=1.

Compared with binlog‑position failover, GTID failover eliminates the need to copy binlog files from the old master.

Crash‑Safe Slave Configuration

For binlog‑position replication, set:

relay_log_info_repository = TABLE
relay_log_recovery = ON

For GTID replication, the recommended settings are:

sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
relay_log_recovery = ON

These ensure that the GTID set stored in the binlog matches the InnoDB state after a crash.

Performance Impact of “Double‑1”

Enabling both sync_binlog=1 and innodb_flush_log_at_trx_commit=1 (the “double‑1” configuration) increases transaction latency but keeps data safe. Benchmarks with sysbench show that latency grows sharply when QPS exceeds ~1 000 on a slave configured with double‑1, while a non‑double‑1 slave tolerates higher QPS before latency spikes.

When multi‑threaded slave (MTS) is used, the double‑1 setting is also required to avoid gaps caused by crash recovery.

Additional Tips for MTS, Non‑Double‑1, and Master Crash Safety

If you cannot use double‑1, you can turn off log_slave_updates on MySQL 5.7 so that GTIDs are written to the system table mysql.gtid_executed in real time. For MySQL 5.6, set relay_log_info_repository=TABLE and relay_log_recovery=ON, then manually adjust gtid_purged before restarting the slave.

For master crash safety, also set sync_binlog=1 and innodb_flush_log_at_trx_commit=1. After a crash, verify that the master’s gtid_executed matches the new master’s before re‑establishing replication.

Proper GTID configuration dramatically simplifies failover, backup restoration, and crash recovery, but it requires careful handling of the “double‑1” settings and awareness of performance trade‑offs.