MySQL High Availability: Concepts, Replication Architectures, and Design Considerations

High Availability (HA) is a crucial factor in distributed system architecture design, aiming to reduce service downtime through design.

Assuming a system always provides service, its availability is 100%. If it loses service for 1 out of every 100 time units, availability is 99%. Many companies target "four nines" (99.99%), meaning about 8.76 hours of annual downtime.

Baidu's search homepage is widely recognized for its excellent HA, often used as a benchmark for network connectivity.

1. MySQL High Availability

Replication is the foundation of MySQL HA, providing data backup, failover to a slave, disaster recovery, and load scaling (writes on master, reads on slaves).

Data backup

If the master fails, promote a slave to provide HA.

Geographic disaster recovery

Scale‑out: master handles writes, slaves handle reads.

1.1 Master‑Slave Replication Process

Different replication protocols:

1.2 High‑Availability Replication Architecture

1.3 MySQL High‑Availability Architectures

1.3.1 MySQL Cluster Architecture (NDB storage engine)

1.3.2 MySQL + MMM Architecture

MMM (Master‑Master Replication Manager) provides monitoring, failover, and management for MySQL master‑master replication, enabling a single writable node, read load balancing across slaves, virtual IP failover, backup, and resynchronization scripts.

Features: high safety and stability, good scalability, requires at least three servers, supports dual‑master setups, and enables read‑write separation.

1.3.3 MySQL + MHA Architecture

MHA, developed in Japan, offers fast automatic failover while preserving data consistency.

Features: simple deployment, automatic monitoring and failover, data consistency guarantee, manual or automatic multi‑direction failover, wide applicability to any storage engine.

2. Design Reflections from MySQL HA

MySQL introduced replication to ensure data consistency and provides redo and undo logs for ACID compliance. Redo logs are physical, used for forward recovery; undo logs are logical, used for rollback.

High‑availability designs rely on clustering or redundancy to eliminate single points of failure, with replication handling consistency.

Automatic failover and log‑based disaster recovery further enhance availability.

3. Summary

Single points of failure are the biggest risk to HA; redundancy through clustering mitigates this, but consistency remains a challenge that MySQL addresses via replication, logs, and automated failover mechanisms.

References: [1] http://uat.severalnines.com/blog/comparing-replication-solutions-oracle-and-mysql [2] https://mysqlhighavailability.com/mysql-group-replication-hello-world/ [3] https://www.cnblogs.com/youkanyouxiao/p/8335159.html [4] http://www.sohu.com/a/197271694_505827 [5] https://www.cnblogs.com/f-ck-need-u/archive/2018/05/08/9010872.html [6] https://www.cnblogs.com/youkanyouxiao/p/9834791.html