MySQL Read-Write Separation Masterclass: Replication, Lag Solutions & HA Architecture

What: Read-Write Separation Explained

Previous optimizations (indexing, deep pagination, archiving, sharding) address data volume issues. However, high-concurrency scenarios where read requests vastly outnumber writes require a different approach. Most internet businesses are read-heavy: product queries, order lists, user profiles, homepage traffic. Concentrating all reads and writes on a single master overwhelms CPU, connections, and I/O.

The core architecture: master handles writes (INSERT/UPDATE/DELETE), slaves handle all reads (SELECT) . Master data syncs to slaves via binlog in real time, distributing read load horizontally and protecting write stability.

Why: Root Cause of Database Avalanches

90% of production database collapses follow this pattern: traffic spike → massive queries saturate master → master load spikes, latency rises → write requests block and timeout → full business outage.

Single-master fatal flaws:

Read/write resource contention; heavy reads inevitably drag down writes.

Reads cannot scale horizontally; single-node performance ceiling is fixed.

No backup node; master failure brings down everything with zero disaster recovery.

Read-write separation delivers two core values:

Performance scaling : multiple slaves absorb massive read traffic, QPS scales horizontally without limit.

High availability : master failure triggers fast slave promotion, eliminating single point of failure.

Where: Applicable & Forbidden Scenarios

Must enable read-write separation:

Read-heavy workloads: e-commerce, content platforms, user centers, order lists.

High-concurrency spikes: flash sales, promotions, homepage bursts.

Reporting, analytics, batch queries (extremely resource-intensive).

Core businesses requiring database HA and master-slave disaster recovery.

Do not use:

Write-heavy workloads: payments, transfers, core transaction systems.

Strong consistency requirements with zero tolerance for replication lag.

Low-traffic, low-QPS projects — over-engineering that adds ops overhead.

How: Master-Slave Replication Under the Hood

Three-step asynchronous process:

Master logs writes : all DML operations recorded to binary log (binlog) .

Slave pulls logs : slave I/O thread fetches master binlog continuously, writes to local relay log .

Slave replays logs : slave SQL thread executes relay log events, replaying DML to keep data consistent.

Key takeaway : replication is asynchronous — master returns immediately after write, does not wait for slave sync completion.

One-Master-Multi-Slave Enterprise Standard Architecture

Production standard: one master + two slaves / one master + three slaves .

Master : writes only, core transactions, strong-consistency reads.

Slave 1 : serves regular business read queries.

Slave 2 : handles reporting, statistics, exports, offline analysis — isolates heavy analytical queries from OLTP workload.

Advantage: business reads and analytical reads are completely isolated, preventing reports from dragging down live user queries.

Core Challenge: Replication Lag (Biggest Production Pitfall)

Asynchronous replication guarantees data lag ranging from milliseconds to seconds. The fatal symptom: freshly written data is invisible on slaves .

Lag root causes

Fast master writes, slow slave replay, network jitter, large transactions, DDL statements all amplify lag.

Enterprise-grade solutions

Force master reads : for newly inserted data, real-time data, user-just-operated data — route directly to master to bypass lag.

Lag threshold fallback : monitor replication lag; when threshold exceeded, temporarily route all reads to master.

Semi-synchronous replication : master waits for at least one slave to acknowledge receipt before committing, drastically reducing lag and strengthening consistency.

Master-Slave Automatic Failover HA Architecture

Plain master-slave lacks auto-failover. Production standard: MHA (Master High Availability) .

Core capabilities:

Real-time master health monitoring.

Second-level failure detection, automatic promotion of optimal slave to new master.

Automatic VIP (virtual IP) drift — applications unaware, no config changes needed.

Failed master automatically demoted to slave after recovery.

Application-Layer Read-Write Separation with Sharding-JDBC

No manual routing logic; middleware intercepts and routes automatically.

spring:
  shardingsphere:
    datasource:
      names: master,slave1,slave2
    sharding:
      readwrite-splitting:
        data-sources:
          master-slave:
            write-data-source-name: master
            read-data-source-names: [slave1,slave2]

Routing rule : DML goes to master; SELECT load-balances across slave nodes.

Top 5 Production Pitfalls

Ignoring replication lag → users cannot see their own posts, orders, payments immediately after submission. Highest-frequency production bug.

Blindly routing all reads to slaves — strong-consistency and real-time queries must hit master.

Large transactions / bulk writes cause massive binlog pile-up; slave replay falls behind, data staleness becomes severe.

Sharing business slave for reporting — complex analytical SQL consumes resources, starving live user queries.

No automatic failover — single master + single slave without MHA means master crash = total outage, zero disaster recovery.

Key Takeaways

Read-write separation core: master writes, slaves read; read/write isolation — solves read-heavy high concurrency.

Replication principle: binlog sync, asynchronous, lag is inevitable.

Standard architecture: one master + multiple slaves, isolate business reads from analytical reads.

Lag mitigation: real-time queries to master, semi-sync replication, lag-threshold fallback.

High availability depends on MHA auto-failover to eliminate master single point of failure.