Why You Need MySQL Replication

MySQL replication keeps an exact copy of your database on a second server in real time. It is used for: read load (read replicas), disaster recovery (failover), and backups that put no load on the master.

Master-Slave Architecture

The master writes every change to the Binary Log (binlog). The slave reads the binlog and applies the same changes on its side. Replication is asynchronous — a small delay (lag) is normal.

Configuring the Master Server

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
binlog_expire_logs_seconds = 604800
max_binlog_size = 100M

sudo systemctl restart mysql

# Create a user for replication
mysql -u root -p -e "
CREATE USER 'replicator'@'%' IDENTIFIED BY 'ReplPass123!';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;"

# Get the binlog position (note down File and Position)
mysql -u root -p -e "SHOW MASTER STATUS\G"

Configuring the Slave Server

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
server-id = 2
relay_log = /var/log/mysql/mysql-relay.log
read_only = 1
log_slave_updates = 1

sudo systemctl restart mysql

# Connect to the master
mysql -u root -p -e "
CHANGE MASTER TO
    MASTER_HOST='192.168.1.10',
    MASTER_USER='replicator',
    MASTER_PASSWORD='ReplPass123!',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=154;
START SLAVE;"

Monitoring Replication

mysql -u root -p -e "SHOW SLAVE STATUS\G"

-- Key fields:
-- Slave_IO_Running: Yes  (must be Yes)
-- Slave_SQL_Running: Yes  (must be Yes)
-- Seconds_Behind_Master: 0  (replication lag)

If Slave_IO_Running: No or Slave_SQL_Running: No, replication is broken. Check Last_Error in the SHOW SLAVE STATUS output to diagnose it.

GTID Replication (MySQL 5.6+ recommended)

[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON
log_bin = ON

# On the slave, instead of MASTER_LOG_FILE/POS:
CHANGE MASTER TO
    MASTER_HOST='192.168.1.10',
    MASTER_USER='replicator',
    MASTER_PASSWORD='ReplPass123!',
    MASTER_AUTO_POSITION=1;

GTID replication is preferable to traditional binlog replication: it detects the position automatically, makes failover easier to manage, and removes the need to track File/Position by hand.

Common Replication Errors

Error	Cause	Fix
Last_IO_Error: error connecting to master... Access denied	Wrong replication user password, or it is not connecting from the replica's IP	Check `GRANT REPLICATION SLAVE` and the host the slave connects from
Last_SQL_Error: Duplicate entry '5' for key 'PRIMARY'	The replica already has data that does not match the dump replication started from	Rebuild the replica from a clean master dump with a recorded binlog position
Last_IO_Error: Got fatal error 1236 from master... binary log is purged	The master deleted a binlog file before the replica read it (a small `binlog_expire_logs_seconds`)	Increase binlog retention on the master and rebuild the replica

Why Replication Lag Grows Non-Linearly

Asynchronous MySQL replication is single-threaded on the SQL thread side by default (before MySQL 5.7, multi-threaded replication is optional and requires explicitly configuring slave_parallel_workers). If one heavy UPDATE on the master takes 10 seconds, the same operation on the replica blocks the entire replication thread for 10 seconds — meanwhile the master's other, lighter changes simply pile up in the queue. That is why lag sometimes jumps in bursts instead of growing evenly: one long transaction on the master delays applying everything that follows on the replica.

When a Read Replica Is Not Suitable for Reads

An application that reads the same data right after writing it (a typical pattern: create an order and immediately show a confirmation page) should not read that record from a replica — due to replication lag, the data may appear there seconds later, and the user will see an "order not found" error. The rule is simple: read from the master right after your own write, not from the replica; send to the replica only reads of data independent of the current session — reports, analytics, other users' feeds.

Monitoring Lag and Replication Health

# Main status fields (MySQL 5.7 / MariaDB < 10.5)
mysql -u root -p -e "SHOW SLAVE STATUS\G" | grep -E "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master|Last_Error"

# Renamed command in MySQL 8.0.22+
mysql -u root -p -e "SHOW REPLICA STATUS\G" | grep -E "Replica_IO_Running|Replica_SQL_Running|Seconds_Behind_Source"

# Compare a data checksum between master and replica (pt-table-checksum)
pt-table-checksum --host=master_host --databases=mydb

# Check InnoDB state on the replica under replication load
SHOW ENGINE INNODB STATUS\G

Seconds_Behind_Master equal to 0 does not guarantee the data is fully identical — it shows the time gap between the last applied event and the current moment, not an actual data comparison. Real consistency checking needs pt-table-checksum from Percona Toolkit, which compares checksums row by row. A sturdier replication setup that avoids manually tracking File/Position is covered in the article on GTID replication, and recovering to a specific point in time is covered in the article on point-in-time recovery with binlog.