Designing Resilient MySQL Architectures for 24/7 Online Services

Introduction

Unlike traditional industries with limited service hours, internet e‑commerce and gaming require 24/7 availability, so application and database architectures must provide disaster‑recovery and mutual backup; MySQL should incorporate HA at the design stage.

1. MySQL Architecture Design – Business Analysis

(1) Read‑Heavy, Write‑Light

In cross‑datacenter deployments, a master handles both reads and writes; read consistency is critical, so reads are placed on the master.

A standby replica (M(R)) is used only after the primary write‑enabled master (M(WR)) fails, then it becomes read‑write.

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

Typical e‑commerce scenario uses one master with 4‑6 slaves; all slaves attach to the master.

During failover, the read‑write workload moves from M1 to M2, and all slaves of M1 are re‑attached to M2.

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

Gaming systems often have one master with more than ten slaves; some slaves can be moved to a new read‑only replica (M(R)) to reduce pressure and maintain service when a server fails.

(4) Write‑Heavy, Read‑Light

When reads do not affect write performance, both read and write can reside on a single master (M1(WR)), while a standby instance provides only disaster‑recovery without serving reads or writes.

(5) Balanced Read/Write

Reads should not impact writes; primary master M1(WR) handles all writes, while a portion of reads are served by a secondary read‑only instance M2(R) deployed across datacenters.

2. Common MySQL Architecture Patterns

(1) Strong Consistency

For workloads requiring real‑time read/write (e.g., order processing), both reads and writes stay on M1; M2 acts as standby.

(2) Weak Consistency

For less critical reads such as reports or static configuration, a secondary instance M2 can offload read traffic.

(3) Intermediate Consistency

A middle‑ground approach adds a read‑only replica S1(R) in the same datacenter to reduce load on M1(WR), while M2 remains standby in another IDC.

(4) Statistical Workloads

Large‑scale statistics (PV, UV, page views) consume significant resources; real‑time stats are limited by MySQL’s master‑slave latency, so near‑real‑time stats use binlog parsing to populate summary tables, while offline stats can be moved to data warehouses or NoSQL stores.

(5) Historical Data Migration

To avoid impacting online performance, historical data (e.g., completed orders) is migrated from the online database to a separate historical store (MySQL, NoSQL, or Hadoop) using slave queries and business rules, with routing logic handling combined online‑historical queries.

(6) MySQL Sharding

Sharding addresses storage scaling limits of shared‑disk solutions; MySQL sharding can be vertical or horizontal.

(6.1) Vertical Partitioning

6.1.1 Horizontal Partitioning

Separate tables (e.g., logs and orders) into different databases to reduce coupling and improve scalability.

6.1.2 Vertical Partitioning

Place unrelated databases on separate instances to avoid cross‑database operations.

6.1.3 Combined Horizontal & Vertical Partitioning

When an initial deployment uses a single instance, later growth may require both dimensions of partitioning.

6.1.4 Business‑Level Partitioning

Split data by business dimension such as supplier or user, each with its own complete dataset.

(6.2) Horizontal Partitioning

6.2.1 Real‑time vs Historical Data

Separate online and historical data into different databases.

6.2.2 Single‑Database Multi‑Table Splitting

Large tables are divided into multiple smaller tables (e.g., hash‑based sharding) to improve performance.

6.2.3 Multi‑Database Multi‑Table Splitting

When a single database cannot meet capacity, distribute tables across multiple databases, using a configuration DB or proxy to route queries.

6.2.4 Horizontal Partitioning Guidelines

Evenly distribute data across shards.

Avoid cross‑database transactions.

Minimize cross‑database queries.

(6.3) Cluster Management

Vertical scaling splits an instance into multiple instances; horizontal scaling adds more shards, requiring updates to configuration services.

Both scaling types must keep routing information synchronized.