MySQL Replication, Data Sharding, and Distributed Database Deployment Overview

Distributed databases and storage are among the most challenging components of distributed systems, and solving these problems is essential for supporting massive user traffic.

MySQL Replication

Master‑slave replication copies data from a primary MySQL server to one or more replicas, enabling read/write separation and higher read throughput. The process records updates to the binary log, which a replication thread reads and applies on the replica via the relay log.

One‑master‑multiple‑slave setups distribute read load across several replicas, offering load balancing, dedicated servers for specific queries, easier cold backups, and high availability when a replica fails.

Master‑master replication uses two primary servers that replicate each other's writes, providing write availability and failover; if one master fails, the other continues to accept writes and synchronizes data back when the failed node recovers.

Key considerations include avoiding simultaneous writes to both masters, recognizing that replication improves read concurrency but not write capacity, and handling schema changes carefully to prevent large synchronization delays.

Data Sharding

Sharding splits a large table into smaller pieces stored on different servers, using a shard key to route queries to the appropriate node, thereby increasing storage capacity and write scalability.

Hard‑coded sharding logic in application code can be simple (e.g., odd/even user IDs) but couples the application to the partitioning scheme.

External mapping tables allow the application to look up which server holds a given shard, decoupling logic from code.

Middleware such as Mycat provides transparent distributed‑database routing, supporting shard‑based queries, joins, and transactions.

Sharding also introduces challenges like data migration when adding new nodes and maintaining consistent routing rules.

Database Deployment Schemes

1. Single service with a single database – the simplest architecture for low traffic.

2. Master‑slave replication for scaling read traffic.

3. Two web services each with its own database (business‑level partitioning) to increase capacity and reduce coupling.

4. Comprehensive deployment combining replication, sharding, and multiple services to meet high‑availability and performance demands.

Alternative NoSQL solutions can provide greater scalability but may require additional mechanisms to achieve eventual consistency.