Building a highly available MySQL layer with PXC, ProxySQL and Keepalived

A production billing database had reached the point where a classic primary/replica layout was becoming an operational risk. The goal was not simply to add more MySQL servers. We wanted a database entry point that could survive the loss of a database node or a proxy without changing application configuration.

The final path looked like this:

Application servers
        |
        v
172.16.23.200:6033  (Keepalived VIP)
        |
        v
ProxySQL x3
 .207   .208   .209
        |
        v
Percona XtraDB Cluster x3
 .207   .208   .209

All application servers connect to one address: 172.16.23.200:6033. Keepalived decides which ProxySQL instance owns the VIP. ProxySQL decides which PXC node receives each query.

Why three layers?

PXC solves database-node availability, but applications still need a stable endpoint and sane query routing. ProxySQL provides that routing and understands Galera state. Keepalived removes the proxy itself as a single point of failure by moving the VIP between ProxySQL nodes.

We deliberately kept one writer. Ordinary reads can be distributed across the cluster, while writes and locking reads go to the writer.

ProxySQL hostgroups

The Galera topology was defined with four hostgroups:

INSERT INTO mysql_galera_hostgroups
(
    writer_hostgroup,
    backup_writer_hostgroup,
    reader_hostgroup,
    offline_hostgroup,
    active,
    max_writers,
    writer_is_also_reader,
    max_transactions_behind
)
VALUES (10, 20, 30, 40, 1, 1, 0, 100);

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

The important parts are:

HG10  writer
HG20  backup writers
HG30  readers
HG40  offline

max_writers = 1
writer_is_also_reader = 0

Node .209 was given the highest writer weight, so under normal conditions it becomes the active writer. The other nodes remain available to take over.

A simplified server configuration looked like this:

INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight)
VALUES
(10, '172.16.23.209', 3306, 1000),
(10, '172.16.23.208', 3306, 1),
(10, '172.16.23.207', 3306, 1),
(30, '172.16.23.207', 3306, 100),
(30, '172.16.23.208', 3306, 100),
(30, '172.16.23.209', 3306, 100);

In production the Galera monitor moves nodes between the writer, backup-writer, reader and offline hostgroups according to their actual cluster state.

Routing SELECT and locking queries

The routing rules were intentionally simple.

A SELECT ... FOR UPDATE must reach the writer:

INSERT INTO mysql_query_rules
(rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES
(10, 1, '^SELECT.*FOR UPDATE', 10, 1);

Ordinary SELECTs go to the reader hostgroup:

INSERT INTO mysql_query_rules
(rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES
(20, 1, '^SELECT', 30, 1);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

The order matters: the FOR UPDATE rule is evaluated before the generic SELECT rule.

INSERT/UPDATE/DELETE use the user’s default writer hostgroup, so we do not need a regex for every write statement.

Did SELECT balancing actually work?

Configuration is not proof. After switching traffic we tested the route itself.

For a simple controlled sample of 100 SELECT requests, the distribution was:

172.16.23.207   30
172.16.23.208   36
172.16.23.209   34
------------------
TOTAL          100

That is what we wanted: reads were not pinned to one database node.

ProxySQL’s own statistics can be checked from the admin interface:

SELECT hostgroup, srv_host, srv_port, status,
       ConnUsed, ConnFree, Queries
FROM stats_mysql_connection_pool
ORDER BY hostgroup, srv_host;

And query-rule counters:

SELECT rule_id, hits
FROM stats_mysql_query_rules
ORDER BY rule_id;

These counters are much more useful than assuming that a rule works because it exists in the configuration.

What about UPDATE / FOR UPDATE?

We also tested a locking read repeatedly:

SELECT @@server_id FOR UPDATE;

100 requests produced:

172.16.23.207    0
172.16.23.208    0
172.16.23.209  100
------------------
TOTAL          100

So ordinary SELECT traffic was balanced, while the locking path consistently reached the active writer.

For real UPDATE traffic the same principle applies: it stays on the writer hostgroup rather than being distributed across the readers.

Keepalived in front of ProxySQL

Running ProxySQL on three nodes still leaves a question: which proxy address should hundreds of application servers use?

The answer was a floating VIP:

172.16.23.200

A shortened Keepalived configuration on the preferred node looked like this:

vrrp_instance VI_MYSQL {
    state BACKUP
    interface ens256
    virtual_router_id 51
    priority 120
    nopreempt

    virtual_ipaddress {
        172.16.23.200/24
    }
}

The other nodes use lower priorities. In our preferred order the VIP moves:

.209 -> .208 -> .207

nopreempt was intentional. After a failure we did not want the VIP unnecessarily jumping back simply because a higher-priority node returned. Stability is often more valuable than immediately restoring the original owner.

In the real configuration ProxySQL availability is also checked so a node that cannot serve MySQL traffic should not keep the service VIP.

The result

The application side became simple:

mysql://172.16.23.200:6033

Behind that one endpoint we now had:

Keepalived     -> proxy failover
ProxySQL       -> query routing + statistics
PXC            -> database HA
HG10           -> one writer
HG30           -> balanced reads

The important part was not installing three products. It was testing the complete failure and routing path: VIP ownership, proxy health, Galera state, writer selection, SELECT distribution and locking/write routing.

A high-availability diagram is easy to draw. Production HA starts when you can remove a component and the application keeps working.