# MySQL GTID replication: setup and master failover

> MySQL GTID replication explained: how it differs from binlog coordinates, how to configure it, and how to fail a replica over to a new master.

## 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](https://www.zevshost.net/en/kb/mysql-replication-master-slave/).

## 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](https://www.zevshost.net/en/kb/mysql-binlog-point-in-time/) 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.

---

Source: https://www.zevshost.net/en/kb/mysql-gtid-replikaciya/