MySQL Architecture Design: Business Analysis, Common Deployment Patterns, Sharding Strategies, and High‑Availability Practices

MySQL Architecture Design – Business Analysis

(1) Read‑Heavy, Write‑Light

In Internet e‑commerce or gaming systems, services must run 24/7, so the database layer needs disaster‑recovery and redundancy. When reads dominate, a master can serve both reads and occasional writes, while slaves handle most read traffic. If the master fails, a standby slave is promoted to read‑write.

(2) Read‑Heavy, Write‑Light (E‑commerce)

Typical e‑commerce setups use one master with 4–6 slaves; all slaves attach to the master. During a switchover, the master’s read‑write role moves to a second master, and its slaves are re‑attached accordingly.

(3) Read‑Heavy, Write‑Light (Gaming)

Gaming workloads often have a single master with more than ten slaves. Some slaves can be attached to a new read‑only replica (M(R)) to reduce pressure on the primary master and improve fault tolerance.

(4) Write‑Heavy, Read‑Light

When writes dominate, both reads and writes can stay on a single master (M1‑WR), while a second instance provides standby disaster‑recovery only.

(5) Balanced Read/Write

When reads and writes are comparable, place both on M1‑WR and offload a portion of reads to a second read‑only instance (M2‑R) located in another data center.

MySQL Architecture Design – Common Deployment Patterns

(1) Strong Consistency

For scenarios requiring real‑time read/write consistency (e.g., order processing), all traffic stays on the primary master; the secondary node acts only as standby.

(2) Weak Consistency

For less time‑critical workloads (e.g., reporting, static configuration), a secondary replica can serve read traffic, reducing load on the primary.

(3) Intermediate Consistency

A hybrid approach uses an additional read‑only server (S1‑R) in the same data center for read offloading, while a remote standby handles disaster recovery.

(4) Statistical Workloads

Large‑scale statistics (PV/UV, traffic aggregation) are resource‑intensive. Real‑time stats may require querying the primary, while near‑real‑time stats can be built from binlog parsing and written to separate tables or message queues.

(5) Historical Data Migration

To offload stale data (e.g., completed orders) without impacting online performance, migrate it to a separate historical database (MySQL, NoSQL, or a data warehouse) using slave queries and business‑rule filters.

(6) MySQL Sharding

Sharding splits data across multiple servers to handle growth. It can be vertical (separating tables into different instances) or horizontal (splitting rows of a single table).

(6.1) Vertical Splitting

6.1.1 Horizontal (table) Splitting

Separate unrelated tables (e.g., logs vs. orders) into different databases to improve capacity and performance.

6.1.2 Vertical (instance) Splitting

Place strongly related tables in the same instance while moving unrelated databases to separate instances.

6.1.3 Combined Horizontal & Vertical Splitting

When a single instance becomes a bottleneck, apply both table‑level and instance‑level splits.

6.1.4 Business‑Level Splitting

Split data by business dimension (e.g., per‑vendor or per‑user) when tables are unrelated across that dimension.

(6.2) Horizontal Splitting

Horizontal sharding distributes rows across many tables or databases, often using a modulo of a key (e.g., user_id % 1024).

Cluster Management

Vertical scaling adds more instances; horizontal scaling adds more tables/databases and requires updating routing metadata (Configure DB, Proxy, or Data Engine Service) to keep queries directed to the correct shard.