Replication and reading from replicas

Key points

• The primary writes a binary log of changes; replicas fetch and apply it. Replication is asynchronous by default, so a replica is always at least slightly behind.
• That means a read from a replica immediately after a write may not see it. A user who saves a profile and is bounced to a replica sees their old data - the read-your-writes problem.
• The usual fix is to route a user reads to the primary for a short window after they write, or to return the updated object from the write itself rather than re-reading it.
• Semi-synchronous replication makes the primary wait for at least one replica to acknowledge receipt. It reduces the data-loss window on failover at the cost of write latency.
• Lag is not linear. A replica applies changes with limited parallelism, so a large batch write on the primary can push it minutes behind while ordinary traffic stays instant.
• Monitor Seconds_Behind_Source, but know its limits: it reports zero when the replica is idle or disconnected, so it must be read alongside the replication thread states.

Example

-- Replica health. Both threads must be Yes.
SHOW REPLICA STATUS\G
--   Replica_IO_Running:  Yes      fetching the binary log
--   Replica_SQL_Running: Yes      applying it
--   Seconds_Behind_Source: 0
--   Last_SQL_Error:
--
--   Seconds_Behind_Source = 0 also happens when disconnected.
--   Always read it together with the two Running flags.

-- GTID position - a more reliable measure than seconds:
SELECT @@GLOBAL.gtid_executed;      -- on primary and on replica; compare

---

// Read-your-writes, in the application. Route recent writers to the primary.
@Service
public class ProfileService {

    @Transactional                       // primary
    public Profile update(String userId, ProfileForm form) {
        Profile saved = repo.save(form.toEntity(userId));
        recentWriters.put(userId, Instant.now());   // remember for ~5 seconds
        return saved;                    // RETURN it - do not re-read
    }

    @Transactional(readOnly = true)      // replica, unless they just wrote
    public Profile get(String userId) {
        if (recentWriters.wroteWithin(userId, Duration.ofSeconds(5))) {
            return primaryRepo.findById(userId).orElseThrow();
        }
        return repo.findById(userId).orElseThrow();
    }
}

/*
 * WHAT IS SAFE TO READ FROM A REPLICA:
 *
 *   reports, analytics, exports          yes - staleness is irrelevant
 *   search and browse listings           usually
 *   another user profile                 usually
 *   the page right after YOUR write      no  - route to primary
 *   a balance a decision depends on      no  - primary, and lock it
 */

Replicas are always behind, so route a user own reads to the primary briefly after they write - or return the written object directly.