How to Set Up GTID Replication in MySQL: Step-by-Step Guide

To set up GTID replication in MySQL, enable gtid_mode=ON and enforce_gtid_consistency=ON on both master and slave servers. Then configure the master with log_slave_updates=ON and start replication on the slave using CHANGE MASTER TO MASTER_AUTO_POSITION=1.

Syntax

Here is the basic syntax to enable GTID replication in MySQL:

• gtid_mode=ON: Enables GTID-based replication.
• enforce_gtid_consistency=ON: Ensures all transactions are GTID safe.
• log_slave_updates=ON: Makes the slave log replicated events for chaining.
• CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='replica_user', MASTER_PASSWORD='password', MASTER_AUTO_POSITION=1;: Configures the slave to connect to the master and use GTID auto-positioning.
• START SLAVE;: Starts the replication process on the slave.

sql

SET GLOBAL gtid_mode = ON;
SET GLOBAL enforce_gtid_consistency = ON;
SET GLOBAL log_slave_updates = ON;

CHANGE MASTER TO
  MASTER_HOST='master_ip',
  MASTER_USER='replica_user',
  MASTER_PASSWORD='password',
  MASTER_AUTO_POSITION=1;

START SLAVE;

Example

This example shows how to configure a master and a slave for GTID replication. It assumes you have a user replica_user with replication privileges on the master.

sql

-- On Master server (my.cnf or my.ini):
[mysqld]
gtid_mode=ON
enforce_gtid_consistency=ON
log_bin=mysql-bin
server_id=1
log_slave_updates=ON

-- On Slave server (my.cnf or my.ini):
[mysqld]
gtid_mode=ON
enforce_gtid_consistency=ON
server_id=2
log_slave_updates=ON

-- On Slave MySQL client:
CHANGE MASTER TO 
  MASTER_HOST='192.168.1.100',
  MASTER_USER='replica_user',
  MASTER_PASSWORD='password',
  MASTER_AUTO_POSITION=1;

START SLAVE;

-- Check slave status:
SHOW SLAVE STATUS\G;

Output

Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.1.100
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

Common Pitfalls

Common mistakes when setting up GTID replication include:

• Not enabling enforce_gtid_consistency=ON, which causes errors with non-transactional statements.
• Forgetting to set log_slave_updates=ON on the slave, which breaks chained replication.
• Using CHANGE MASTER TO without MASTER_AUTO_POSITION=1, which disables GTID auto-positioning.
• Not setting unique server_id values on master and slave.

Example of wrong and right slave setup:

-- Wrong:
CHANGE MASTER TO MASTER_HOST='master', MASTER_USER='replica_user', MASTER_PASSWORD='password';
START SLAVE;

-- Right:
CHANGE MASTER TO MASTER_HOST='master', MASTER_USER='replica_user', MASTER_PASSWORD='password', MASTER_AUTO_POSITION=1;
START SLAVE;

Quick Reference

Setting	Description	Typical Value
gtid_mode	Enable GTID replication	ON
enforce_gtid_consistency	Ensure GTID-safe transactions	ON
log_bin	Enable binary logging on master	mysql-bin
log_slave_updates	Log events on slave for chaining	ON
server_id	Unique server identifier	1 (master), 2 (slave)
MASTER_AUTO_POSITION	Enable GTID auto-positioning	1

Key Takeaways

Enable gtid_mode and enforce_gtid_consistency on all servers for GTID replication.

Set log_slave_updates=ON on slaves to support chained replication.

Use CHANGE MASTER TO with MASTER_AUTO_POSITION=1 to start GTID replication.

Ensure unique server_id values on master and slave to avoid conflicts.

Check slave status with SHOW SLAVE STATUS\G to confirm replication is running.