MySQL Replication Lag and Read-After-Write Consistency in Applications

Quick answer: MySQL replication is asynchronous by default, so a replica can lag behind the source and return stale data. Measure lag with a heartbeat rather than trusting a single status value, route only delay-tolerant reads to replicas, and use patterns such as reading from the source after a write or waiting for a replica to reach a specific transaction.

Why Do MySQL Replicas Lag?

The source writes changes to its binary log, replicas fetch them into a relay log, and then apply them. Applying is where lag usually builds up. Large transactions take a long time to execute on the replica, and a single big update or delete can stall replication until it finishes. Heavy write bursts, slow disks on the replica, and long-running DDL statements produce the same effect.

Replica hardware and configuration matter. A replica that is weaker than the source, or that serves heavy read queries while replaying changes, can fall behind under load. Historically, replicas applied changes with a single thread, so a source with many concurrent writers could outrun them. Modern versions support multi-threaded application of changes, whose effectiveness depends on settings and on how parallelizable the workload is.

Network problems and replica restarts add delay as well. After a pause, the replica must catch up, and during that time reads can be seconds or minutes behind. Lag is therefore not a constant, and applications must handle it as normal behavior.

How Should You Measure Lag?

The status output for a replica includes a seconds-behind value, but it has limits. It is computed from timestamps in the applied events, can be zero while the replica is actually disconnected or stalled in some states, and can jump unexpectedly. Command and column names changed across versions, from the older slave terminology to the newer replica terminology, so check your version's documentation.

A more reliable approach is a heartbeat table. A small process on the source updates a row with the current timestamp every second. On the replica, you compare that value with the current time, which gives an end-to-end measurement that includes network, relay, and apply delays. It works regardless of replication topology and exposes stalls that status values can hide.

-- On the source, once per second
UPDATE heartbeat SET ts = NOW(6) WHERE id = 1;

-- On the replica
SELECT TIMESTAMPDIFF(MICROSECOND, ts, NOW(6)) / 1000000 AS lag_seconds
FROM heartbeat WHERE id = 1;

How Do You Keep Reads Consistent?

Start by classifying reads. Reports, search, and catalog pages usually tolerate delay, while reads right after a user's own write do not. If a user saves a profile and the next page reads a lagging replica, the old value appears and the user thinks the save failed. Choose the strategy based on how strict the requirement is:

• Read from the source after writes: send a user's reads to the source for a short window after they write, tracked in the session.
• Wait for a position: capture the transaction identifier after the write and ask the replica to wait until it has applied it, using the server's built-in wait function with a timeout, then fall back to the source if it times out.
• Route critical flows to the source: checkout, balance checks, and inventory decisions should not read from replicas.
• Use semi-synchronous replication carefully: it reduces the risk of losing committed data on failover, but it does not by itself guarantee fresh reads on every replica.

Whichever approach you choose, add a lag threshold to your load balancer or data access layer. If a replica exceeds the limit, stop sending it reads until it recovers. Also remember that failover changes which server is the source, so your routing logic should find the current source dynamically, not through hardcoded addresses.

Frequently Asked Questions

Can I eliminate replication lag completely?

Not with standard asynchronous replication. You can reduce it with better hardware, smaller transactions, and parallel apply, and you can design applications to tolerate what remains.

Why is seconds behind source showing zero while data is missing?

The value can be misleading when replication is stalled or the thread state is unusual. Use a heartbeat table and check the replica thread status.

Do large transactions cause lag?

Yes. A large transaction is applied as a unit, so the replica can be delayed for as long as it takes to execute. Break big changes into smaller batches.

Is it safe to read from replicas for reports?

Generally yes, because reports tolerate delay. Make sure long reports do not overload a replica that also serves latency-sensitive reads.

Summary

Replica lag is normal in asynchronous replication, so measure it end to end, keep large transactions small, and route reads according to their freshness needs. Use post-write routing or position waits for read-after-write consistency. Check the MySQL documentation for replication variables and terminology in your version.