Traditional Replication Is a Headache — What Does GTID Actually Solve?
If you’ve ever configured MySQL Master-Slave replication the old way — tracking binlog_file + binlog_pos — you know that gut-wrenching feeling when you need to promote a Slave to become the new Master. You have to pinpoint the exact binlog position. One small mistake and replication drifts out of sync silently, and you won’t notice until it’s too late.
I once dealt with database corruption at 3 AM and spent nearly two hours restoring from backup. After that incident, I migrated everything to GTID — because with GTID, every transaction gets a globally unique identifier. MySQL knows which transactions have already been applied and which haven’t. You don’t need to remember file names and offsets.
GTID (Global Transaction Identifier) follows the format: source_id:transaction_id, for example 3E11FA47-71CA-11E1-9E33-C80AA9429562:23. During failover, the new Slave simply connects to the new Master — MySQL figures out what’s missing and continues syncing automatically, no manual intervention required.
Environment Setup
This guide uses two MySQL 8.0 servers running Ubuntu 22.04:
- Master:
192.168.1.10 - Slave:
192.168.1.11
Install MySQL 8.0 on both servers:
sudo apt update
sudo apt install -y mysql-server-8.0
sudo systemctl enable --now mysqlVerify the version:
mysql --version
# mysql Ver 8.0.36-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu))Configuring GTID Replication Step by Step
Step 1: Configure the Master (192.168.1.10)
Edit /etc/mysql/mysql.conf.d/mysqld.cnf:
[mysqld]
server-id = 1
bind-address = 0.0.0.0
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
# GTID settings
gtid_mode = ON
enforce_gtid_consistency = ON
# Recommended for GTID
log_slave_updates = ON
binlog_expire_logs_seconds = 604800 # keep binlogs for 7 daysRestart MySQL:
sudo systemctl restart mysqlCreate a dedicated replication user — don’t use root, and restrict access to the Slave’s IP only:
CREATE USER 'replicator'@'192.168.1.11' IDENTIFIED WITH mysql_native_password BY 'StrongPass!2024';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'192.168.1.11';
FLUSH PRIVILEGES;Take a snapshot dump from the Master. Use --single-transaction to avoid locking tables — this is critical in production since a full table lock can bring your application to a halt. The binary log retained here also enables point-in-time recovery if something goes wrong during the sync:
mysqldump \
--single-transaction \
--master-data=2 \
--set-gtid-purged=ON \
--all-databases \
-u root -p \
> /tmp/master_dump.sqlCopy the dump to the Slave:
scp /tmp/master_dump.sql [email protected]:/tmp/Step 2: Configure the Slave (192.168.1.11)
Similar to the Master, but add read_only to prevent accidental writes to the Slave — a surprisingly common mistake when developers connect to the wrong server:
[mysqld]
server-id = 2
bind-address = 0.0.0.0
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
# GTID settings
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON
# Slave-specific: block writes to prevent accidental modifications
read_only = ON
super_read_only = ONsudo systemctl restart mysqlImport the dump from the Master:
mysql -u root -p < /tmp/master_dump.sqlConnect the Slave to the Master. Note SOURCE_AUTO_POSITION = 1 — this is the key difference from traditional replication; no file name or offset needed:
CHANGE REPLICATION SOURCE TO
SOURCE_HOST = '192.168.1.10',
SOURCE_PORT = 3306,
SOURCE_USER = 'replicator',
SOURCE_PASSWORD = 'StrongPass!2024',
SOURCE_AUTO_POSITION = 1;
START REPLICA;Verifying Sync and Monitoring
Check Slave Status
SHOW REPLICA STATUS\GThree lines to watch:
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Seconds_Behind_Source: 0If Seconds_Behind_Source keeps climbing instead of reaching 0 — the Slave is lagging. Check CPU and disk load on both servers.
Compare GTIDs between Master and Slave to confirm full synchronization:
-- On Master
SHOW MASTER STATUS\G
-- Executed_Gtid_Set: 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-150
-- On Slave
SELECT @@GLOBAL.gtid_executed;
-- Result must match the MasterQuick Test: Write on Master, Read on Slave
-- On Master
CREATE DATABASE gtid_test;
USE gtid_test;
CREATE TABLE ping (id INT AUTO_INCREMENT PRIMARY KEY, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
INSERT INTO ping VALUES ();
-- On Slave (after a few seconds)
USE gtid_test;
SELECT * FROM ping;
-- The inserted row should appear hereReplication Lag Monitoring Script
Drop this script into crontab and run it every 5 minutes. A 30-second threshold works for most systems — if your SLA is tighter, lower it to 10 seconds:
#!/bin/bash
# /usr/local/bin/check_replica_lag.sh
LAG=$(mysql -u root -p'password' -e "SHOW REPLICA STATUS\G" 2>/dev/null \
| grep Seconds_Behind_Source \
| awk '{print $2}')
if [ "$LAG" = "NULL" ]; then
echo "CRITICAL: Replication stopped!"
# Send alert (email, Telegram, PagerDuty...)
elif [ "$LAG" -gt 30 ]; then
echo "WARNING: Replication lag = ${LAG}s"
fiHandling Failover with GTID
Scenario: the Master dies unexpectedly and you need to promote the Slave to a new Master in minutes rather than the hours it would take with traditional replication.
-- On the newly promoted Slave
STOP REPLICA;
RESET REPLICA ALL;
-- Disable read-only mode
SET GLOBAL read_only = OFF;
SET GLOBAL super_read_only = OFF;Update your application’s connection string to point to 192.168.1.11. Done — this typically takes under 5 minutes if you’ve prepared a runbook in advance.
Want to add a new Slave (192.168.1.12) to the newly promoted Master? The syntax is identical — GTID handles the rest:
CHANGE REPLICATION SOURCE TO
SOURCE_HOST = '192.168.1.11',
SOURCE_USER = 'replicator',
SOURCE_PASSWORD = 'StrongPass!2024',
SOURCE_AUTO_POSITION = 1;
START REPLICA;No need to know the binlog file name or offset. MySQL uses the GTID set to figure out which transactions are missing.
Common Errors When Using GTID
- Error 1236 — Got fatal error from master: The Slave is requesting a binlog that the Master has already purged. Fix: take a fresh dump from the Master, run
RESET MASTERon the Slave before importing, then re-sync. - Error 1418 — This function has none of DETERMINISTIC: Missing the
DETERMINISTICdeclaration when creating a stored function. Add that keyword to your function — see the MySQL Stored Procedures and Functions guide for proper syntax — or temporarily setlog_bin_trust_function_creators = ONif you’re in a hurry. - Slave stops due to duplicate GTID: Often happens when restoring a dump out of order. Run
RESET MASTERon the Slave before importing the dump to avoid this.
Setting up GTID takes an extra 15–20 minutes compared to traditional replication. In return, failover drops from tens of minutes to just a few, and you’ll never have to hunt for binlog positions at 3 AM again.
