MySQL Replication Troubleshooting: Diagnose and Fix Replica Lag

MySQL replication helps maintain copies of database changes across servers for high availability, reporting, and disaster recovery. However, replication can experience delays or stop when network problems, SQL errors, missing binary logs, or resource bottlenecks occur.

This guide explains how to diagnose common replication issues.

1. Check Replication Status

On a replica, run:

SHOW REPLICA STATUS\G

Review these fields:

• Replica_IO_Running
• Replica_SQL_Running
• Seconds_Behind_Source
• Last_IO_Error
• Last_SQL_Error
• Source_Log_File
• Relay_Source_Log_File
• Exec_Source_Log_Pos

The I/O thread receives events from the source, while the SQL applier processes the received events. Both must operate as expected for normal replication.

Older MySQL versions may use SHOW SLAVE STATUS; use the syntax supported by your installed release.

2. Investigate Replica Lag

Check Seconds_Behind_Source, but do not rely on it as the only indicator of replication health. It may be NULL when replication is stopped, and it does not always capture the full effect of network or workload delays.

Investigate:

• Heavy write activity on the source.
• Slow queries and transactions on the replica.
• Disk I/O latency.
• Network throughput and packet loss.
• Long-running transactions.
• Replication worker errors.

For MySQL versions that support parallel appliers, inspect worker status:

SELECT
    WORKER_ID,
    THREAD_ID,
    SERVICE_STATE,
    LAST_ERROR_NUMBER,
    LAST_ERROR_MESSAGE
FROM performance_schema.replication_applier_status_by_worker;

3. Check Binary Log Availability

If the replica cannot retrieve required binary logs, inspect the source’s binary log configuration and retention policy.

On the source:

SHOW BINARY LOGS;

Check whether the log files needed by the replica still exist and whether the replication account has the required privileges.

If the required logs have already been purged, recovery may require provisioning a fresh replica from a consistent backup or another supported recovery procedure.

4. Investigate Replication Errors

Inspect the error fields in SHOW REPLICA STATUS\G and correlate them with the MySQL error log.

Common causes include:

• Duplicate key errors.
• Missing rows or tables.
• Incompatible schema changes.
• Insufficient replication privileges.
• Source connectivity failures.
• Missing binary logs.
• Storage or filesystem problems.

Do not skip transactions or modify replication positions without first identifying the affected data and assessing consistency requirements.

5. Verify Replication Recovery

After resolving the underlying issue, start replication using the syntax supported by your MySQL release:

START REPLICA;

Then verify:

SHOW REPLICA STATUS\G

Confirm that the replication threads are running, the errors are cleared, and lag is returning to an acceptable level.

For critical systems, validate data consistency and investigate whether any transactions were missed or manually bypassed.

Conclusion

MySQL replication troubleshooting should begin with replication status and error messages, followed by checks of binary logs, network connectivity, SQL execution, and system resources.

Resolve the root cause before restarting replication or changing replication positions. This reduces the risk of data inconsistency and helps restore reliable database replication.

How to Install MySQL 8.4.11 on RHEL 9 / Oracle Linux 9 Using RPM Bundle