Percona MySQL master-master replication using GTID (Global Transaction Identifiers)

by

Setting up a Percona MySQL master-master replication using GTID (Global Transaction Identifiers) provides high availability, seamless failover, and data consistency across multiple database servers.

This guide walks you through configuring two Percona MySQL servers to replicate data with each other using GTID. Ideal for applications requiring high reliability and performance.

Step-by-Step Configuration

🔨 1. Edit my.cnf on Both Servers

✅ On Server A (/etc/mysql/my.cnf):

[mysqld]
server-id=1
log_bin=mysql-bin
binlog_format=ROW
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
read_only=OFF
auto_increment_increment=2
auto_increment_offset=1
expire_logs_days=28

✅ On Server B (/etc/mysql/my.cnf):

[mysqld]
server-id=2
log_bin=mysql-bin
binlog_format=ROW
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
read_only=OFF
auto_increment_increment=2
auto_increment_offset=2
expire_logs_days=28

After editing the configuration, restart MySQL on both servers:

sudo systemctl restart mysql

👤 2. Create Replication User on Both Servers

Execute the following SQL on both Server A and B:

CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

This creates a user with permission to replicate binary log events.

📦 3. Get GTID Position from Each Server

On Server A, run:

SHOW MASTER STATUS\G

Take note of:

  • File
  • Position
  • Executed_Gtid_Set

Repeat the same on Server B.

🔄 4. Setup Replication on Each Server

Now link each server to replicate from the other.

🚀 On Server A (connect to Server B):

STOP SLAVE;
CHANGE MASTER TO
  MASTER_HOST='192.168.1.12',
  MASTER_USER='repl',
  MASTER_PASSWORD='password',
  MASTER_AUTO_POSITION=1;
START SLAVE;

🚀 On Server B (connect to Server A):

STOP SLAVE;
CHANGE MASTER TO
  MASTER_HOST='192.168.1.11',
  MASTER_USER='repl',
  MASTER_PASSWORD='password',
  MASTER_AUTO_POSITION=1;
START SLAVE;

✅ 5. Verify Replication Status

Run the following on both servers:

SHOW SLAVE STATUS\G

Ensure:

  • Slave_IO_Running: Yes
  • Slave_SQL_Running: Yes
  • No error messages

🔍 Additional Checks

To ensure everything is working and configured properly:

SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'gtid_mode';
SHOW VARIABLES LIKE '%expire_logs%';
SHOW BINARY LOGS;

These confirm binary logging, GTID mode, log expiration, and current logs.