Before GTID, every replication failover came with a manual archaeology project — find the binlog file and position on the new primary that corresponded to where the replica left off on the old one. GTID replaces that with a globally-unique identifier per transaction: every replica knows which transactions it has already applied and asks the primary for “everything I am missing.” This article is the configuration we deploy when a client needs MySQL replication that can be re-pointed in seconds, the rules GTID imposes on workloads, and the operational discipline behind a clean switchover.
How to verify
GTID has its own set of variables, status counters, and views. Confirm the mode, look at the executed set, and check the replica’s connection.
mysql -e "SHOW GLOBAL VARIABLES LIKE 'gtid_mode';"
mysql -e "SHOW GLOBAL VARIABLES LIKE 'enforce_gtid_consistency';"
mysql -e "SHOW GLOBAL VARIABLES LIKE 'binlog_gtid_simple_recovery';"
mysql -e "SELECT @@GLOBAL.gtid_executed;"
mysql -e "SHOW REPLICA STATUS\G" | grep -E '(Auto_Position|Gtid|Replica_)'
gtid_mode=ON is the production setting. Auto_Position=1 on the replica means it asks the primary using GTID; Auto_Position=0 means it is still using file/position and the GTID migration is incomplete.
What’s happening
A GTID is a string of the form server-uuid:transaction-number. The server UUID identifies the server that originally executed the transaction; the number is monotonic within that server. @@gtid_executed on any server is the union of every GTID it has ever applied — typically a compact string like aaa-...:1-1000,bbb-...:1-500.
When a replica connects with SOURCE_AUTO_POSITION=1, it sends its @@gtid_executed to the primary. The primary computes the difference between its own set and the replica’s, and sends the missing transactions. There is no file or position to track — the GTID is the position.
enforce_gtid_consistency=ON is the rules engine. Some SQL constructs do not have a clean GTID model: CREATE TABLE ... SELECT produces multiple GTIDs in older versions; CREATE TEMPORARY TABLE inside a transaction is ambiguous; non-transactional and transactional updates in the same transaction cannot get a coherent GTID. With enforcement on, MySQL rejects these constructs. Without it, GTID replication will silently corrupt.
The set of GTIDs that the server has “purged” — committed but no longer present in any local binlog — is @@gtid_purged. When restoring a replica from a backup, you must set gtid_purged to the value captured at backup time (XtraBackup writes it to xtrabackup_binlog_info) before connecting; otherwise the replica thinks it has executed transactions it has not.
The procedure
-
Configure the primary in
my.cnf. GTID requires several variables; setting only some leaves the server ingtid_mode=OFForOFF_PERMISSIVEand the migration incomplete.[mysqld] server_id = 1 log_bin = /var/lib/mysql/binlog binlog_format = ROW gtid_mode = ON enforce_gtid_consistency = ON log_slave_updates = ON binlog_expire_logs_seconds = 604800 sync_binlog = 1 innodb_flush_log_at_trx_commit = 1log_slave_updatesis the variable that lets a replica be promoted to primary later — without it, the binlog of the replica does not contain the GTIDs it received from the previous primary. -
Configure each replica with the same
gtid_mode=ON,enforce_gtid_consistency=ON, andlog_slave_updates=ON. Restart both sides to pick up the variables. -
Create the replication user with
REPLICATION SLAVEonly.CREATE USER 'repl'@'10.0.0.%' IDENTIFIED BY 'REPL_PASS' REQUIRE SSL; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.%'; -
Provision the replica from a physical backup, restoring
gtid_purgedbefore starting replication.# backup on primary xtrabackup --backup --target-dir=/backup --user=backup --password=BACKUP_PASS xtrabackup --prepare --target-dir=/backup # ship to replica, copy-back, start MySQL cat /backup/xtrabackup_binlog_info # output: binlog.000123 19216 aaaa-bbbb-...:1-1000000-- on the replica RESET MASTER; SET GLOBAL gtid_purged = 'aaaa-bbbb-...:1-1000000'; CHANGE REPLICATION SOURCE TO SOURCE_HOST = '10.0.0.11', SOURCE_USER = 'repl', SOURCE_PASSWORD = 'REPL_PASS', SOURCE_AUTO_POSITION = 1, SOURCE_SSL = 1; START REPLICA; -
Confirm the replica caught up.
SHOW REPLICA STATUS\G SELECT @@GLOBAL.gtid_executed; -- compare with primary -
Perform a controlled switchover. The pattern: put the primary into read-only mode, wait for the chosen replica to catch up, point applications at the new primary, point the old primary at the new one as a replica.
-- on the old primary SET GLOBAL super_read_only = ON; FLUSH BINARY LOGS;-- on the chosen replica (now becoming primary) STOP REPLICA; RESET REPLICA ALL; SET GLOBAL read_only = OFF; SET GLOBAL super_read_only = OFF;-- on the old primary (now becoming replica) CHANGE REPLICATION SOURCE TO SOURCE_HOST = '10.0.0.12', SOURCE_USER = 'repl', SOURCE_PASSWORD = 'REPL_PASS', SOURCE_AUTO_POSITION = 1, SOURCE_SSL = 1; START REPLICA; SET GLOBAL read_only = ON; SET GLOBAL super_read_only = ON;
Common pitfalls
- Mixing
gtid_mode=ONandgtid_mode=OFFservers in the same replication chain — they cannot replicate. The migration from non-GTID to GTID has documented intermediate states (OFF_PERMISSIVE,ON_PERMISSIVE); skipping them corrupts the binlog stream. - Forgetting
log_slave_updates— a replica without it cannot be promoted; its binlog does not carry the GTIDs of received transactions. SET sql_log_bin=0on a transaction that writes data — bypasses the binlog, so no GTID is assigned. The replica never receives the change and the GTID sets drift.enforce_gtid_consistency=OFF— silent allowance of constructs that break GTID. The drift only shows up at the next failover, when the new primary’s GTID set differs from what replicas have.- Restoring a backup without setting
gtid_purged— the replica thinks it has executed every GTID since the dawn of time, asks the primary for transactions starting from1, gets a lot of replayed inserts, and fails the unique constraint.
GTID-based replication is what makes the difference between a 30-second switchover and a 30-minute outage during a primary failure — every replica knows where it is, and re-pointing is a CHANGE REPLICATION SOURCE away. In the engagements we run, GTID is the default for new MySQL deployments, switchover procedures are rehearsed, and the post-switchover validation confirms @@gtid_executed matches across servers before traffic moves. For the broader pattern of operating MySQL replication topologies, see /en/solutions/clustered-environments/.