PhxSQL: Delivering Zookeeper‑Level Strong Consistency & High Availability for MySQL

Abstract

PhxSQL is a MySQL‑compatible cluster that guarantees Zookeeper‑level strong (linearizable) consistency and high availability by leveraging Paxos. It provides two service ports—ReadWritePort for strongly consistent reads/writes and ReadonlyPort for read‑only access—while keeping deployment and operations simple.

1. What is PhxSQL?

PhxSQL builds a MySQL cluster on top of Paxos, intercepting MySQL binlog streams, aggregating them into a global binlog, and replicating this stream to read‑only replicas. As long as a majority of nodes are up, the cluster remains operational.

2. Consistency and Availability Level

PhxSQL offers the same linearizable consistency and majority‑based availability as Zookeeper: any client sees the latest writes once a majority of nodes are alive.

3. Why Build PhxSQL?

Traditional MySQL primary‑standby setups provide full SQL support and strong transaction isolation but lack automatic failover and strong consistency guarantees. PhxSQL addresses these gaps without sacrificing MySQL compatibility, avoiding costly migrations to non‑compatible NewSQL systems.

4. Design Principles

4.1 Simple, Logically Proven Consistency Model

PhxSQL relies on two premises: Paxos ensures consensus, and synchronized MySQL binlog streams guarantee data equality across nodes. The system’s correctness is verified through extensive testing, including random restarts and network perturbations.

4.2 Minimal MySQL Intrusion

Only three tiny modifications are made to MySQL: a binlog plugin argument change, a startup plugin for binlog checks, and passing the client IP through the protocol. This keeps PhxSQL compatible with upstream MySQL releases.

4.3 Simple Architecture, Deployment, and Operations

PhxSQL consists of three core modules plus an optional client library: MySQL, PhxBinlogSvr (global binlog storage and Paxos), PhxSQLProxy (transparent client proxy), and PhxSQL client lib for automatic proxy failover. Deployment is a matter of installing the binaries and listing node IPs; failover and rolling upgrades are handled automatically.

5. Why Open‑Source?

The authors believe open‑source drives technological progress and want the community to benefit from and contribute to PhxSQL.

6. Limitations

Two main limitations are acknowledged: (1) DDL statements can lose their binlog if the primary crashes after InnoDB commit but before interception, and (2) under heavy write loads, replica lag may delay failover, causing temporary write unavailability. Future work includes DDL audit checks and support for MySQL 5.7 parallel replication.

References

Key papers on linearizability, Paxos, Raft, ZAB, Viewstamped Replication, and related systems are listed.