Putting GTID-Based Replication to Work in Practice

1. What GTIDs are and how they differ from binlog positions

Classic MySQL replication identifies every transaction by a coordinate pair of binlog filename and byte position, for example mysql-bin.000042 at position 1874392. These coordinates are server specific: a replica knows its own position in the relay log, but not automatically which point in a new source's binlog corresponds to it after a failover. That translation work had to be done by hand, usually under time pressure and with real risk of error.

A Global Transaction Identifier consists of the source server's UUID and a running transaction number, for example 3E11FA47-71CA-11E1-9E33-C80AA9429562:23. This ID is globally unique and stays valid across the entire topology, regardless of how many intermediate nodes a transaction passed through. Every server therefore knows not only its own position but the exact identity of every single transaction it has ever processed.

2. Automatic failover handling through GTIDs

With position-based replication, someone had to manually work out which binlog position on a new source a replica should resume from after a failover, often through a tedious comparison of timestamps and statement content across several binlog files. A single mistake in that mapping either applies transactions twice or silently skips data, both hard-to-detect failure modes that surface much later.

GTIDs remove that manual translation entirely. A replica simply reports its full gtid_executed set to the new source, the source computes the difference against its own set and streams exactly the missing transactions. This mechanism is the foundation that lets modern HA tools such as MySQL Router, Orchestrator, or InnoDB Cluster offer reliable automatic failover at all, without a human resolving positions by hand.

3. Enabling gtid_mode: prerequisites and server variables

GTID replication requires log_bin, a unique server_id, and enforce_gtid_consistency. The latter forbids statements that cannot be cleanly represented as a single GTID, such as mixed updates across transactional and non-transactional engines in one transaction, CREATE TABLE ... SELECT, and temporary tables inside replicated transactions. Before the actual switch, a trial run in WARN mode is worth the effort to surface incompatible statements in production without blocking them outright.

Only once WARN mode reports no violations over a representative period should the strict ON mode be enabled. This order prevents the later GTID activation from being derailed by application code that has been quietly relying on incompatible patterns all along, for example legacy scripts that create a report table as a temporary table inside a replicated transaction.

-- Step 1: surface incompatible statements in production without blocking them
SET PERSIST enforce_gtid_consistency = WARN;

-- After a sufficient observation period with no warnings in the error log:
SET PERSIST enforce_gtid_consistency = ON;

-- Check current mode
SELECT @@GLOBAL.enforce_gtid_consistency, @@GLOBAL.gtid_mode;

4. Migrating from position-based to GTID-based replication without data loss

MySQL ships an official four-stage transition for exactly this case: OFF, OFF_PERMISSIVE, ON_PERMISSIVE, ON. In both permissive stages the server accepts transactions with and without a GTID at the same time, so source and replicas can be switched one after another without replication ever breaking in between. The order is mandatory: all replicas move to OFF_PERMISSIVE first, then the source, then all replicas move to ON_PERMISSIVE, then the source again, and only at the very end does everything move to the final ON.

Between each stage you have to wait until no more anonymous, meaning non-GTID, transactions are still circulating in the system. That status can be checked with the ongoing_anonymous_transaction_count variable, which must drop to zero before the next step is safe. Skip that check and you risk losing individual non-GTID transactions in the permissive window once the strict mode kicks in.

# Run on every replica, then on the source, in this exact order
mysql -e "SET PERSIST gtid_mode = OFF_PERMISSIVE;"
# ... wait until ongoing_anonymous_transaction_count = 0 on all nodes
mysql -e "SET PERSIST gtid_mode = ON_PERMISSIVE;"
# ... wait again, then per node:
mysql -e "SET PERSIST gtid_mode = ON;"
mysql -e "SELECT @@GLOBAL.gtid_mode, @@GLOBAL.ongoing_anonymous_transaction_count;"

5. Reading GTID sets: gtid_executed and gtid_purged

The gtid_executed variable holds every transaction a server has ever committed, as a compact set of UUID ranges, for example 3E11FA47-...9562:1-874. gtid_purged is the subset of that whose binlog entries have already been physically removed through rotation or PURGE BINARY LOGS, but still need to be tracked so a new replica does not try to request transactions that no longer exist anywhere.

For extracting individual transaction ranges from a binlog archive, mysqlbinlog with the --include-gtids option is the tool of choice, for example to replay only one application's statements after a faulty deployment. That kind of targeted extraction is practically impossible with plain positions, because positions carry no logical mapping back to a specific transaction source.

SELECT @@GLOBAL.gtid_executed;
SELECT @@GLOBAL.gtid_purged;

-- Targeted extraction from a binlog archive via mysqlbinlog:
-- mysqlbinlog --include-gtids='3E11FA47-71CA-11E1-9E33-C80AA9429562:500-874' \
--   mysql-bin.000042 | mysql -u root -p

6. Externally defined GTIDs and detecting errant transactions

An errant transaction happens when a write lands directly on a replica and creates a GTID that never came from the source. As long as that replica only serves reads, the effect often goes unnoticed. It turns critical the moment that exact replica gets promoted to be the new source: it brings along a GTID no other node knows about, and the topology diverges, frequently surfacing days later through inconsistent data.

The most reliable prevention is strict read_only and super_read_only configuration on every replica, so only the replication thread itself can write. To detect existing errant transactions, compare GTID sets across all nodes with GTID_SUBTRACT: any GTID that shows up on a replica but not in the current source's set is a candidate errant transaction and should be checked before every planned failover.

-- Run on the replica, with source_gtid_executed copied over beforehand:
SELECT GTID_SUBTRACT(@@GLOBAL.gtid_executed, 'insert_source_gtid_executed_here')
  AS possible_errant_transactions;

7. CHANGE REPLICATION SOURCE TO with SOURCE_AUTO_POSITION in practice

Once GTIDs are active everywhere, replica configuration shrinks down to host, credentials, and the SOURCE_AUTO_POSITION=1 option, with no log file or byte position at all. The server handles the full reconciliation itself and raises a clear error whenever an errant transaction blocks automatic reconciliation, instead of silently resuming at the wrong point the way older setups could.

This behavior makes failover runbooks noticeably shorter and less error prone: instead of a multi-step procedure for comparing binlog files, a single, always identical command per replica is enough, regardless of which node currently acts as the source.

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = 'db-primary-new.internal',
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = 'secret',
  SOURCE_AUTO_POSITION = 1;

START REPLICA;
SHOW REPLICA STATUS\G

8. Monitoring: SHOW REPLICA STATUS and the performance schema view of GTIDs

SHOW REPLICA STATUS exposes two key fields, Retrieved_Gtid_Set and Executed_Gtid_Set: the first shows what has already landed in the relay log, the second what has actually been applied. The difference between the two sets is exactly the still unprocessed backlog, independent of the often imprecise second-based estimate in Seconds_Behind_Source.

For a trustworthy lag figure, run a GTID_SUBTRACT query between those two sets, which, unlike a pure time estimate, returns the actual number of open transactions. That figure can be wired directly into a monitoring dashboard and stays accurate under multi-threaded replication, because it is transaction-exact rather than a time-based approximation.

SELECT GTID_SUBTRACT(Retrieved_Gtid_Set, Executed_Gtid_Set) AS still_pending
FROM performance_schema.replication_connection_status
JOIN performance_schema.replication_applier_status
  USING (channel_name);

9. Pitfalls in day-to-day GTID operation: multi-source, backups, and restores

In multi-source replication, where one server receives transactions from several independent sources, server UUID uniqueness must hold across every channel, otherwise GTID ranges collide and individual transactions get wrongly skipped as already applied. Cloned instances sharing an identical UUID are a common but avoidable cause here, especially after booting from a snapshot without a preceding UUID reset.

After restoring from a backup, gtid_purged must be set explicitly on the target server, so that later auto-position requests know which transactions were already contained in the backup but no longer exist in the binlog. Both mysqldump --single-transaction --set-gtid-purged=ON and Percona XtraBackup write this information automatically into their output, ignore it during restore and replication later fails with a hard-to-diagnose error.

10. Summary

11. FAQ: GTID Replication: Key Facts at a Glance