Mastering MySQL GTID Replication: Benefits, Drawbacks, and Step‑by‑Step Setup

What is GTID?

GTID stands for Global Transaction ID, introduced in MySQL 5.6. Previously replication used binary log positions; with GTID each transaction gets a unique ID, allowing slaves to replicate based on IDs rather than positions.

Advantages and Disadvantages of GTID‑Based Replication

Advantages

Facilitates easier failover because slaves no longer need to sync to a specific binlog offset.

Simplifies master‑slave configuration.

Disadvantages

Introduces SQL restrictions, e.g., CREATE TABLE ... SELECT ... must be split.

Limited to MySQL 5.6+ (better in 5.7); older versions and certain HA solutions like MMM cannot use GTID.

GTID Replication Configuration Overview

Configuration Steps

Prepare two MySQL instances.

Operations on the Master

(1) Ensure server UUIDs differ. Check with: mysql> SHOW VARIABLES like '%server_uuid%'; If identical, edit the auto.cnf file in the datadir to change the server‑uuid value.

(2) Enable GTID in /etc/my.cnf under [mysqld]:

[mysqld]
...
log-bin=mysql-bin
server-id=221
gtid_mode=on
enforce-gtid-consistency=true
...

Restart MySQL and verify: mysql> show global variables like '%gtid%'; Both variables should be ON.

(3) Export initial data, e.g., using mysqldump:

mysqldump --single-transaction --master-data=2 --triggers --routines --all-databases -uroot -p111111 > all.sql

Transfer all.sql to the slave.

(4) Create a replication user on the master:

mysql> GRANT REPLICATION SLAVE ON *.* to 'gtidrepli'@'%' identified by '123456';

Operations on the Slave

(1) Enable GTID in /etc/my.cnf:

server_id=207
log-bin=mysql-bin
read_only=on
gtid_mode=on
enforce-gtid-consistency=true
master_info_repository=TABLE
relay_log_info_repository=TABLE

Restart MySQL.

(2) Import the dumped data: mysql -uroot -p111111 < all.sql (3) Point the slave to the master:

mysql> change master to master_host='192.168.31.221',master_user='gtidrepli',master_password='123456',master_auto_position = 1;

(4) Start the slave and verify status:

mysql> start slave;

mysql> show slave status \G;

Both Slave_IO_Running and Slave_SQL_Running should be Yes, indicating successful replication.