MySQL Replication Lag: Why Seconds_Behind_Master Can Read Zero and Lie

Your read replica is serving data from twenty minutes ago. A customer swears they placed the order; support cannot find it; you check the replica and it is not there, but it is on the primary. So you open a shell, run the one command everyone runs, and it tells you Seconds_Behind_Source: 0. Zero. No lag. The metric that exists specifically to answer "is my replica behind" is looking you in the eye and lying.

Here is the thing nobody puts on the dashboard tooltip: Seconds_Behind_Source does not measure how far the replica is behind the primary. It measures how far the replica is behind the relay log it has already downloaded. Those are different numbers, and the gap between them is exactly where outages hide. A replica whose I/O thread stopped fetching an hour ago can read 0 all day, because it has applied everything it bothered to download, and it downloaded nothing.

The command everyone runs, and what it actually shows

On MySQL 8.0.22 and later the statement is SHOW REPLICA STATUS (the older SHOW SLAVE STATUS still works as a deprecated alias):

SHOW REPLICA STATUS\G

The three fields people glance at:

        Replica_IO_Running: Yes
       Replica_SQL_Running: Yes
     Seconds_Behind_Source: 0

That looks healthy. It is not necessarily healthy. Each of those lines can hide a different failure, and the last one is the most trusted and the least trustworthy.

Why Seconds_Behind_Source can read zero while you are badly behind

Replication in MySQL has two threads on the replica, and understanding them is the whole game.

The I/O thread connects to the primary and copies its binary log into a local relay log. The SQL thread reads that relay log and applies the events to the replica's tables. Seconds_Behind_Source is computed by the SQL thread: it takes the timestamp of the event it just applied and subtracts it from the replica's current time.

Follow the consequence. If the I/O thread stalls, dies, or loses its connection, it stops adding new events to the relay log. The SQL thread keeps chewing through whatever is left, catches up to the end, and reports 0, because relative to the relay log it is perfectly current. Meanwhile the primary has written another 40 minutes of transactions the replica has never seen. The metric is technically correct and operationally useless, because it is answering a question you did not ask.

There is a second trap. When the SQL thread has drained the relay log and the I/O thread is also idle, MySQL sets Seconds_Behind_Source to 0. When replication threads are stopped or disconnected, it is set to NULL. A dashboard that graphs this value will plot NULL as a gap or, worse, coerce it to 0, so a totally broken replica shows up as the flattest, healthiest line on the board.

How to actually tell if a replica is behind

Step 1: Check the threads before the number

The lag value is meaningless until you know both threads are alive:

SHOW REPLICA STATUS\G

Read these three, in this order:

        Replica_IO_Running: Yes
       Replica_SQL_Running: Yes
     Seconds_Behind_Source: 0
        Last_IO_Error:
       Last_SQL_Error:

If either running field is No or Connecting, replication is stopped or stalling and the lag number does not apply. Read Last_IO_Error and Last_SQL_Error immediately; they tell you why. A duplicate-key or missing-row error will stop the SQL thread; a bad credential or firewall change will stop the I/O thread.

Step 2: Compare the log positions

You can see the download gap without any extra tooling by comparing where the I/O thread has read to where the SQL thread has applied:

   Source_Log_File: mysql-bin.001234
Read_Source_Log_Pos: 8842301        <- I/O thread has downloaded to here
  Relay_Source_Log_File: mysql-bin.001220
   Exec_Source_Log_Pos: 5510044     <- SQL thread has applied to here

If Source_Log_File / Read_Source_Log_Pos on the replica is far behind the primary's current binary-log coordinates (from SHOW MASTER STATUS on the primary, or SHOW BINARY LOG STATUS on MySQL 8.4+), the I/O thread itself is behind, which is precisely the case Seconds_Behind_Source cannot see.

Step 3: Measure real lag with a heartbeat

The only number that survives a stalled I/O thread is a wall-clock heartbeat written on the primary and read on the replica. The idea: a tiny table with one row the primary updates every second, then you read that row's timestamp on the replica and subtract it from the replica's own clock. Percona's pt-heartbeat is the standard implementation of this, and it is worth using because it measures end-to-end lag through both threads and the network, not the internal relay-log gap. A hand-rolled version is the same shape: primary runs UPDATE heartbeat SET ts = NOW(6) WHERE id = 1; on a schedule, and the replica computes NOW(6) - ts. If that number climbs while Seconds_Behind_Source stays at zero, you have caught the lie in the act.

Verification

You know monitoring is fixed when a deliberately broken replica raises an alarm. Stop the I/O thread on a test replica and watch what each signal does:

STOP REPLICA IO_THREAD;
SHOW REPLICA STATUS\G

Expected: within a few seconds Replica_IO_Running reads No, and after the relay log drains Seconds_Behind_Source falls to a low value or NULL, exactly the misleading picture. Your heartbeat lag, meanwhile, should climb by roughly one second per second. If your alerting fires on the heartbeat and on Replica_IO_Running = No, you are covered. If it only watches Seconds_Behind_Source, you just proved it is blind. Restart with START REPLICA IO_THREAD; and confirm the heartbeat lag drops back to near zero.

What people get wrong

"Seconds_Behind_Source is 0, so we are fine." Only if both threads are Yes and the log positions are current. On its own the number tells you the SQL thread has caught up to the relay log, nothing more. Always read it alongside the two running flags.

"It went NULL, so lag dropped to zero." NULL means the SQL thread is not running or the replica is not connected to the primary. It is the single most urgent state in the whole output, and treating it as zero is how a broken replica serves stale reads for hours. Alert on NULL explicitly.

"Just bump the propagation timeout / restart the replica." Restarting can clear a transient stall, but if the cause is a large transaction, a missing index on the replica, or a non-transactional table, it comes straight back. Find the cause in Last_SQL_Error and in the log positions before you reach for a restart.

"Our replica uses MyISAM to go faster." Non-transactional engines like MyISAM apply statements without transactional boundaries and are a classic lag and drift source. If the replica's tables differ from the primary (missing indexes, different engine), the SQL thread does more work per event and falls behind under load. Keep the schema identical, InnoDB on both sides.

When it is still lagging

• Both threads Yes, heartbeat lag high and growing. The SQL thread cannot keep up. Look for a huge single transaction on the primary, a missing index on the replica forcing full scans, or single-threaded apply. Consider multi-threaded replication (replica_parallel_workers) if your workload allows it.
• Lag spikes then recovers. Usually one big write on the primary, a bulk DELETE or a schema change, not steady-state delay. Batch large writes into smaller chunks.
• SQL thread stopped on an error. Read Last_SQL_Error. A row that exists on the replica but not the primary means the two have diverged; do not blindly SET GLOBAL sql_replica_skip_counter, that hides drift. Re-seed the replica if the datasets no longer match.
• I/O thread flapping. Network or auth. If you also see the connection dropping under load, the causes overlap with the four causes of "MySQL server has gone away". And if you graph replica health, feed the heartbeat into the same pipeline you use to correlate logs, metrics and traces so a lag spike lines up with the deploy that caused it.