MySQL GTID Replication: Complete Setup and Failover Guide

It was 2 AM when the on-call DBA got paged: the primary MySQL server had crashed, and the e-commerce platform was down. The replica was running fine, but promoting it meant hunting down the exact binary log file and position where replication had stopped — and with the primary gone, there was no clean way to verify. Three hours later, after manually inspecting relay logs, cross-referencing binlog events, and carefully constructing a CHANGE MASTER TO command with the right MASTER_LOG_FILE and MASTER_LOG_POS, the replica was finally promoted. With GTID replication enabled, that entire recovery would have been a single command and five minutes of work.

What Are GTIDs?

A Global Transaction Identifier (GTID) is a unique identifier automatically assigned to every committed transaction on a MySQL server. The format is straightforward:

source_uuid:transaction_id

-- Example:
3E11FA47-71CA-11E1-9E33-C80AA9429562:1-23

The source_uuid is the server_uuid of the MySQL instance where the transaction originated. The transaction_id is a monotonically increasing integer starting at 1. A GTID set — a collection of GTIDs — represents the complete history of transactions a server has executed or received:

-- View the current GTID set executed on this server
SELECT @@global.gtid_executed;

-- Output example:
-- 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-523,
-- 7B2A19DC-21CA-11E1-AB33-C80AA9429562:1-12

Every server maintains two key GTID sets: gtid_executed (all transactions applied locally) and gtid_purged (GTIDs that have been purged from the binary log). When a replica connects to a primary, it sends its own gtid_executed set, and the primary automatically determines which transactions to send — no file names or byte offsets involved.

Why GTIDs Over Traditional Binlog Replication?

Traditional MySQL replication requires you to track two pieces of state: the binary log filename (e.g., mysql-bin.000047) and the byte position within that file. This creates several operational problems that GTIDs solve cleanly.

No binlog file and position tracking. With classic replication, after a failover or clone, a DBA must identify the exact binlog coordinates on the new primary where the replica should start reading. Getting this wrong by even one byte corrupts data silently. GTIDs replace this entirely — the replica tells the primary what it has applied, and the primary figures out the rest.

Automatic relay log handling. GTIDs are written into the relay log alongside transaction data. If a replica crashes mid-relay-log, MySQL can resume from the correct point automatically. There is no need for relay-log-recovery guesswork.

Simplified failover. Promoting a replica and pointing other replicas to it becomes trivial:

-- On each remaining replica, point to the new primary
CHANGE MASTER TO
  MASTER_HOST = 'new-primary.example.com',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = 'replpassword',
  MASTER_AUTO_POSITION = 1;

START SLAVE;

MySQL uses the GTID sets to detect exactly which transactions each replica is missing and replays only those. No binlog coordinates, no manual cross-referencing.

Enabling GTID Replication

my.cnf Configuration

The three mandatory settings for GTID replication are:

# /etc/mysql/my.cnf or /etc/my.cnf

[mysqld]
# Enable binary logging (required for replication)
log_bin                    = /var/log/mysql/mysql-bin.log
server_id                  = 1          # Must be unique across all servers

# GTID settings
gtid_mode                  = ON
enforce_gtid_consistency   = ON

# Recommended additional settings
binlog_format              = ROW
log_slave_updates          = ON         # Required for replica chains

The enforce_gtid_consistency=ON setting is critical — it prevents statements that are inherently unsafe for GTID replication (such as CREATE TABLE ... SELECT on MySQL versions before 8.0.21) from executing at all, rather than silently corrupting your GTID stream.

Online GTID Migration

In MySQL 5.7 and 8.0, you can migrate from traditional binlog replication to GTID without restarting the server or taking downtime. The key is that gtid_mode accepts four states that must be traversed in order:

-- Step 1: Enable enforce_gtid_consistency first (allows online change)
SET @@GLOBAL.enforce_gtid_consistency = WARN;
-- Monitor error log for any GTID-unsafe statements, fix them first

SET @@GLOBAL.enforce_gtid_consistency = ON;

-- Step 2: Move gtid_mode through intermediate states one at a time
-- OFF -> OFF_PERMISSIVE: Server accepts GTID and non-GTID transactions
SET @@GLOBAL.gtid_mode = OFF_PERMISSIVE;

-- OFF_PERMISSIVE -> ON_PERMISSIVE: Server writes GTIDs, still accepts non-GTID
SET @@GLOBAL.gtid_mode = ON_PERMISSIVE;

-- Wait until all replicas have processed all non-GTID transactions
-- Check: SHOW SLAVE STATUS\G -> Executed_Gtid_Set should be non-empty

-- ON_PERMISSIVE -> ON: Full GTID mode, non-GTID transactions rejected
SET @@GLOBAL.gtid_mode = ON;

-- Step 3: Persist the settings to my.cnf for server restarts
-- (Online SET only survives until restart)

Setting Up a GTID Replica

Create the Replication User

-- On the primary server
CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'strong_repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

CHANGE MASTER TO with MASTER_AUTO_POSITION

After restoring a logical or physical backup to the replica (e.g., with mysqldump --single-transaction --master-data=2 or Percona XtraBackup), configure replication:

-- On the replica server
CHANGE MASTER TO
  MASTER_HOST     = 'primary.example.com',
  MASTER_PORT     = 3306,
  MASTER_USER     = 'repl',
  MASTER_PASSWORD = 'strong_repl_password',
  MASTER_AUTO_POSITION = 1;   -- This replaces MASTER_LOG_FILE and MASTER_LOG_POS

START SLAVE;

-- MySQL 8.0+ equivalent syntax:
-- CHANGE REPLICATION SOURCE TO
--   SOURCE_HOST = 'primary.example.com',
--   SOURCE_USER = 'repl',
--   SOURCE_PASSWORD = 'strong_repl_password',
--   SOURCE_AUTO_POSITION = 1;
-- START REPLICA;

Verify with SHOW SLAVE STATUS

SHOW SLAVE STATUS\G

-- Key fields to verify:
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes
-- Seconds_Behind_Master: 0
-- Retrieved_Gtid_Set: 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-523
-- Executed_Gtid_Set: 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-523
-- Auto_Position: 1

-- MySQL 8.0+:
-- SHOW REPLICA STATUS\G

When Retrieved_Gtid_Set equals Executed_Gtid_Set, the replica has fully caught up with everything it has received from the primary.

Failover and Promotion with GTIDs

Promoting a Replica to Primary

-- On the replica being promoted to primary

-- 1. Stop replication
STOP SLAVE;          -- MySQL 5.7
-- STOP REPLICA;     -- MySQL 8.0

-- 2. Remove replica configuration (makes it an independent primary)
RESET SLAVE ALL;     -- MySQL 5.7
-- RESET REPLICA ALL; -- MySQL 8.0

-- 3. Verify it has the most recent GTID set
SHOW MASTER STATUS;
-- Check Executed_Gtid_Set to confirm all expected transactions are present

-- 4. Update your application connection strings or load balancer (ProxySQL, HAProxy)
-- to point to the new primary

Pointing Other Replicas to the New Primary

With GTID, redirecting remaining replicas is simple. Each replica declares its gtid_executed set to the new primary, and the primary sends only the transactions the replica is missing:

-- On each remaining replica
STOP SLAVE;

CHANGE MASTER TO
  MASTER_HOST     = 'new-primary.example.com',
  MASTER_USER     = 'repl',
  MASTER_PASSWORD = 'strong_repl_password',
  MASTER_AUTO_POSITION = 1;

START SLAVE;

-- Verify immediately
SHOW SLAVE STATUS\G

Re-Adding the Old Primary as a Replica

Once the old primary is recovered, re-adding it as a replica requires care. If it missed transactions while it was down, GTID handles this automatically. If it has transactions that never replicated (errant transactions), those must be handled before replication can start. The gtid_subtract() function is your diagnostic tool here:

-- Run on the recovered old primary
-- Find GTIDs on this server not present on the new primary
SELECT gtid_subtract(@@global.gtid_executed, '3E11FA47-71CA-11E1-9E33-C80AA9429562:1-545');
-- If non-empty result: errant transactions exist and must be resolved before replication

Monitoring GTID Replication

Effective GTID monitoring goes beyond checking whether Slave_IO_Running is Yes.

-- On the primary: view all transactions executed
SHOW MASTER STATUS;
-- Executed_Gtid_Set shows the complete GTID history

-- On the replica: detailed replication status
SHOW SLAVE STATUS\G
-- Retrieved_Gtid_Set:  transactions received from primary (in relay log)
-- Executed_Gtid_Set:   transactions actually applied to this replica
-- Seconds_Behind_Master: lag in seconds (wall clock, not transaction count)

-- Precise lag analysis using gtid_subtract
-- Transactions received but not yet executed on the replica:
SELECT gtid_subtract(
  @@global.gtid_executed,         -- what primary has done
  (SELECT Executed_Gtid_Set       -- what replica has done
   FROM performance_schema.replication_applier_status_by_coordinator)
) AS missing_gtids;

-- Count of missing transactions (approximate lag in transaction count)
SELECT GTID_SUBTRACT(
  (SELECT @@GLOBAL.gtid_executed FROM primary_server),
  @@GLOBAL.gtid_executed
) AS missing_from_replica;

GTID Restrictions and Gotchas

GTIDs require that every transaction in the binary log be uniquely identifiable and independently replayable. Several common MySQL patterns violate this requirement.

-- WRONG (rejected with GTID enforcement before 8.0.21):
CREATE TABLE new_table SELECT * FROM old_table;

-- CORRECT: separate DDL and DML
CREATE TABLE new_table LIKE old_table;
INSERT INTO new_table SELECT * FROM old_table;

Errant transactions deserve special attention. An errant transaction is a GTID that exists on a replica but not on the primary — typically created by accidentally writing directly to a replica. When you later try to use that replica as a primary or re-add the old primary as a replica, replication breaks because the GTID histories diverge. Detection and resolution:

-- Detect errant GTIDs on a replica (transactions on replica not on primary)
-- Run on the replica, substituting the primary's gtid_executed value:
SELECT gtid_subtract(
  @@global.gtid_executed,
  '3E11FA47-71CA-11E1-9E33-C80AA9429562:1-523'
) AS errant_transactions;

-- If errant GTIDs exist, inject an empty transaction on the primary
-- to "acknowledge" the GTID without applying any change:
SET @@SESSION.GTID_NEXT = '7B2A19DC-21CA-11E1-AB33-DEADBEEF1234:1';
BEGIN; COMMIT;
SET @@SESSION.GTID_NEXT = 'AUTOMATIC';

GTID with Group Replication and InnoDB Cluster

MySQL Group Replication and InnoDB Cluster are built on top of the GTID infrastructure. Group Replication requires GTID mode to be fully enabled and extends the GTID mechanism with group-wide transaction certification — each transaction is certified across all group members before being committed, using the GTID set to detect write conflicts.

InnoDB Cluster (the MySQL Shell-managed abstraction over Group Replication) uses GTIDs for its automatic failover logic in MySQL Router and for cluster topology management. When a primary fails, MySQL Router uses the GTID sets to identify which secondary has the most complete transaction history and promotes it automatically — the same GTID mathematics that makes manual failover simple in classic replication makes automated failover reliable in Group Replication.

-- Verify GTID prerequisites before setting up Group Replication
SHOW VARIABLES LIKE 'gtid_mode';            -- Must be ON
SHOW VARIABLES LIKE 'enforce_gtid_consistency'; -- Must be ON
SHOW VARIABLES LIKE 'binlog_format';        -- Must be ROW
SHOW VARIABLES LIKE 'log_slave_updates';    -- Must be ON

-- Check group replication GTID usage
SELECT * FROM performance_schema.replication_group_member_stats\G
-- Columns include: COUNT_TRANSACTIONS_IN_QUEUE, COUNT_TRANSACTIONS_CHECKED,
-- COUNT_CONFLICTS_DETECTED, TRANSACTIONS_COMMITTED_ALL_MEMBERS

For environments running semi-synchronous replication with GTID today and considering Group Replication in the future, enabling full GTID mode is the correct first step — you are already building on the right foundation.

Working with JusDB on MySQL Replication

JusDB sets up and manages MySQL GTID replication for engineering teams that need reliable, automated failover without manual binlog tracking. We handle initial setup, online GTID migration, replica monitoring, and failover runbooks.

Explore JusDB MySQL Services →  |  Talk to a DBA

Related reading:

• MySQL Group Replication
• ProxySQL Load Balancing
• MySQL Architecture Deep Dive