How to Fix Replication Lag in MySQL: Causes and Solutions

Replication lag in MySQL happens when the slave server falls behind the master in applying changes. To fix it, optimize slow queries on the slave, increase network bandwidth, and tune MySQL replication settings like slave_parallel_workers and innodb_flush_log_at_trx_commit.

Why This Happens

Replication lag occurs when the slave server cannot keep up with the changes made on the master server. This can happen due to slow queries on the slave, network delays, or resource bottlenecks like CPU or disk I/O.

For example, if the slave runs a heavy query that takes a long time, it delays applying other changes, causing lag.

sql

SHOW SLAVE STATUS\G

Output

Seconds_Behind_Master: 120
Last_SQL_Error: NULL
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

The Fix

To fix replication lag, first identify slow queries on the slave and optimize them. You can also enable parallel replication by setting slave_parallel_workers to a value greater than 0 to apply transactions concurrently.

Additionally, adjust innodb_flush_log_at_trx_commit to 2 on the slave to reduce disk I/O, and ensure the network between master and slave is fast and stable.

sql

SET GLOBAL slave_parallel_workers = 4;
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
START SLAVE;

Output

Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Slave started

Prevention

To avoid replication lag in the future, regularly monitor slave performance and query execution times. Use tools like pt-heartbeat to measure lag precisely.

Keep your slave hardware capable of handling the workload, and consider using row-based replication for more efficient data transfer.

Also, avoid running heavy reporting queries on the slave during peak replication times.

Related Errors

Other common replication issues include Slave_IO_Running = No or Slave_SQL_Running = No, which indicate connection or SQL thread problems. Fix these by checking network connectivity and error logs.

Another related error is Duplicate entry during replication, often caused by inconsistent data or manual changes on the slave.

Key Takeaways

Optimize slow queries on the slave to reduce replication lag.

Enable parallel replication with slave_parallel_workers for faster apply.

Tune innodb_flush_log_at_trx_commit to reduce disk I/O on the slave.

Monitor replication lag regularly using tools like pt-heartbeat.

Ensure network and hardware resources are sufficient for replication load.