MySQL Master‑Slave Synchronization: Three Common Approaches and Consistency Solutions

In high‑concurrency internet services, read operations far exceed writes, making it inefficient to handle both on a single database server; therefore, read‑write separation is employed by designating a master for writes and one or more slaves for reads.

To further reduce database load, distributed cache clusters such as Redis or Memcached are often placed in front of the database, shifting read pressure to the application layer while following the same principle of offloading the backend.

Problem: Read‑write separation introduces latency‑induced inconsistencies between master and slaves, requiring robust consistency solutions.

1. Half‑Synchronous Replication

In this mode, the master waits until at least one slave acknowledges receipt of the transaction in its relay log before returning a response to the client, improving data safety at the cost of added latency.

Implementation details for MySQL include installing the semi‑sync plugin (available from MySQL 5.5) and ensuring the transaction’s binlog reaches at least one slave before commit.

Advantages: Simple, uses native database features.

Disadvantages: Increases write latency and can reduce throughput; performance may degrade under network issues or slave failures.

2. Database Middleware Synchronization

All reads and writes pass through a middleware layer; writes are routed to the master, reads to slaves. Within a configurable sync window (e.g., 500 ms), recent write keys are tracked; reads for those keys are temporarily routed to the master to ensure consistency, then revert to slaves after the window.

Advantages: Guarantees strong consistency.

Disadvantages: Higher cost and complexity due to the middleware component.

3. Cache‑Key Write Tracking Method

When a key is written, it is recorded in a cache with a short expiration (e.g., 500 ms). Subsequent reads first check the cache: if the key is present, the request is directed to the master for the latest data; otherwise, it goes to the slave, maintaining read‑write separation.

Advantages: Lower cost compared to full middleware.

Disadvantages: Introduces additional cache layer and extra cache operations for both reads and writes to preserve consistency.