Master‑Slave Replication with GTID in MySQL: Principles, Benefits, and Step‑by‑Step Setup

MySQL Master‑Slave Replication Overview

MySQL master‑slave replication works by enabling the binary log on the master, having an I/O thread on the slave read the binlog, store it in the relay log, and then an SQL thread apply the events, achieving data synchronization.

Advantages of MySQL Replication

Horizontal scaling : Reads can be distributed across multiple slaves while writes go to the master, improving read performance.

Data safety : Slave copies can be used for backups without affecting the master.

Analytics : Analytical queries can run on slaves, isolating them from the master’s workload.

GTID Concept

Since MySQL 5.6.5, GTID (Global Transaction ID) assigns a unique identifier to each transaction on the master, improving consistency, failover, and fault tolerance.

GTID consists of UUID:transaction_id, where UUID identifies the MySQL instance and the transaction ID increments monotonically.

GTID sets can include transactions from multiple instances, separated by commas. Ranges from the same instance are separated by colons.

Purpose of GTID

GTID‑based replication replaces the old binlog offset method, allowing the slave to locate and track each transaction globally, simplifying consistency checks and reducing manual errors.

GTID Working Process

When a transaction commits on the master, a GTID is written to the binlog.

The binlog is transferred to the slave, stored in the relay log, and the gtid_next variable is set.

The SQL thread reads the GTID from the relay log and checks whether it already exists in the slave’s binlog.

If the GTID exists, the transaction is skipped; otherwise it is executed and recorded.

During execution, primary keys are used for lookup; if absent, secondary indexes or full scans are performed.

Configuration Steps

Environment

System: CentOS 7
Database: Percona MySQL 5.7
Master: 192.168.11.31
Slave: 192.168.11.32

Master Configuration (my.cnf)

[mysqld]
datadir=/data/mysql/3306
socket=/tmp/mysql.sock
symbolic-links=0
server_id=31
log-bin=master-bin
binlog_format=row
log-slave-updates=1
gtid-mode=on
enforce-gtid-consistency=1
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
sync_binlog=1
innodb_flush_log_at_trx_commit=1

Slave Configuration (my.cnf)

[mysqld]
server_id=32
log-bin=mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=1
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=4
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
#sync_binlog=1
#innodb_flush_log_at_trx_commit=1
log-slave-updates=0
relay_log_recovery=1
read_only=on
super_read_only=on

Grant Replication Privileges

mysql> grant replication slave on *.* to 'slave'@'192.168.11.32' identified by 'slave123';
mysql> flush privileges;

Automatic Sync (Method 1)

Use CHANGE MASTER TO with master_auto_position=1 when the master’s binlog is intact.

CHANGE MASTER TO master_host='192.168.11.31',
master_user='slave',
master_password='slave123',
master_port=3306,
master_auto_position=1;

Backup‑Import Sync (Method 2)

Set GTID_PURGED from the backup’s Xtrabackup_binlog_info file, then run CHANGE MASTER TO.

Backup‑Import Sync (Method 3 – Recommended for Large Data)

Restore a backup on the slave, set master_auto_position=1 so the slave skips GTIDs already present in the backup.

Set Master to Read‑Only (Optional)

mysql> flush tables with read lock;
mysql> set global read_only=on;

Export Master with mysqldump

mysqldump --databases <db_name> --single-transaction --order-by-primary -r <backup_file> --routines -h<host> -P<port> -u<user> -p<password>
mysqldump --default-character-set=utf8mb4 --single-transaction --triggers --routines --events --hex-blob --databases music record > music_record.sql

Record GTID_PURGED

grep -r "GLOBAL.GTID_PURGED" music_record.sql
SET @@GLOBAL.GTID_PURGED='3cdb9ce6-0d7e-11e8-abe4-001517b5a5f0:1-698887';

Import Data to Slave

mysql> create database `music`;
mysql -u root -p music < /root/music.sql
mysql> reset slave all;
mysql> reset master;
SET @@GLOBAL.GTID_PURGED='3cdb9ce6-0d7e-11e8-abe4-001517b5a5f0:1-698887';

Start Replication

mysql> start slave;

Check Slave Status

show slave status\G;

Key fields: Slave_IO_Running, Slave_SQL_Running, and Seconds_Behind_Master should indicate synchronization.

Troubleshooting

stop slave;
reset slave;
start slave;

Check master status with show master status; and verify GTID variables.

Source: https://www.sundayle.com/mysql-gtid-replication/