How GTID differs from classic coordinate-based replication

In classic replication, a replica remembers a file and a position inside the master's binlog to know where to keep reading — the pair MASTER_LOG_FILE and MASTER_LOG_POS. After a master change, those coordinates have to be found manually by comparing logs, and an error of a couple of bytes breaks the whole replication chain.

GTID (Global Transaction Identifier) assigns every transaction a unique id in the form server_uuid:number. A replica keeps the set of GTIDs it has already applied and, on failover, works out by itself which transaction to continue from — with no manual search for a file and position.

How to enable GTID on the master and replicas

The mode is turned on with the same settings on every node:

[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON
log_bin = /var/lib/mysql/binlog
server_id = 10

The enforce_gtid_consistency setting forbids constructs that cannot be tied unambiguously to one transaction — for example, mixing temporary and regular tables in a single statement. It must be enabled before switching replication to GTID, otherwise the server will refuse to start with existing problem queries already in the logs.

Configuring a replica with MASTER_AUTO_POSITION

Instead of a file and a position, the replica only points at the master's address:

CHANGE MASTER TO
  MASTER_HOST='10.0.0.10',
  MASTER_USER='repl',
  MASTER_PASSWORD='strong-pass',
  MASTER_AUTO_POSITION=1;

START SLAVE;

The MASTER_AUTO_POSITION=1 flag tells the server to compare the GTID set on the replica against the set on the master and request exactly the missing transactions. The basic steps for setting up a replication user and the channel itself are covered in the article on Master-Slave replication.

Failing over to a new master without losing position

The main advantage of GTID shows up when the master fails. The steps are:

Step	Action
1	Stop writes on the old master or confirm it is unreachable
2	Pick the replica with the most complete GTID set via SHOW SLAVE STATUS
3	Run CHANGE MASTER TO pointing at the new replica-turned-master
4	Start START SLAVE on the remaining replicas
5	Check Seconds_Behind_Master and Executed_Gtid_Set on every node

Because a GTID is globally unique across the whole cluster, replicas will not re-request transactions they already applied and will not lose events between the old and new master, as long as they were pointed at an up-to-date copy.

Diagnosing a gap in the GTID set

The most common problem is an errant transaction — one executed directly on a replica, bypassing the master. It corrupts the GTID set and stops replication with a duplicate error. Comparing the sets helps find it:

SELECT GTID_SUBTRACT(
  @@GLOBAL.gtid_executed,
  'source-uuid:1-500'
) AS extra_transactions;

If the result is not empty, the replica holds transactions the master does not have — they must either be skipped with GTID_NEXT, or the replica must be fully rebuilt from a fresh backup.

Common mistakes when moving to GTID

Before enabling GTID in production, check the following:

Every node runs binlog in ROW format, not STATEMENT.
server_id is unique for every node in the cluster, including standby replicas.
There are no direct writes to replicas bypassing the master that create errant transactions.
The failover procedure has been tested on a staging setup, not only on paper.

Checklist before rolling GTID into production

A final check before switching a working replication setup:

gtid_mode = ON and enforce_gtid_consistency = ON on the master and every replica.
CHANGE MASTER TO uses MASTER_AUTO_POSITION=1 everywhere.
Executed_Gtid_Set has been compared across nodes with no discrepancies.
A fresh full backup exists in case the failover does not go as planned.
Monitoring of Seconds_Behind_Master is set up and alerts on lag.

MySQL GTID replication: setup and master failover