Building Scalable MySQL HA: From MHA to 7‑Layer Proxy and RDS

1. Introduction

The author began by building a distributed MySQL system called MyShard, designed for multi‑master high availability across many IDC data centers. As the business stabilized, the need for large‑scale distributed storage declined, but numerous small services required thousands of individual MySQL instances. Existing MySQL delivery and management were primitive, with long deployment cycles and manual failover handling.

2. Why Not Use Open‑Source HA Solutions

Popular open‑source MySQL HA solutions include MMM and MHA, with MHA being the preferred choice. MHA works by comparing slave I/O thread positions to select the latest slave, promoting it to master after applying binlog differences. It can preserve data when the master is reachable and works well with semi‑synchronous replication.

However, MHA has drawbacks: it requires SSH trust between nodes, which poses security risks; its scripts need further refinement; it lacks experience with large‑scale clusters; and its VIP‑based failover (e.g., using keepalive) is limited to a single network segment, making cross‑data‑center deployments difficult.

The company’s database characteristics—multi‑data‑center deployment, thousands of clusters, and strict security requirements—made MHA unsuitable.

3. Four‑Layer Proxy – RDS Project

Instead of MHA, the team explored proxy‑based HA. Alibaba and Tencent introduced RDS/CDB solutions; Tencent’s CDB uses a TGW (NAT) 4‑layer proxy but requires modifying the MySQL protocol, which is hard to adopt.

The team implemented an RDS project using iptables NAT to provide MySQL proxy routing. The basic workflow is that client connections hit the proxy, NAT rewrites the destination to the target MySQL host, and the response is NAT‑rewritten back to the client.

Example setup:

echo 1 > /proc/sys/net/ipv4/ip_forward

iptables -t nat -A PREROUTING -p tcp -s 172.26.14.16 --dport 3306 -j DNAT --to-destination 172.26.82.7:20000

iptables -t nat -A POSTROUTING -p tcp -d 172.26.82.7 --dport 20000 -j SNAT --to-source 172.26.82.45

mysql -utest -ptest -h172.26.82.45 -P20000

After these rules, connecting to port 20000 on the proxy actually accesses the MySQL instance at 172.26.82.7:3306.

4. Database Configuration Center – Seven‑Layer Proxy

To overcome the limitations of the 4‑layer proxy, the author developed a 7‑layer application‑level MySQL proxy platform. This proxy implements core functions such as:

Authorization and authentication model

SQL interception

Load balancing

Read/write splitting

High availability

Large‑SQL isolation

Additionally, the proxy is data‑center aware: each proxy instance and MySQL node carries a data‑center attribute, enabling automatic nearest‑node routing for better performance and simpler deployment.

The platform adopts a multi‑tenant design managed via Zookeeper; new business clusters are automatically discovered by the proxy without deploying separate instances, dramatically reducing operational overhead.

Benefits include transparent HA for clients, seamless MySQL instance migration, one‑click scaling, and the ability to run hundreds of MySQL clusters on the same proxy infrastructure.

5. Postscript

While MySQL offers many HA options, none are perfect. The proxy solution described has been successfully deployed at large scale within the company. Future work includes adding GTID‑based failover to leverage the maturity of MySQL 5.6/5.7 GTID features.