Troubleshoot MySQL Replication Lag and Broken Replicas

Measure MySQL replication lag, read SHOW REPLICA STATUS, clear SQL thread errors and resync a replica that has fallen too far behind to catch up.

  • Advanced
  • Linux VPS
  • 7 min read

By RunRacks Team · Published

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 RunRacks Linux VPS you can confirm the IP and open a console from the Dashboard.
  • 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:

Terminal
mysql -u root -p

Measure MySQL replication lag

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

SQL
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:

SQL
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:

SQL
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:

SQL
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:
SQL
STOP REPLICA;
SHOW REPLICA STATUS\G
  1. On the primary, take a consistent dump with the binary log position embedded:
Terminal
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:
Terminal
scp /root/primary-dump.sql [email protected]:/root/
  1. Import it on the replica:
Terminal
mysql -u root -p < /root/primary-dump.sql
  1. Point the replica at the primary and start it. With GTID:
SQL
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:

SQL
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:

SQL
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:

SQL
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

If you need a second server to act as the replica, see our Linux VPS plans and open a ticket from /tickets/new if replication still will not start.

Was this guide helpful?

Still stuck?

Ask RunRacks AI for a quick answer, or open a ticket and our team will take a look at your server.

Databases

Reset a Forgotten MySQL Root Password on Linux

Recover access to MySQL or MariaDB on a Linux VPS by starting the server with skip-grant-tables, setting a new root password and restarting normally.

  • Intermediate
  • Linux VPS
  • 5 min read

Databases

MySQL Point-in-Time Recovery Using Binary Logs

Recover a MySQL or MariaDB database to an exact moment after a bad write or dropped table by restoring a full backup and replaying binary logs up to the incident.

  • Advanced
  • Linux VPS
  • 7 min read