Simplify your replication setup and automate failover with transaction-based replication
Global Transaction Identifier (GTID) replication revolutionizes MySQL replication by introducing a unique identifier for every transaction committed on any server in your replication topology. Unlike traditional replication that depends on specific binary log files and positions, GTID replication uses these identifiers to automatically track and synchronize data across servers.
Each GTID consists of two components: the originating server's UUID and a transaction sequence number, formatted as UUID:transaction_id. For example, 3E11FA47-71CA-11E1-9E33-C80AA9429562:23 represents the 23rd transaction executed on the server with that specific UUID.
With traditional replication, promoting a replica to primary requires identifying the correct binary log position—a complex and error-prone process. GTID replication eliminates this complexity because replicas automatically know which transactions they need from the new source using the MASTER_AUTO_POSITION option.
GTID replication ensures that each transaction is applied exactly once on each replica, preventing data inconsistencies that could occur with traditional replication methods.
The MASTER_AUTO_POSITION = 1 feature enables replicas to automatically request missing transactions from the source without administrators needing to manually specify log files or positions.
When using transactional storage engines like InnoDB, GTID positions are stored in system tables and updated within the same transaction as the data changes, making replication crash-safe.
Aspect Traditional Replication GTID Replication
Position Tracking Manual log file/position management Automatic GTID tracking
Failover Process Complex, requires finding positions Simple, automatic positioning
Data Consistency Potential for gaps/errors Guaranteed once-only application
Topology Changes Requires replication restart Dynamic adaptation
MySQL 5.6 or later (recommended: 8.0+)
Unique server-id for each server
Replication user with appropriate privileges
1. Configure the Source Server
Add these settings to your my.cnf file on the source server:
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
gtid_mode = ON
enforce_gtid_consistency = ON
2. Configure the Replica Server
For each replica, use this configuration:
[mysqld]
server-id = 2Â # Must be unique
gtid_mode = ON
enforce_gtid_consistency = ON
# Optional: For additional tracking
log_replica_updates = ON
3. Create Replication User
On the source server, create a dedicated replication user:
CREATE USER 'repl_user'@'replica_ip' IDENTIFIED WITH mysql_native_password BY 'secure_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'replica_ip';
FLUSH PRIVILEGES;
4. Configure Replication on Replica
Set up the replica to use GTID-based auto-positioning:
CHANGE MASTER TO
MASTER_HOST = 'source_ip_address',
MASTER_USER = 'repl_user',
MASTER_PASSWORD = 'secure_password',
MASTER_AUTO_POSITION = 1;
5. Start Replication
START SLAVE;
Check replication status on the replica:
SHOW SLAVE STATUS\G
Key fields to verify:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Retrieved_Gtid_Set: GTIDs fetched from source
Executed_Gtid_Set: GTIDs applied on replica
Auto_Position: 1 (indicates GTID auto-positioning is active)
Problem: When a replica executes a transaction locally that didn't originate from the source, it creates an "errant transaction" with the replica's own UUID. During failover, the new source might try to execute these missing transactions on the old source.
Solution: Use MySQL's GTID functions to identify and resolve discrepancies:
-- Check for errant transactions
SELECT GTID_SUBTRACT('replica_gtid_set', 'source_gtid_set') as errant_transactions;
Problem: The error "Master has purged binary logs containing GTIDs that the slave requires" occurs when the source deletes binary logs that the replica still needs.
Solution: Increase binary log retention time and monitor replica lag:
-- Set binlog retention to 7 days (168 hours)
CALL mysql.rds_set_configuration('binlog retention hours', 168);
MySQL 8.4 introduces Tagged GTIDs, allowing you to assign custom tags (up to 33 characters) to transactions for better organization and selective replication.
Use Case: Skip specific operations on certain replicas:
-- Tag a DELETE operation
SET gtid_next = 'automatic:archive_tag';
DELETE FROM logs WHERE created_at < NOW() - INTERVAL 1 YEAR;
-- On replica, skip transactions with this tag
SET GLOBAL gtid_purged = 'uuid:archive_tag:1-999999999';
Use Row-Based Replication: While GTIDs work with both statement and row-based replication, MySQL recommends row-based format for best results with GTIDs.
Monitor Replication Lag: Keep an eye on replica lag to prevent binary log purge issues.
Regular Backups: Maintain regular backups of your GTID-enabled servers.
Test Failover Procedures: Regularly test your failover procedures to ensure smooth operation during emergencies.
Avoid Local Writes on Replicas: Prevent errant transactions by ensuring applications don't write directly to replica servers.