MySQL Read-Write Separation: Principles and Implementation

When to Introduce

In typical internet business scenarios, the read-write ratio can reach 8:2 or even higher.

Introduction Conditions

  • Read request QPS continuously exceeds single database instance processing capacity
  • Read operations cause database CPU usage to remain high
  • Many read request timeouts during peak periods

Read-Write Separation Architecture

  1. Primary (Master): Handles all write operations
  2. Slave: Specifically handles read operations

Master-Slave Synchronization Lag Solutions

Solution 1: Read After Write

  • Within a period after data is written, all read operations are directed to the primary database

Solution 2: Secondary Query

  • First query on slave, if not found, degrade to primary database query

Solution 3: Business-Specific Handling

  • Implement tiered processing for different business levels

Read-Write Separation Implementation

Based on Programming and Configuration (Application Side)

spring:
  datasource:
    master:
      url: jdbc:mysql://master-host:3306/db
    slave:
      url: jdbc:mysql://slave-host:3306/db

Transaction Handling

  • Operations within transactions are recommended to be forcibly routed to the primary database

Database Middleware Proxy

Main Products

ProductFeatures
MySQL ProxyOfficial offering
ShardingSphereApache top-level project
AtlasDeveloped by Qihoo 360
AmoebaEarly open-source solution

Common Issues and Solutions

Master-Slave Lag Issues

  1. Force primary database
  2. Semi-synchronous replication
  3. Application-layer lag handling

Read-Write Consistency

  • Key business configured to force primary database
  • Tiered query strategy
  • Lag compensation mechanism