Configuring MySQL GTID-Based Replication: Concepts, Setup, and Testing

GTID (Global Transaction Identifier) is a unique identifier associated with each transaction on the master server; it is stored in the mysql.gtid_executed table and is globally unique across the replication topology.

If a transaction with a given GTID has started but not yet committed or rolled back, any concurrent attempt to start a transaction with the same GTID on another server will be blocked until the first attempt finishes.

Enabling GTID replication requires setting the gtid_mode and enforce_gtid_consistency variables to ON on both master and slave, then restarting MySQL. Example my.cnf configuration:

[mysqld]

# Enable GTID

gtid_mode=ON

enforce_gtid_consistency=ON

After configuration, use SHOW MASTER STATUS to view the current GTID value.

Testing transactions :

INSERT INTO class VALUES (1,'笔记本','北京');

INSERT INTO class VALUES (2,'CPU','天津');

INSERT INTO class VALUES (3,'显示器','上海');

These inserts are displayed with SELECT * FROM class;. Then start a transaction, insert a fourth row, and roll it back to see that the row disappears after ROLLBACK.

After GTID is enabled, the Executed_Gtid_Set variable will contain values only after transactions have been executed.

Creating a replication user on the master:

CREATE USER 'repl'@'192.168.20.%' IDENTIFIED BY '123456';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.20.%';

Initializing the slave by dumping the master and importing it on the slave:

mysqldump -uroot -p123456 -h 192.168.20.40 -P 3306 --single-transaction --master-data=2 --flush-logs --flush-privileges --events --routines --all-databases > /root/master.sql

mysql -uroot -p123456 < /root/master.sql

Adding the slave to the replication environment with CHANGE MASTER TO:

CHANGE MASTER TO

MASTER_HOST='192.168.20.40',

MASTER_PORT=3306,

MASTER_USER='repl',

MASTER_PASSWORD='123456',

MASTER_AUTO_POSITION=1;

Then start the slave with START SLAVE; and verify replication using SHOW SLAVE STATUS \G, which displays fields such as Slave_IO_Running, Slave_SQL_Running, and the master log file/position.

Related reading links are provided for further details on MySQL binlog cleanup, data recovery, and MySQL 8.0 master‑slave replication.