Mastering MySQL Master‑Slave Read/Write Splitting: 4 Strategies and Real‑World Practices

1. MySQL Master‑Slave Replication Basics

Replication is the foundation of read/write splitting. The master writes changes to the binary log (binlog) after the redo log. Each slave runs two threads:

I/O thread pulls the binlog from the master and stores it in a local relay log .

SQL thread reads the relay log and replays the statements to update the slave’s data.

Key point: Replication is asynchronous by default, so a slave can lag behind the master.

2. Why Read/Write Splitting Is Required

Write traffic is typically a small fraction of total traffic.

Read traffic can be 10–100× the write traffic.

Because the master handles all writes, read pressure quickly becomes a bottleneck. Adding one or more slaves expands read capacity without increasing write load.

3. Four Practical Read‑Distribution Strategies

Strategy 1 – Basic Read/Write Splitting

All writes go to the master; all reads are sent to one or more slaves.

Advantages: Increases read throughput.

Disadvantages: Write‑after‑read inconsistency is visible because a read may hit a lagging slave.

When to use: First scaling step for most workloads.

Strategy 2 – Read/Write Splitting with Automatic Fallback

If a slave is down or its replication lag exceeds a threshold, the request is automatically routed to the master.

Prevents “dead‑slave” situations.

Provides high‑availability reads.

Typical middleware that implements this logic: MyCat, ShardingSphere, ProxySQL, Vitess.

Strategy 3 – Split by Real‑Time Consistency Requirements (Core Strategy)

Classify queries based on how fresh the data must be:

Require real‑time consistency → route to the master.

Can tolerate some delay → route to a slave.

Typical mapping:

Order placement check – ultra‑high real‑time → master.

Historical order list – medium real‑time → slave.

Product detail page – low real‑time → slave.

Strategy 4 – Split by Business Importance Level (Large Platforms)

Route different query categories to different slaves based on their importance and resource profile.

Commonly used in e‑commerce, live‑streaming, content platforms, and financial systems.

4. Main Sources of Replication Lag

Slow execution of the slave SQL thread (primary cause).

Large numbers of slow queries on the slave.

Very large transactions (e.g., updates affecting hundreds of thousands of rows).

Missing or inefficient indexes causing long scans.

Network jitter between data‑centers or availability zones.

Inferior slave hardware (CPU or I/O bottlenecks).

Slow binlog flush on the master.

Slow I/O thread on the slave due to network limits.

Approximately 80 % of master‑slave lag originates from the slave SQL thread not keeping up with the master’s write rate.

5. Solving Write‑After‑Read Inconsistency

Typical bug:

userMapper.insert(user); // write
userMapper.selectById(user.getId()); // immediate read

The slave may not have applied the write yet, so the read returns no result.

Solution 1 – Force Master (Strong Consistency)

Mark the current thread as “master‑only” using ThreadLocal.

Use AOP to intercept “write‑after‑read” patterns and route them to the master.

Apply a custom @Master annotation on methods that require strong consistency.

Best for order processing, payment, login/registration. Not suitable for high‑volume read traffic.

Solution 2 – Latency‑Threshold Detection

Middleware monitors Seconds_Behind_Master. If the value exceeds a configurable threshold (e.g., 1 s), subsequent reads are sent to the master.

Implemented by ProxySQL, MyCat, ShardingSphere, etc.

Solution 3 – GTID‑Based Consistent Read

Each write generates a Global Transaction Identifier (GTID) on the master.

When reading, require the slave to have processed up to that GTID.

If the slave has not caught up, either wait for it or fall back to the master.

Provides true strong consistency; suitable for finance and critical e‑commerce order paths.

Solution 4 – Cache Fallback (Most Common)

Write path writes the new data to Redis immediately. Read path first queries Redis; on a cache miss it falls back to the master or a slave.

Typical cached objects: user profiles, product details, configuration data.

6. Multi‑Slave Allocation Strategies

Enterprises often deploy 2–10 slaves. Choosing which slave serves a given request can improve load balance and latency.

Strategy 1 – Round‑Robin (Default)

Requests are distributed sequentially across slaves. Simple but may cause uneven load if queries have different resource demands.

Strategy 2 – Least Connections

The slave with the fewest active connections receives the next request. Essential for high‑traffic systems; supported natively by ProxySQL.

Strategy 3 – Weighted Allocation

Assign a weight to each slave based on hardware capacity. Example:

Slave A: 16 CPU, SSD → weight 5.

Slave B: 8 CPU, SATA → weight 2.

Strategy 4 – Locality‑Aware

Route users to the slave located in the same geographic region or data‑center (e.g., China users → China slave, US users → US slave) to reduce network latency.

7. Replication Modes and Consistency Guarantees

Asynchronous (default)

The master does not wait for any slave. Provides highest throughput but also the highest possible lag and write‑after‑read inconsistency.

Semi‑Synchronous

The master waits until at least one slave acknowledges receipt of the binlog entry. This reduces the risk of data loss and typically lowers lag compared with pure async.

Strong Synchronous (Group Replication)

All participating nodes must confirm the write before it is considered committed. Offers the strongest consistency at the cost of lower performance; commonly used in banking, trading, and other financial systems.

8. E‑Commerce Decision Matrix for Read Sources

Pre‑order inventory check – ultra‑high real‑time – read from master.

Post‑order order lookup – ultra‑high – master.

Product detail page – low – slave.

Shop product list – low – slave.

User profile page – medium – slave + cache.

Historical order list – medium – slave.

Backend data export – low – analytical slave.

BI reports – low – offline data warehouse.

Core‑chain business (order creation, payment, authentication) should never read from a slave.

9. Recommended Architecture (Golden Combination)

A pragmatic configuration that satisfies most workloads:

# Write path
master writes
# Within a transaction, force master for all reads
# Read path
slave reads with latency‑threshold fallback to master
# High‑consistency reads → master
# Regular reads → slave
# Heavy analytical reads → dedicated analytical slave
# Cache (e.g., Redis) as fallback to reduce master load

90 % of internet‑scale companies adopt a similar pattern: strong‑consistency operations go to the master, non‑critical data use slaves plus cache, and reporting uses an analytical database.