Master‑Slave Replication & Read/Write Splitting in MySQL: Benefits, Pitfalls, Tips

1. Master‑Slave Read/Write Splitting

Most internet services are read‑heavy, so separating read and write traffic allows independent scaling of read replicas. The primary database (master) handles writes, while one or more replicas (slaves) serve read queries.

Cache vs MySQL read/write splitting: because cache adds complexity (consistency, penetration, avalanche), it is recommended to adopt read/write splitting first and only introduce cache when the database layer can no longer handle the load.

1.1 Core Concepts

Original DB is the master, responsible for data writes.

Copy target DBs are slaves, responsible for data reads.

Key points of read/write splitting:

Data is copied via master‑slave replication.

Developers continue to use a single logical DB interface, unaware of the underlying master‑slave topology.

2. Master‑Slave Replication

MySQL replication relies on the binary log (binlog), which records every change on the master. Replication is typically asynchronous: the master does not wait for the binlog to be synchronized to slaves before returning a result.

2.1 Replication Process

The slave creates an I/O thread that requests the master’s binlog updates and writes them to a relay log.

The master creates a log‑dump thread to send the binlog to the slave.

The slave also creates an SQL thread that reads the relay log and replays the changes, achieving eventual consistency.

Using a separate log‑dump thread makes replication asynchronous, avoiding impact on the master’s primary update flow. The slave writes to a relay log instead of directly to its storage to reduce latency.

Because the master returns results without waiting for binlog persistence, a catastrophic failure (e.g., disk loss before the binlog is flushed) can cause data loss and inconsistency, though the probability is low.

If the master crashes and the binlog is lost, the inconsistency must be recovered manually.

After replication is set up:

Writes go only to the master.

Reads go only to slaves.

This isolates write‑induced locks from read queries, allowing multiple slaves to share read traffic and improving read concurrency. Slaves can also serve as backup nodes.

Can we keep adding slaves indefinitely? No. Each slave adds an I/O thread on the master, increasing resource consumption and limited by the master’s network bandwidth; typically a master supports 3‑5 slaves.

2.2 Side Effects of Replication

Consider a social‑media scenario where a user posts a “moment”. The workflow is:

Synchronous operation: write to the DB.

Asynchronous operation: send the moment ID to a message queue for downstream audit.

If the master‑slave lag is significant, the consumer may not find the moment in the slave, causing errors.

Mitigation strategies:

2.3.1 Data Redundancy : Include all necessary moment data in the MQ message, avoiding a slave lookup.

2.3.2 Use Cache : Write the moment to a cache while persisting to the DB; consumers read from the cache first. This works well for new data but can cause inconsistency if cache is updated before the DB.

2.3.3 Query the Master : Consumers read directly from the master. This should be used sparingly and only when the read volume is low enough not to overload the master.

Example of cache inconsistency: Thread A updates cache to 1, Thread B updates cache to 2, Thread B then writes DB to 2, Thread A later writes DB to 1, leaving DB=1 and cache=2.

2.3 Reducing Replication Lag

Monitoring the Seconds_Behind_Master metric from SHOW SLAVE STATUS\G is a common way to detect lag. However, if the I/O thread is overloaded, this metric may stay at 0 while lag exists. Comparing master and slave binlog positions provides a more accurate view.

3. Accessing the DB

With replication, the application must distinguish between a master address (writes) and multiple slave addresses (reads), increasing complexity. Middleware solutions have emerged to abstract this.

3.1 In‑Process Middleware (e.g., TDDL)

Embedded in the application, it acts as a data‑source proxy managing multiple data sources (master or slaves). It routes SQL statements to the appropriate source.

Advantages : Simple to use, low deployment cost, suitable for small teams with limited ops capability.

Disadvantages : Limited to Java, upgrades depend on the application.

3.2 Stand‑alone Proxy Layer (Mycat, Atlas, DBProxy)

Deployed as an independent server, it intercepts SQL, rewrites it if necessary, and forwards it to the correct data source.

Advantages : Uses standard MySQL protocol, supports multiple languages, easier to maintain and upgrade for medium‑to‑large teams.

Disadvantages : Adds an extra network hop (application → proxy → DB), incurring performance overhead.

4. Summary

Master‑slave replication can be viewed as a generic data‑copy technique that provides redundancy, backup, and horizontal scalability. When using it, consider:

Consistency vs. write performance trade‑offs: guaranteeing writes on all slaves hurts performance; most internet services favor performance over strong consistency.

Replication lag, which can cause “missing data” anomalies; monitor lag (ms‑level is normal, seconds‑level should trigger alerts).

Real‑world examples of similar replication concepts include Redis master‑slave for read/write splitting, Elasticsearch index shard replication, and HDFS block replication across DataNodes. Different components have varying consistency and latency requirements, but the underlying design principles remain consistent.