Choosing the Right MySQL Architecture: Master‑Standby, Dual‑Master, and Consistency Strategies

Database Architecture Principles

Four fundamental goals guide any database design: high availability, high performance, strong consistency, and easy scalability.

Common Architecture Schemes

Scheme 1: Master‑Standby (Primary‑Only with Failover)

JDBC URL: jdbc:mysql://vip:3306/xxdb High‑availability analysis: If the primary fails, a keepalive tool automatically switches traffic to the standby; the change is transparent to the application.

Performance analysis: All reads and writes go to the primary, creating a bottleneck; the standby is idle 50% of the time.

Consistency analysis: No data‑consistency issues because only the primary handles read/write.

Scalability analysis: Adding slaves does not improve read performance, so overall scalability is limited.

Practical deployment: Performance can be improved with better indexing and caching; scalability can be addressed by sharding.

Scheme 2: Dual‑Master (Active‑Active Load Balancing)

JDBC URL: jdbc:mysql://vip:3306/xxdb High‑availability analysis: If one master fails, the other continues serving traffic without changes to the application.

Performance analysis: Read/write capacity roughly doubles compared with Scheme 1.

Consistency analysis: Data‑consistency problems arise; see the consistency solutions section.

Scalability analysis: Adding a third master is possible but adds synchronization overhead; the author recommends moving to Scheme 4 instead.

Practical deployment: Resolve consistency with the solutions below; primary‑key conflicts can be handled by a distributed ID service.

Scheme 3: Master‑Slave with Read/Write Separation

JDBC URLs:

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

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

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

High‑availability analysis: The master is a single point of failure; if it goes down, writes stop.

Performance analysis: Reads are offloaded to slaves, improving overall throughput; slaves can have different indexes to suit their workloads.

Consistency analysis: Replication lag can cause stale reads; see the consistency solutions.

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

Practical deployment: Consistency can be mitigated with the methods described later; the master‑single‑point issue remains.

Scheme 4: Dual‑Master + Slave (Hybrid)

JDBC URLs:

jdbc:mysql://vip:3306/xxdb

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

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

High‑availability analysis: Both masters provide failover.

Performance analysis: Similar to Scheme 2.

Consistency analysis: Replication between the two masters introduces additional lag.

Scalability analysis: Read scaling via slaves, but synchronization complexity grows.

Practical deployment: Same consistency challenges as Scheme 2, with extra data‑delay due to the extra replication layer.

Consistency Solutions

1. Master‑Slave Consistency

Data is synchronized from the master to slaves via binlog; during the sync window the master and slaves may diverge, causing stale reads.

Ignore the lag if the business can tolerate eventual consistency.

Force all reads to the master (master‑only architecture) and use a cache to boost read performance; ensure the cache is highly available to avoid cascade failures.

Read‑through cache with TTL longer than the replication delay: generate a cache key on write, set an expiration, and read from cache first; if a miss occurs, fall back to the slave.

Semi‑synchronous replication: the master waits for at least one slave to acknowledge before confirming a write, increasing latency but guaranteeing consistency.

Introduce a database middleware (e.g., MyCat) to route reads intelligently; this adds operational cost and another failure domain.

2. DB‑Cache Consistency

Typical cache workflow: evict → write DB → read cache? → read DB → write cache. If step 4 (write cache) occurs before step 3 (read DB), stale data can appear.

Set an expiration time on cached entries to bound staleness.

Use write‑through or write‑behind strategies to keep cache and DB in sync.

Personal Insights

Architecture Evolution

Evolution 1: Scheme 1 → Scheme 1 + sharding → Scheme 2 + sharding → Scheme 4 + sharding.

Evolution 2: Scheme 1 → Scheme 1 + sharding → Scheme 3 + sharding → Scheme 4 + sharding.

Evolution 3: Scheme 1 → Scheme 2 → Scheme 4 → Scheme 4 + sharding.

Evolution 4: Scheme 1 → Scheme 3 → Scheme 4 → Scheme 4 + sharding.

Key Takeaways

Adding cache and proper indexes is a universal way to improve MySQL performance.

Sharding (分库分表) yields massive scalability gains but introduces complexity; see the author’s detailed article for implementation details.

The chosen architecture must match the specific business scenario; a one‑size‑fits‑all design is rarely optimal.

Cloud providers (e.g., Alibaba Cloud) typically offer master‑standby services; achieving master‑slave read‑write separation often requires custom engineering.