MySQL GTID-based replication is fundamentally about ensuring that your replicas can independently catch up to the master, even if the master’s binary log has been rotated or purged.
Let’s see it in action. Imagine a simple scenario: two MySQL servers, master and replica.
On master:
[mysqld]
server-id = 1
log_bin = mysql-bin
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_format = ROW
On replica:
[mysqld]
server-id = 2
log_bin = mysql-bin
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_format = ROW
After restarting both servers and setting up a replication user on master:
-- On master
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
Now, on replica, we tell it where to connect and what to do to start replicating using GTIDs:
-- On replica
CHANGE MASTER TO
MASTER_HOST='master_ip_address',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;
START SLAVE;
Notice MASTER_AUTO_POSITION=1. This is the magic. Instead of pointing to a specific binary log file and position, it tells the replica to ask the master for its current GTID set and start from there.
When a transaction is committed on master, MySQL assigns it a unique Global Transaction Identifier (GTID). This GTID isn’t just a sequence number; it’s a tuple like aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:1. The first part is a UUID unique to the server that originated the transaction, and the second part is the sequence number within that server’s transaction history.
The replica server, when it connects, asks the master for its current GTID set. Let’s say master has GTIDs aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:1-5. The replica then fetches all transactions with those GTIDs that it hasn’t already applied. If the replica has already processed GTID aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:3, it will only fetch and apply GTIDs 4 and 5. This is how it knows precisely what to replicate, regardless of binary log rotation.
The core problem GTID-based replication solves is the fragility of traditional file-position based replication. With file-position, if the master’s binary logs are purged before the replica has read them, or if a replica fails and its recorded position becomes invalid, you’re often left with a broken replication setup requiring manual intervention, potentially involving re-dumping the entire master and re-setting up the replica. GTIDs make this a thing of the past. Each transaction carries its own identity, and the replica simply needs to know which identities it has already processed.
The gtid_executed and gtid_purged variables on the master are crucial for understanding what’s happening. gtid_executed shows all GTIDs that have been committed on that server. gtid_purged shows GTIDs that have been committed and are still available in the binary log files. When gtid_purged becomes equal to gtid_executed, it means all executed GTIDs can be purged from the binary logs.
The one thing most people don’t realize is how robust GTID replication is to network interruptions and restarts. If the replica disconnects, it doesn’t lose its place. It simply reconnects, asks the master for its current GTID set, and then queries the master for any GTIDs it’s missing since the last GTID it successfully applied. This is tracked internally by the replica’s relay_log_info table, which stores the GTID set that has been applied to the replica’s data.
The next logical step is understanding how to handle failover scenarios with GTID-based replication, particularly using tools like Orchestrator or MHA.