Skip to content

MySQL GTID replication — setup, switchover, and the rules that matter

How GTID-based replication works in MySQL, the configuration we deploy on primary and replica, switchover and re-pointing, and the rules that keep GTID safe.

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

  1. Configure the primary in my.cnf. GTID requires several variables; setting only some leaves the server in gtid_mode=OFF or OFF_PERMISSIVE and 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  = 1

    log_slave_updates is 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.

  2. Configure each replica with the same gtid_mode=ON, enforce_gtid_consistency=ON, and log_slave_updates=ON. Restart both sides to pick up the variables.

  3. Create the replication user with REPLICATION SLAVE only.

    CREATE USER 'repl'@'10.0.0.%' IDENTIFIED BY 'REPL_PASS' REQUIRE SSL;
    GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.%';
  4. Provision the replica from a physical backup, restoring gtid_purged before 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;
  5. Confirm the replica caught up.

    SHOW REPLICA STATUS\G
    SELECT @@GLOBAL.gtid_executed;
    -- compare with primary
  6. 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=ON and gtid_mode=OFF servers 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=0 on 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 from 1, 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/.