MySQL High Availability Architectures: Overview of Common Solutions

High availability (HA) is a standard requirement for Internet services, and MySQL databases need HA solutions that ensure both service continuity and data consistency. This article summarizes several typical MySQL HA architectures, describing their features, pros, and cons.

1. Based on Shared Storage (SAN)

Shared Storage Area Network (SAN) allows multiple servers to access the same file system; if the primary server fails, a standby can mount the same storage and start MySQL. Advantages: avoids data loss from external components, simple deployment, strong consistency between primary and standby. Limitations: shared storage is a single point of failure and costly.

2. Based on Disk Replication (DRBD)

DRBD (Distributed Replicated Block Device) replicates block devices over the network, providing a hot‑standby similar to SAN but without shared storage. Advantages: transparent failover, strong consistency. Limitations: write performance impact, limited scalability (typically two‑node sync), standby cannot serve reads.

3. Primary‑Slave Replication Based Solutions

These solutions rely on MySQL’s own replication to create one or more hot replicas that can take over when the master fails.

3.1 keepalived / heartbeat

keepalived uses VRRP to monitor server health and manage a virtual IP (VIP). When the master fails, a slave takes over the VIP, providing transparent failover. Advantages: simple installation, fast transparent switch. Limitations: requires same subnet for IPs, weak health checks, cannot guarantee data consistency (MySQL async replication), HA software itself is a single point of failure.

3.2 MHA (Master High Availability)

MHA, written in Perl, monitors master nodes and automatically promotes the most up‑to‑date slave after synchronizing binary logs. Advantages: open source, can reconcile slave differences, flexible VIP or global directory switch. Limitations: does not guarantee strong consistency, supports only one‑master‑multiple‑slaves (minimum three servers), requires application awareness for some switch methods, complex configuration, HA manager itself is not HA.

3.3 ZooKeeper‑based HA

By registering MySQL nodes in ZooKeeper, HA clients can detect failures and coordinate failover using distributed consensus (e.g., Paxos/Raft). This eliminates the single point of failure of the HA software itself. Advantages: system‑wide HA, strong consistency depends on MySQL (semi‑sync or replay), excellent scalability. Limitations: added complexity due to ZooKeeper.

4. Cluster (Multi‑Write) Solutions

True multi‑write clusters such as Percona XtraDB Cluster (PXC) based on Galera and MySQL NDB Cluster provide synchronous replication, allowing multiple nodes to read and write simultaneously.

PXC (Galera)

Uses InnoDB engine with optimistic concurrency control. Advantages: quasi‑synchronous replication, multi‑node read/write, automatic node management, strict data consistency, high availability. Limitations: only InnoDB, all tables need primary keys, write amplification, high network dependency.

5. Middleware Proxy Solutions

Middleware (e.g., MySQL‑proxy, Fabric, Cobar, TDDL) sits between applications and databases, providing transparent failover, sharding, and HA. Example with Fabric shown below. Advantages: transparent switch, strong scalability, cross‑datacenter deployment. Limitations: relatively new component, does not solve strong consistency (relies on MySQL semi‑sync), configuration complexity.

Summary

The article presented the main MySQL HA architectures: shared‑storage SAN, DRBD disk replication, primary‑slave based solutions (keepalived, MHA, ZooKeeper), true clustering (Galera/PXC), and middleware proxy approaches. Each was evaluated for availability, data consistency, and application transparency, concluding that replication‑based solutions are the most mature, while ZooKeeper or middleware can extend scalability at the cost of added complexity.