Troubleshoot MySQL Replication Lag and Broken Replicas

MySQL replication lag means the replica is executing transactions that the primary wrote some time ago, and a broken replica is one whose SQL or I/O thread has stopped entirely. This guide shows you how to measure the real lag, read SHOW REPLICA STATUS field by field, clear the common SQL thread errors, and resync a replica from a fresh dump when it cannot catch up.

The commands below work on MySQL 8.0 and 8.4 and on MariaDB 10.x and 11.x. Where the two differ, the difference is called out.

Before you start

You need:

• Two servers: the primary and the replica, both reachable over SSH. On a Linux VPS you can confirm the IP and open a console from your VPS provider's control panel.
• Root or sudo access on both, and the mysql client installed.
• The replication user's name and password on the primary (often repl).
• A maintenance window if you plan to resync, because the replica will be unusable during the restore.

Connect to the server you want to inspect:

mysql -u root -p

Measure MySQL replication lag

Run this on the replica first. It is the single command that answers most questions:

SHOW REPLICA STATUS\G

Read these fields:

• Replica_IO_Running — is the replica pulling the binary log from the primary? Should be Yes.
• Replica_SQL_Running — is the replica applying what it pulled? Should be Yes.
• Seconds_Behind_Source — the lag estimate in seconds.
• Last_IO_Error and Last_SQL_Error — the reason a thread stopped.
• Retrieved_Gtid_Set and Executed_Gtid_Set — with GTID replication, the gap between these two is exactly what is left to apply.
• Relay_Log_Space — how much relay log is still queued.

For per-worker detail on a multi-threaded replica:

SELECT * FROM performance_schema.replication_applier_status_by_worker\G

Fix a stopped SQL thread

A stopped SQL thread is the most common "broken replica". Look at Last_SQL_Error and match it to the cause:

• 1062 Duplicate entry — the row already exists on the replica.
• 1032 Can't find record — the row is missing on the replica.
• 1146 Table... doesn't exist — a schema change never reached the replica.
• 1236 Could not find first log file name — the primary purged binary logs the replica still needs.

If the failing statement is a single transaction you have verified is safe to drop, skip it. Without GTID:

STOP REPLICA SQL_THREAD;
SET GLOBAL SQL_REPLICA_SKIP_COUNTER = 1;
START REPLICA SQL_THREAD;

With GTID, take the transaction ID from Last_SQL_Error and commit an empty transaction with that GTID:

STOP REPLICA SQL_THREAD;
SET GTID_NEXT='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:42';
BEGIN;
COMMIT;
SET GTID_NEXT='AUTOMATIC';
START REPLICA SQL_THREAD;

If the error is 1236, skipping will not help — the binary logs are gone. Go to the resync section.

Resync a replica that fell too far behind

1. Stop replication on the replica and note the current state:

STOP REPLICA;
SHOW REPLICA STATUS\G

1. On the primary, take a consistent dump with the binary log position embedded:

mysqldump --single-transaction --source-data=2 \
  --all-databases --routines --triggers --events \
  -u root -p > /root/primary-dump.sql

1. Copy the dump to the replica:

scp /root/primary-dump.sql deploy@203.0.113.10:/root/

1. Import it on the replica:

mysql -u root -p < /root/primary-dump.sql

1. Point the replica at the primary and start it. With GTID:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='203.0.113.10',
  SOURCE_PORT=3306,
  SOURCE_USER='repl',
  SOURCE_PASSWORD='your-replication-password',
  SOURCE_AUTO_POSITION=1;
START REPLICA;

Without GTID, read SOURCE_LOG_FILE and SOURCE_LOG_POS from the comment block at the top of the dump and pass those instead of SOURCE_AUTO_POSITION.

Reduce lag on a busy primary

Once the replica is running again, stop it from drifting:

• Enable multi-threaded apply. Set replica_parallel_workers (MySQL 8.0.26+; slave_parallel_workers earlier) to 4 or 8, replica_parallel_type=LOGICAL_CLOCK and replica_preserve_commit_order=ON in my.cnf, then restart MySQL. Check the file location with Where is my.cnf?.
• Split bulk DELETE and UPDATE statements into batches. A single statement touching millions of rows blocks the applier for its whole duration.
• Find statements that are slow on the replica itself with the slow query log, as described in Find slow queries in MySQL.
• On a replica with no downstream replicas, sync_binlog=0 and innodb_flush_log_at_trx_commit=2 reduce write pressure.

Check it works

On the replica:

SHOW REPLICA STATUS\G

You want Replica_IO_Running: Yes, Replica_SQL_Running: Yes, Seconds_Behind_Source: 0 and empty Last_IO_Error / Last_SQL_Error. Then prove data actually flows. On the primary:

CREATE DATABASE IF NOT EXISTS repltest;
CREATE TABLE repltest.t (id INT PRIMARY KEY AUTO_INCREMENT, note VARCHAR(50));
INSERT INTO repltest.t (note) VALUES ('lag-check');

On the replica:

SELECT * FROM repltest.t;

The row should appear within a second. Clean up on the primary with DROP DATABASE repltest; and confirm it disappears on the replica too.

Troubleshooting

• 1236 Could not find first log file name in binary log index file — the primary's binary logs were purged before the replica read them. Skipping cannot fix this; resync from a fresh dump and consider raising binlog_expire_logs_seconds on the primary.
• 1062 Duplicate entry or 1032 Can't find record — the replica drifted, usually after a manual write or a skipped transaction. Skip the single transaction if you can prove it is safe, otherwise resync.
• Replica_IO_Running: Connecting and never Yes — the replica cannot reach the primary. Check that port 3306 is open between the two hosts, that the replication user still exists with REPLICATION SLAVE privileges, and read Last_IO_Error for 1045 (bad credentials) or 2003 (connection refused).
• Lag never reaches zero — look for a long-running query on the replica with SHOW PROCESSLIST, a single-threaded applier, or a slow disk. Check Relay_Log_Space to see whether the backlog is growing or shrinking.
• Seconds_Behind_Source is 0 but data is missing — clock skew between the hosts, or no writes happening on the primary. Verify with a heartbeat table rather than trusting the counter.

Next steps

• MySQL Point-in-Time Recovery Using Binary Logs — restore to a moment before a bad transaction reached the replica.
• Automating MySQL backups with mysqldump and cron — keep a dump ready so a resync is quick.
• Tune MySQL and MariaDB memory settings for a small VPS — give the applier threads enough buffer pool to keep up.
• MySQL command-line cheat sheet for server admins — the status and process commands you will reach for most often.