MySQL Read‑Write Splitting: From Replication Basics to Production‑Ready Architecture

Why Read‑Write Splitting?

Read‑write splitting aims to reduce primary read load, increase overall throughput, improve peak‑time stability, support business layering, and prepare for sharding or multi‑region architectures. It is suitable for read‑heavy, write‑light workloads where a single primary can handle writes and replicas can serve reads. It is not a solution for write‑throughput bottlenecks, hot‑spot updates, strong cross‑region consistency, massive distributed transactions, or poorly indexed SQL.

MySQL Replication Fundamentals

Full Replication Chain

Client
  |
  v
Primary(MySQL)
  | 1. Execute transaction
  | 2. Write binlog
  v
Binlog Dump Thread
  |
  v
Replica IO Thread
  | 3. Pull binlog
  v
Relay Log
  |
  v
Replica SQL / Worker Threads
  | 4. Replay relay log
  v
Replica Data

Key stages after a transaction commit are:

Business thread executes write on primary.

InnoDB generates redo/undo and commits.

Server writes to binlog.

Replica IO thread pulls binlog to a local relay log.

Replica SQL thread (or parallel workers) replays the log.

Replica data catches up with primary.

Binlog Format

Production environments usually set binlog_format=ROW because ROW avoids nondeterministic functions, aids audit/CDC, and prioritises correctness over a modest increase in log size. For large updates, binlog_row_image=MINIMAL can reduce row image size.

Sources of Replication Lag

Primary commits faster than replica replay.

Network jitter or cross‑datacenter latency.

Insufficient replica parallelism (single‑threaded).

Large transactions, batch updates, or DDL causing replay blockage.

Heavy read queries on replicas competing for CPU/IO/Buffer Pool.

Lock contention, page races, or disk flush jitter.

Therefore Seconds_Behind_Master or Seconds_Behind_Source are result metrics, not root‑cause indicators.

Replication Modes

Asynchronous : primary does not wait for replica acknowledgment; high performance but possible data loss on failover.

Semi‑synchronous : at least one replica must acknowledge receipt before primary returns success; higher safety.

Group Replication / InnoDB Cluster : stronger consistency and member governance, but higher complexity.

Typical production choice for internet services: single primary, multiple replicas, ROW format, semi‑sync, and parallel replay.

Consistency Is the Real Challenge

Four Consistency Requirements

Strong consistency reads – e.g., payment status, inventory deduction.

Read‑your‑write – a user must see the value they just wrote.

Session consistency – results should not “jump back and forth” within a single user session.

Eventual consistency reads – e.g., recommendation lists, reporting; short delays are acceptable.

Common Pitfalls

Pitfall 1: Routing All SELECT to Replicas

Queries that must go to the primary include:

Write‑after‑read.

Queries inside a transaction.

Business‑branch decisions based on the latest state.

Core data such as account balance, inventory, payment status.

Pitfall 2: Assuming a Global Lag Threshold Is Sufficient

Even if overall replica lag is only 200 ms, a critical request can still receive stale data. Routing must consider the consistency level required by each business scenario.

Pitfall 3: Believing Replicas Are Always Stable

Replicas can be impacted by slow SQL, full‑table scans, reporting jobs, or DDL replay, which may degrade the replication chain.

Read‑Write Splitting Strategies Comparison

Strategy 1 – Static Routing in Application

WRITE → primary

READ → replica

Advantages: quick to implement, no extra middleware, suitable for small or early projects.

Disadvantages: high business intrusion, difficult to govern uniformly, cannot gracefully handle transactions, lag, degradation, or failover.

Strategy 2 – Dynamic Routing in Application

Enhances static routing with lag awareness, weighted load balancing, forced primary for transactions, write‑after‑read fallback, and fault removal/recovery.

Advantages: high flexibility, fine‑grained business matching, no extra proxy nodes.

Disadvantages: increased code complexity, higher maintenance cost in multi‑language, multi‑service environments, routing logic may be scattered.

Strategy 3 – Database Proxy / Middleware

Typical solutions: ShardingSphere‑JDBC / ShardingSphere‑Proxy, ProxySQL, MySQL Router.

Advantages: unified routing governance, supports load balancing, fault removal, weight configuration, easier integration with observability, audit, throttling, and topology changes.

Disadvantages: adds an extra infrastructure layer, higher configuration and operational complexity, the middleware itself must be highly available.

Strategy 4 – Business‑Semantic Hybrid Routing

Core transactional queries force primary.

Write‑after‑read within a configurable window goes to primary.

Low‑consistency queries go to low‑latency replicas.

Reporting queries go to dedicated read‑only replicas.

During degradation, traffic can be switched back to primary or cache.

This mature approach combines business consistency grading with data‑routing governance.

Production‑Grade Architecture

Recommended Architecture Diagram

+----------------------+
                     |  Config / Registry   |
                     | Nacos / Apollo / etc |
                     +----------+-----------+
                                |
                                v
+----------------+     +------+-------+      +---------------------+
| Application A  | --> | RW Router    | ---> | Primary MySQL       |
| Application B  | --> | SDK/Proxy    |      | write + strong read |
| Application C  | --> +------+-------+      +----------+----------+
+----------------+            |                     |
                              |                     |
                              v                     v
                        +---------+---------+   +-------+--------+
                        | Replica Group     |   | Binlog / Semi  |
                        | replica-1         |   | Sync / Monitor |
                        | replica-2         |   +----------------+
                        | replica-analytics |
                        +---------+---------+
                                  |
                                  v
                        +---------+---------+
                        | Metrics / Alert   |
                        | Prometheus / etc  |
                        +-------------------+

Key Design Principles

Unified routing decision entry – avoid scattering “some queries go to primary” logic across services.

Force primary inside a transaction – keep the same primary for the whole transaction.

Replica grouping governance – assign different responsibilities (online queries, reporting, async tasks) to distinct replica groups.

Latency‑aware node removal – automatically downgrade weight or remove a replica when its latency spikes.

Mandatory degradation capability – one‑click switch to primary‑only, global read‑only mode, throttling, circuit‑break.

Decouple replication topology from business topology – use configuration center or service discovery instead of hard‑coding node addresses.

Key Configuration Recommendations

Primary Parameters

[mysqld]
server-id=1
log_bin=mysql-bin
binlog_format=ROW
binlog_row_image=MINIMAL
sync_binlog=1
innodb_flush_log_at_trx_commit=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
sync_master_info=1
sync_relay_log=1
sync_relay_log_info=1

Explanation: ROW + GTID is the modern production foundation. sync_binlog=1 and innodb_flush_log_at_trx_commit=1 improve crash‑recovery safety. GTID facilitates failover and replication rebuilding.

Replica Parameters

[mysqld]
server-id=2
read_only=ON
super_read_only=ON
relay_log=relay-bin
log_slave_updates=ON
gtid_mode=ON
enforce_gtid_consistency=ON
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=8
slave_preserve_commit_order=ON

Key points: enable parallel replay, preserve commit order, and protect against accidental writes.

Semi‑Sync Replication

rpl_semi_sync_master_enabled=ON
rpl_semi_sync_master_timeout=1000
rpl_semi_sync_slave_enabled=ON

Note: semi‑sync is not strong consistency; it only raises the probability that a transaction reaches at least one replica. On timeout it degrades to async, so monitoring the state change is required.

Engineering Upgrade for High Concurrency

Design Goals

Dynamic datasource up/down.

Primary‑replica role switching.

Lag‑threshold control.

Weighted load balancing.

Transaction awareness.

Write‑after‑read fallback to primary.

Circuit‑break, removal, recovery.

Observability hooks.

Support for gray releases and dynamic config.

Routing Decision Model

RouteDecision = f(
  sqlType,
  transactionContext,
  consistencyLevel,
  recentWriteWindow,
  replicaHealth,
  replicaLag,
  workloadTag
)

Parameters: sqlType: READ / WRITE / DDL. transactionContext: inside‑transaction flag. consistencyLevel: STRONG, SESSION, EVENTUAL. recentWriteWindow: e.g., 3 seconds after a write, read primary. replicaHealth: node health status. replicaLag: replication lag in ms. workloadTag: online query, reporting, async task.

Core Governance Under High Concurrency

1. Connection‑Pool Isolation

Primary and replicas must use independent connection pools. Primary pool should be conservative to protect the write path; each replica pool is configured per node. Reporting queries use a dedicated read‑only pool to avoid affecting online traffic.

2. Slow‑SQL Isolation

Separate pools for different workloads: replica-online: online read‑only queries. replica-batch: offline tasks, reporting, export. replica-cdc: data subscription, sync.

3. Latency‑Driven Weight Reduction

Latency < 100 ms: normal load.

100 ms – 1 s: only low‑consistency reads.

1 s – 3 s: downgrade weight.

≥ 3 s: remove from online traffic.

4. Forced Primary Switch Capability

Dynamic switches for spikes, failures, or maintenance:

Global primary‑only mode.

Tenant‑specific primary routing.

Interface‑specific primary routing.

Time‑window primary routing.

Production‑Level Code (Spring Boot + Dynamic Routing)

Consistency Level Enum

package com.example.rwdb.route;
public enum ConsistencyLevel {
    STRONG,
    SESSION,
    EVENTUAL
}

Replica Node Model

package com.example.rwdb.route;
import javax.sql.DataSource;
import java.util.concurrent.atomic.AtomicBoolean;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.concurrent.atomic.AtomicLong;
public class ReplicaNode {
    private final String name;
    private final DataSource dataSource;
    private final int weight;
    private final AtomicBoolean alive = new AtomicBoolean(true);
    private final AtomicLong lagMillis = new AtomicLong(0);
    private final AtomicInteger activeConnections = new AtomicInteger(0);
    public ReplicaNode(String name, DataSource dataSource, int weight) {
        this.name = name;
        this.dataSource = dataSource;
        this.weight = weight;
    }
    public String getName() { return name; }
    public DataSource getDataSource() { return dataSource; }
    public int getWeight() { return weight; }
    public boolean isAlive() { return alive.get(); }
    public void setAlive(boolean v) { alive.set(v); }
    public long getLagMillis() { return lagMillis.get(); }
    public void setLagMillis(long v) { lagMillis.set(v); }
    public int getActiveConnections() { return activeConnections.get(); }
    public void incrementActive() { activeConnections.incrementAndGet(); }
    public void decrementActive() { activeConnections.decrementAndGet(); }
}

Routing Context Holder

package com.example.rwdb.route;
public final class RouteContextHolder {
    private static final ThreadLocal<ConsistencyLevel> CONSISTENCY =
        ThreadLocal.withInitial(() -> ConsistencyLevel.EVENTUAL);
    private static final ThreadLocal<Long> LAST_WRITE_TIME = new ThreadLocal<>();
    private RouteContextHolder() {}
    public static void setConsistency(ConsistencyLevel level) { CONSISTENCY.set(level); }
    public static ConsistencyLevel getConsistency() { return CONSISTENCY.get(); }
    public static void markWrite() { LAST_WRITE_TIME.set(System.currentTimeMillis()); }
    public static Long getLastWriteTime() { return LAST_WRITE_TIME.get(); }
    public static void clear() { CONSISTENCY.remove(); LAST_WRITE_TIME.remove(); }
}

Read‑Write Router

package com.example.rwdb.route;
import org.springframework.transaction.support.TransactionSynchronizationManager;
import javax.sql.DataSource;
import java.util.Comparator;
import java.util.List;
import java.util.concurrent.ThreadLocalRandom;
import java.util.stream.Collectors;
public class ReadWriteRouter {
    private final DataSource primary;
    private final List<ReplicaNode> replicas;
    private final long sessionReadAfterWriteMillis;
    private final long maxAllowedLagMillis;
    public ReadWriteRouter(DataSource primary, List<ReplicaNode> replicas,
                          long sessionReadAfterWriteMillis, long maxAllowedLagMillis) {
        this.primary = primary;
        this.replicas = replicas;
        this.sessionReadAfterWriteMillis = sessionReadAfterWriteMillis;
        this.maxAllowedLagMillis = maxAllowedLagMillis;
    }
    public DataSource route(SqlIntent intent) {
        if (intent == SqlIntent.WRITE || intent == SqlIntent.DDL) {
            RouteContextHolder.markWrite();
            return primary;
        }
        if (TransactionSynchronizationManager.isActualTransactionActive()) {
            return primary;
        }
        ConsistencyLevel level = RouteContextHolder.getConsistency();
        if (level == ConsistencyLevel.STRONG) {
            return primary;
        }
        Long lastWrite = RouteContextHolder.getLastWriteTime();
        if (lastWrite != null && System.currentTimeMillis() - lastWrite < sessionReadAfterWriteMillis) {
            return primary;
        }
        List<ReplicaNode> candidates = replicas.stream()
            .filter(ReplicaNode::isAlive)
            .filter(node -> node.getLagMillis() <= maxAllowedLagMillis)
            .collect(Collectors.toList());
        if (candidates.isEmpty()) {
            return primary;
        }
        if (level == ConsistencyLevel.SESSION) {
            return candidates.stream()
                .min(Comparator.comparingLong(ReplicaNode::getLagMillis))
                .map(ReplicaNode::getDataSource)
                .orElse(primary);
        }
        return pickWeighted(candidates).getDataSource();
    }
    private ReplicaNode pickWeighted(List<ReplicaNode> candidates) {
        int totalWeight = candidates.stream().mapToInt(ReplicaNode::getWeight).sum();
        int random = ThreadLocalRandom.current().nextInt(totalWeight);
        int cur = 0;
        for (ReplicaNode node : candidates) {
            cur += node.getWeight();
            if (random < cur) {
                return node;
            }
        }
        return candidates.get(0);
    }
}

SQL Intent Enum

package com.example.rwdb.route;
public enum SqlIntent { READ, WRITE, DDL }

Dynamic DataSource (Spring)

package com.example.rwdb.datasource;
import com.example.rwdb.route.ReadWriteRouter;
import com.example.rwdb.route.SqlIntent;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
public class RoutingDataSource extends AbstractRoutingDataSource {
    private final ReadWriteRouter router;
    private static final ThreadLocal<SqlIntent> SQL_INTENT =
        ThreadLocal.withInitial(() -> SqlIntent.READ);
    public RoutingDataSource(ReadWriteRouter router) { this.router = router; }
    public static void markRead() { SQL_INTENT.set(SqlIntent.READ); }
    public static void markWrite() { SQL_INTENT.set(SqlIntent.WRITE); }
    public static void markDdl() { SQL_INTENT.set(SqlIntent.DDL); }
    public static void clear() { SQL_INTENT.remove(); }
    @Override
    protected Object determineCurrentLookupKey() { return null; }
    @Override
    protected javax.sql.DataSource determineTargetDataSource() {
        return router.route(SQL_INTENT.get());
    }
}

Consistency Annotation and AOP

package com.example.rwdb.annotation;
import com.example.rwdb.route.ConsistencyLevel;
import java.lang.annotation.*;
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ReadConsistency {
    ConsistencyLevel value() default ConsistencyLevel.EVENTUAL;
}

package com.example.rwdb.aop;
import com.example.rwdb.annotation.ReadConsistency;
import com.example.rwdb.route.RouteContextHolder;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.springframework.core.annotation.AnnotationUtils;
import org.springframework.stereotype.Component;
@Aspect
@Component
public class ReadConsistencyAspect {
    @Around("@within(com.example.rwdb.annotation.ReadConsistency) || @annotation(com.example.rwdb.annotation.ReadConsistency)")
    public Object around(ProceedingJoinPoint jp) throws Throwable {
        ReadConsistency ann = AnnotationUtils.findAnnotation(jp.getTarget().getClass(), ReadConsistency.class);
        if (ann == null) {
            ann = AnnotationUtils.findAnnotation(((org.aspectj.lang.reflect.MethodSignature) jp.getSignature()).getMethod(), ReadConsistency.class);
        }
        if (ann != null) {
            RouteContextHolder.setConsistency(ann.value());
        }
        try {
            return jp.proceed();
        } finally {
            RouteContextHolder.clear();
        }
    }
}

Replica Lag Probe

package com.example.rwdb.health;
import com.example.rwdb.route.ReplicaNode;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.jdbc.core.JdbcTemplate;
import java.util.List;
import java.util.Map;
public class ReplicaHealthChecker {
    private final List<ReplicaNode> replicas;
    public ReplicaHealthChecker(List<ReplicaNode> replicas) { this.replicas = replicas; }
    @Scheduled(fixedDelay = 3000)
    public void refresh() {
        for (ReplicaNode replica : replicas) {
            try {
                JdbcTemplate jt = new JdbcTemplate(replica.getDataSource());
                Map<String, Object> row = jt.queryForMap("SHOW SLAVE STATUS");
                Object lag = row.get("Seconds_Behind_Master");
                long lagMs = lag == null ? Long.MAX_VALUE : ((Number) lag).longValue() * 1000L;
                replica.setLagMillis(lagMs);
                replica.setAlive(true);
            } catch (Exception e) {
                replica.setAlive(false);
                replica.setLagMillis(Long.MAX_VALUE);
            }
        }
    }
}

Business Scenario: Order Creation

When a user creates an order, the flow is:

Create order (primary transaction).

Deduct inventory.

Write payment record.

Return order ID.

Front‑end immediately queries order details.

If the detail query is routed to a lagging replica, the user may see “order not found” or stale status.

Correct Implementation

createOrder

runs in a primary‑only transaction.

Within a configurable window (e.g., 3 seconds) after a successful write, reads are forced to primary.

Order list queries can be eventual‑consistent, while order detail queries are strong‑consistent.

Sample Service Code

package com.example.order.service;
import com.example.rwdb.annotation.ReadConsistency;
import com.example.rwdb.route.ConsistencyLevel;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
@Service
public class OrderService {
    private final OrderRepository orderRepository;
    private final InventoryRepository inventoryRepository;
    public OrderService(OrderRepository orderRepository, InventoryRepository inventoryRepository) {
        this.orderRepository = orderRepository;
        this.inventoryRepository = inventoryRepository;
    }
    @Transactional
    public Long createOrder(CreateOrderCommand cmd) {
        inventoryRepository.deduct(cmd.getSkuId(), cmd.getQuantity());
        Order order = Order.create(cmd.getUserId(), cmd.getSkuId(), cmd.getQuantity());
        orderRepository.save(order);
        return order.getId();
    }
    @ReadConsistency(ConsistencyLevel.STRONG)
    public OrderDTO getOrderDetail(Long orderId) {
        return orderRepository.findDetail(orderId);
    }
    @ReadConsistency(ConsistencyLevel.EVENTUAL)
    public PageResult<OrderListItemDTO> queryHistoryOrders(Long userId, int pageNo, int pageSize) {
        return orderRepository.queryUserOrders(userId, pageNo, pageSize);
    }
}

ShardingSphere Production Example

spring:
  shardingsphere:
    datasource:
      names: primary,replica0,replica1
      primary:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://mysql-primary:3306/order_db?useSSL=false&serverTimezone=Asia/Shanghai
        username: app
        password: app_pwd
        maximum-pool-size: 40
        minimum-idle: 10
      replica0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://mysql-replica-0:3306/order_db?useSSL=false&serverTimezone=Asia/Shanghai
        username: app
        password: app_pwd
        maximum-pool-size: 60
        minimum-idle: 20
      replica1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://mysql-replica-1:3306/order_db?useSSL=false&serverTimezone=Asia/Shanghai
        username: app
        password: app_pwd
        maximum-pool-size: 60
        minimum-idle: 20
    rules:
      readwrite-splitting:
        data-sources:
          order_rw:
            static-strategy:
              write-data-source-name: primary
              read-data-source-names: replica0,replica1
            load-balancer-name: replica-weight
        load-balancers:
          replica-weight:
            type: WEIGHT
            props:
              replica0: 2
              replica1: 3
    props:
      sql-show: false

Even with middleware, business layers should still mark strong‑consistency interfaces, force primary inside transactions, control write‑after‑read windows, provide degradation switches, and add observability hooks.

High‑Availability Design

Impact of Primary Failure

Application connection strings.

Connection‑pool caches.

Middleware topology caches.

In‑flight transactions.

Cache‑DB double‑write consistency.

Message‑queue consumption idempotency.

Recommended HA Stack

MySQL master‑slave replication.

Orchestrator / MHA / cloud RDS HA switch.

VIP / Proxy / service‑discovery updating primary address.

Application connection‑pool auto‑reconnect.

Failover Process

1. Detect primary unavailability
2. Choose the best replica as candidate primary
3. Verify replication catch‑up and data integrity
4. Promote to new primary
5. Update topology metadata and access entry points
6. Refresh application connection pools
7. Re‑attach old primary as replica after recovery

Primary Selection Principles

Minimal lag.

Healthy semi‑sync link.

No long replication interruptions.

Best hardware and network.

No obvious slow‑SQL or high‑load risk.

Regular Disaster‑Recovery Drills

Conduct at least quarterly drills covering primary outage failover, replica lag escalation, forced primary‑only traffic, and configuration‑center mis‑configuration rollback.

Monitoring System

Must‑Monitor Metrics

Replication chain : Seconds_Behind_Master / Seconds_Behind_Source IO thread status, SQL thread status.

Relay log backlog.

GTID execution offset.

Primary :

QPS, TPS.

Active connections.

Slow‑SQL count.

Buffer‑pool hit rate.

InnoDB row lock wait.

Redo/binlog flush latency.

Replica :

Query RT.

Replication replay lag.

Slow‑SQL count.

CPU / memory / disk utilization.

Read‑only state integrity.

Application routing :

Primary hit rate.

Replica hit rate.

Degradation‑to‑primary count.

Replica removal count.

Strong‑consistent read proportion.

Write‑after‑read fallback count.

Alert Levels

P1: replication break, primary down, full failover failure.

P2: replica lag > 3 s for 5 min.

P2: too many replica removals leaving insufficient replicas.

P3: strong‑consistent read ratio spikes (possible degradation).

P3: primary read traffic abnormal rise (routing may be broken).

Prometheus Scrape Config

scrape_configs:
  - job_name: mysql_exporter
    static_configs:
      - targets:
        - mysql-primary-exporter:9104
        - mysql-replica0-exporter:9104
        - mysql-replica1-exporter:9104

Common Online Issues and Remedies

Write‑After‑Read Returns Nothing

Root cause: request routed to a lagging replica.

Remediation:

Force primary within the write‑after‑read window.

Mark critical interfaces as strong‑consistent.

When latency is high, degrade sensitive interfaces back to primary.

Adding Replicas Does Not Improve Performance

Typical causes:

Application does not actually route traffic to replicas.

Hotspot queries all hit a single replica.

SQL itself is inefficient.

Replicas are burdened with reporting or export tasks.

Remediation:

Check routing hit rate.

Introduce replica role segregation.

Perform SQL and index tuning.

Adjust weights and load‑balancing strategy.

Primary Pressure Remains High

Possible reasons:

Many strong‑consistent reads still hit primary.

Oversized transactions.

Low cache hit rate.

Hot interfaces still run complex queries on primary.

Remediation:

Identify which interfaces hit primary.

Cache hot data upstream.

Shorten transaction duration.

Separate write model from query model.

Replica Lag Grows Due to Slow SQL

Steps:

Immediately remove the problematic replica from online traffic.

Kill the slow SQL or pause reporting jobs.

Observe replication catch‑up.

If necessary, rebuild the replica.

Move heavy queries to a dedicated node.

Performance Optimization Advanced Advice

SQL and Index Tuning

No read‑write splitting scheme can replace proper SQL and index design. Focus on avoiding large deep pagination, implicit type conversion, functions on indexed columns, respecting left‑most prefix rule for composite indexes, and avoiding SELECT *.

Transaction Optimization

Shorten transaction duration.

Avoid long transactions that hold locks and undo logs.

Split massive batch updates.

Limit rows affected per transaction.

Cache and Read‑Write Splitting Cooperation

Hot keys query cache first.

Cache miss falls back to read‑write splitting layer.

Core write path updates cache via double‑delete or binlog subscription.

Sharding Integration

When primary write capacity becomes a bottleneck, read‑write splitting evolves to vertical splitting, horizontal sharding / partitioning, distributed ID generation, and query‑layer aggregation or search.

Case Study: E‑Commerce Big‑Sale Scenario

Background

Peak QPS > 80 k.

Order and product domains experience massive read spikes.

After a flash‑sale, users immediately refresh the order page; strong consistency is critical.

Operations run export and reporting tasks on the same DB.

Initial Problems

Static split: all SELECT to replicas, round‑robin between two replicas.

Resulted in missing orders, hotspot replica saturation, replica lag > 10 s, and primary overload after forced fallback.

Upgrade Plan

1 primary + 3 replicas.

Separate online replica group (eventual consistency) and dedicated reporting replica.

Mark order‑detail and payment‑status APIs as strong‑consistent.

Force primary for reads within 5 seconds after order creation.

Exclude replicas with latency > 1 s from online traffic.

Global dynamic primary‑only switch during promotion.

Integrate Prometheus + Grafana + alert platform.

Results

Primary query pressure reduced by ~55 %.

Online query P99 dropped from 320 ms to 85 ms.

“Order created but not found” incidents virtually disappeared.

Replica lag stabilized from minute‑level to sub‑second.

Database failover time decreased from > 10 min to < 2 min.

The key was not merely adding replicas but establishing a complete governance system: consistency grading, traffic layering, replica role segregation, dynamic degradation, observability, and regular drills.

Best‑Practice Checklist

Primary enables ROW + GTID.

Replicas enable read‑only and parallel replay.

All queries inside a transaction are forced to primary.

Core business interfaces define consistency level.

Write‑after‑read window defaults to primary.

Replica lag threshold participates in routing decisions.

Primary and replica connection pools are configured independently.

Online queries and reporting queries use separate replica groups.

Unified routing governance via middleware or SDK.

Monitor primary hit rate, replica removal count, and latency‑based metrics.

Provide one‑click primary switch and one‑click downgrade.

Conduct regular primary switch and replication‑exception drills.

Conclusion

MySQL master‑slave read‑write splitting is a cross‑layer engineering capability covering database kernel, application architecture, middleware governance, observability, and fault response. A mature solution must answer:

Which requests require strong consistency and which can tolerate eventual consistency?

How to guarantee write‑after‑read?

How to automatically degrade when replica latency spikes?

How to switch and recover when the primary fails?

How to avoid mutual throttling between primary and replicas during peaks?

When these questions have systematic answers, read‑write splitting delivers real value: increased throughput, observable degradation and failover, and scalable growth.