Choosing the Right Database Architecture: Master‑Slave, Dual‑Master, and Beyond

Database Architecture Principles

Four essential principles guide any database design: high availability, high performance, data consistency, and scalability.

Common Architecture Options

Option 1: Primary‑Backup (single master, standby)

High‑availability analysis: if the primary fails, a keepalive tool automatically switches to the standby, transparent to the application.

Performance analysis: all reads and writes go to the primary, creating a bottleneck; most internet services are read‑heavy, so read latency limits overall throughput. The standby only consumes ~50% of resources.

Consistency analysis: all operations target the primary, so no consistency issues arise.

Scalability analysis: adding replicas does not improve read performance because reads remain on the primary.

Practical considerations: performance can be improved with efficient indexes and caching; scalability requires sharding (分库分表).

jdbc:mysql://vip:3306/xxdb

Option 2: Dual‑Master (two masters with load balancing)

High‑availability analysis: if one master fails, the other continues serving traffic without interruption.

Performance analysis: read/write capacity roughly doubles compared with Option 1.

Consistency analysis: data consistency issues appear; a dedicated consistency‑resolution approach is required.

Scalability analysis: adding a third master is possible but adds another data‑sync layer, increasing latency.

Practical considerations: similar to Option 1 regarding sharding for further scaling.

jdbc:mysql://vip:3306/xxdb

Option 3: Primary‑Replica (one master, multiple slaves, read‑write separation)

High‑availability analysis: the master is a single point of failure; slaves are highly available for reads.

Performance analysis: reads are offloaded to slaves, alleviating the primary bottleneck; different indexes can be applied per replica.

Consistency analysis: replication lag can cause stale reads on slaves.

Scalability analysis: adding more slaves improves read throughput but increases the load on the master for binlog streaming.

Practical considerations: sharding can further improve scalability; careful handling of write‑only paths is needed.

jdbc:mysql://master-ip:3306/xxdb

jdbc:mysql://slave1-ip:3306/xxdb

jdbc:mysql://slave2-ip:3306/xxdb

Option 4: Dual‑Master + Replicas (hybrid)

Combines the high‑availability of dual‑master with read‑write separation via replicas.

High‑availability and performance are both strong, but consistency and data‑sync latency become more complex.

Practical considerations: similar to Option 2 for sharding; additional replication layers increase delay.

jdbc:mysql://vip:3306/xxdb

jdbc:mysql://slave1-ip:3306/xxdb

jdbc:mysql://slave2-ip:3306/xxdb

Consistency Solutions

Category 1 – Master‑Slave Consistency

Replication introduces a window where the master and slave diverge because the slave pulls binlog events and replays them. During this window, reads from the slave may return stale data.

Typical solutions:

Ignore the inconsistency if the business can tolerate delayed data.

Force all reads to the master (use primary‑backup architecture) and optionally add a cache layer to boost read performance.

Cache‑first strategy: write a key with a TTL longer than the replication lag; reads first check the cache, falling back to the master if the cache misses.

Semi‑synchronous replication: the master waits for at least one slave to acknowledge receipt before confirming a write, reducing lag at the cost of higher write latency.

Introduce a database middleware (e.g., MyCat) to route reads/writes intelligently; this adds complexity and cost.

Category 2 – DB‑Cache Consistency

Typical cache workflow:

Invalidate cache.

Write to the database.

Read from cache? If miss, read from database.

After reading from the database, write the result back to the cache.

If steps 3 and 4 interleave improperly, the cache can become stale, leading to dirty reads.

Solutions include setting an appropriate TTL on cached entries, using read‑through or write‑through patterns, and ensuring cache invalidation aligns with database writes.

Personal Insights

Adding caches and indexes is a universal way to improve database performance.

Sharding (分库分表) offers massive scalability but introduces its own challenges.

The choice of architecture must match the specific business scenario; most workloads favor the primary‑backup pattern with optional sharding, while only a few require full read‑write separation.

Never adopt an architecture without considering the operational impact on the target use case.

Architecture Evolution Paths

Four possible evolution sequences illustrate how a system can progress from a simple primary‑backup setup to a fully sharded, hybrid architecture:

Option 1 → Option 1 + sharding → Option 2 + sharding → Option 4 + sharding.

Option 1 → Option 1 + sharding → Option 3 + sharding → Option 4 + sharding.

Option 1 → Option 2 → Option 4 → Option 4 + sharding.

Option 1 → Option 3 → Option 4 → Option 4 + sharding.