MySQL Master-Slave Replication Setup: GTID, Monitoring, Errors

MySQL Master-Slave Replication Setup: GTID, Monitoring, Errors

Note: when the MySQL primary server crashes, the business loses not only time but also transactions — recovery from backups can take hours. On one project with a load of 5000 queries per second, the downtime lasted 4 hours, leading to losses. Replication MySQL Master-Slave solves this issue: the replica copies the binary log from the master and applies events with a lag in milliseconds. Professional setup of Master-Slave replication is not just a backup server, but also the ability to distribute read queries, reducing load on the primary. In this article — practical configuration, typical errors, lag monitoring, and what we include in the work. Stable replication with GTID reduces failover time to minutes, and under normal load, the lag does not exceed 1–2 seconds. Professional setup starts at $1,500 for a basic configuration (one master, one replica).

What Does Master-Slave Replication Provide?

• Fault tolerance: when the master fails, we switch to the replica within minutes.
• Read scaling: we send SELECTs to multiple replicas, leaving the master for writes.
• Backups without downtime: we take a dump from the replica without affecting production.

Most projects come to replication after the first failure. We recommend setting it up from the start: it is cheaper and faster than emergency data recovery.

Step-by-Step MySQL Replication Setup

Master Configuration

On the master, enable the binary log and set server_id. Example configuration:

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
binlog_row_image = MINIMAL
sync_binlog = 1
expire_logs_days = 7

Create a user with REPLICATION SLAVE privileges:

CREATE USER 'repl'@'10.0.0.2' IDENTIFIED WITH caching_sha2_password BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.2';
FLUSH PRIVILEGES;

Taking an Initial Dump

To initialize the replica, you need a dump with the binlog position. Two methods:

The binlog position will be at the start of the dump file or in xtrabackup_binlog_info. On a recent project with 500 GB of data, XtraBackup reduced dump time by 3 times compared to mysqldump.

Replica Configuration

On the replica — its own server_id and read_only mode:

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
server_id = 2
relay_log = /var/log/mysql/mysql-relay
read_only = ON
log_bin = /var/log/mysql/mysql-bin
log_slave_updates = ON

Restore the dump and start replication:

mysql -u root -p < /backup/full_dump.sql
mysql -e "CHANGE REPLICATION SOURCE TO SOURCE_HOST='10.0.0.1', SOURCE_USER='repl', SOURCE_PASSWORD='repl_password', SOURCE_LOG_FILE='mysql-bin.000042', SOURCE_LOG_POS=12345, SOURCE_SSL=1; START REPLICA; SHOW REPLICA STATUS\G"

Check: Replica_IO_Running: Yes, Replica_SQL_Running: Yes, Seconds_Behind_Source — lag in seconds.

GTID or Positional Replication?

GTID (Global Transaction ID) — global transaction identifiers available since MySQL 5.6 and above. Their main advantage is that you don't need to manually specify the binlog file and position. During failover, the replica itself determines which transactions to apply. MySQL Documentation: GTID transactions are globally unique. Thanks to GTID, failover time is reduced from 10–15 minutes to 2–3 minutes, which reduces losses in high-load projects. GTID configuration:

gtid_mode = ON enforce_gtid_consistency = ON 

The CHANGE command uses SOURCE_AUTO_POSITION=1 instead of specifying file and position. GTID also simplifies skipping erroneous transactions — instead of SQL_SLAVE_SKIP_COUNTER, use SET GTID_NEXT='...'; BEGIN; COMMIT;.

If your MySQL version is 5.6+ and you plan frequent master switches, choose GTID. For older versions (5.5 and below), only positional replication is available. During the design phase, we help determine the optimal option for your load and SLA.

Replication Lag Monitoring and Management

Lag is the time difference between commit of a transaction on the master and its application on the replica. It is measured in seconds via Seconds_Behind_Source. Under normal load, lag does not exceed 1–2 seconds. If lag increases, check:

• disk performance on the replica;
• transaction size (large DDL can increase lag);
• network latency.

Lag monitoring — via mysqld_exporter metric mysql_slave_status_seconds_behind_master. Alert at lag > 30 seconds and > 60 seconds.

For high lag (>30 seconds), first check replica load: if CPU or I/O are maxed out, increase resources or add parallel workers (slave_parallel_workers). If the problem is network (latency >5 ms), consider a dedicated VLAN or geographic placement of replicas.

To speed up replication, use binlog_format = ROW and binlog_row_image = MINIMAL — this reduces the volume of transmitted data. Also increase slave_parallel_workers to 4-8 if MySQL 8.0+. On a powerful server, this gives a performance boost of up to 50%.

Typical Failure Scenarios