Killing the active writer in a three node Percona XtraDB Cluster cost zero failed writes. Killing the single ProxySQL instance sitting in front of it cost 1,282. Both numbers came off the same test bench, minutes apart, under the same write load. They are the reason this guide spends as much time on the routing tier as it does on the database cluster.
MySQL high availability is two tiers, and most guides only harden one of them. Percona XtraDB Cluster supplies the first: synchronous multi master replication built on Galera, where every node holds a full copy, every commit is certified across the cluster before it returns, and any node can accept a write. ProxySQL supplies the second. It decides which node actually gets each statement, tracks Galera state natively, and pulls failed members out of rotation without a health check script. This guide builds the whole stack on Ubuntu 24.04 LTS, then breaks it four different ways and measures what each failure costs. Every command and every number below was run on Percona XtraDB Cluster 8.4, with the package, repository and version details re-checked against Percona’s live indexes in August 2026.
The MySQL high availability architecture, and why the proxy tier decides it
Galera replicates synchronously. A transaction is sent to every node and certified against concurrent transactions before the commit returns to the client. That is what makes any node readable and consistent. It is also why writes should still land on one node at a time.
Multi master does not mean multi writer in practice. When two nodes commit conflicting changes to the same rows simultaneously, certification resolves it with first committer wins and the loser gets a deadlock error at commit time. Applications that were never written to retry those errors break in confusing ways. Routing every write to a single node removes the conflict window entirely, and costs nothing in availability because promotion of another node is automatic.
ProxySQL implements exactly that with four hostgroups. The writer hostgroup holds the one node receiving writes, the backup writer hostgroup holds candidates ready to be promoted, the reader hostgroup spreads SELECT traffic, and the offline hostgroup collects anything that is desynced, lagging or down.
There are two ways to place the proxy tier, and the choice matters more than most deployment guides admit. The first runs ProxySQL on every application host, with the application connecting to 127.0.0.1 on port 6033. There is no shared proxy to lose, no virtual IP to manage and no extra network hop. Configuration stays consistent because ProxySQL Cluster replicates it between instances automatically.
The second runs a dedicated pair of proxy hosts behind a Keepalived virtual IP. This is the shape used in the MariaDB Galera cluster build, and it is the right answer when the application runs somewhere you cannot install a proxy, such as a managed container platform or a fleet of short lived workers. It reintroduces a component that has to be made highly available on its own.
The measured difference between the two appears later in this guide. It is not subtle.
ProxySQL release tiers, and which one to actually install
ProxySQL now ships three parallel release lines, and picking by highest version number lands you on an experimental branch. This trips up more first time installs than any configuration mistake.
| Tier | Series | Intended for | Use in production |
|---|---|---|---|
| Stable | 3.0.x | Core hardening, protocol compatibility, essential bug fixes | Yes |
| Innovative | 3.1.x | Early adopters wanting new observability and performance work | Only with a reason |
| AI and MCP | 4.0.x | Experimental AI integrations and agentic workflows | No |
This build uses the Stable tier. The current Stable release also closes two pre authentication vulnerabilities that make upgrading non optional for anyone running an older build. CVE-2026-48772 carries a CVSS score of 10.0 and allows a spoofed PROXY protocol v1 header to forge a source address, bypassing any access control built on client_addr. CVE-2026-48773 scores 9.8 and is a heap overflow reachable during first packet handling, before authentication, on both the MySQL and PostgreSQL frontends. Both affect every 3.0.x release up to and including 3.0.8. The first reaches back to 2.0.0 and the second to 2.0.18.
If a ProxySQL instance older than 3.0.9 is reachable from anything you do not fully control, it is exposed to a pre authentication heap overflow. Patch it before reading further, and take 3.0.10 rather than 3.0.9, because it also closes a compressed packet advisory. The upstream release notes list the full advisory details.
Version and platform choices worth stating up front
Percona XtraDB Cluster 8.0 reached end of life on 1 April 2026. A final 8.0 release, 8.0.46-38, shipped after that cutoff on 23 July 2026 and reaches post-EOL support customers through Percona’s private repository, while community builds move to source only. Percona’s own release note for that build says July instead; the lifecycle matrix is the authoritative one. Any guide still targeting the 8.0 line is now describing an unsupported deployment, which is why the older Rocky Linux cluster walkthrough on this site needs the same treatment. The 8.4 LTS line is the current target and 9.7 LTS is the next major, as set out in the Percona release lifecycle policy.
Ubuntu 24.04 leads here for a specific reason. The 8.4 LTS line added Ubuntu 26.04 packages in its most recent release, but ProxySQL’s Stable tier does not yet publish a 26.04 build. Its Debian and Ubuntu assets stop at 24.04, alongside Ubuntu 22.04, Debian 12 and Debian 13. On the RHEL side the current Stable assets cover AlmaLinux 9 and 10, AlmaLinux 8 having been dropped, so Rocky Linux and AlmaLinux 10 run the full stack today.
The practical consequence is that a 26.04 deployment can run the database tier on native packages but has to either install the 24.04 proxy package and verify its dependencies resolve, or build ProxySQL from source. Everything below was run end to end on 24.04, where both tiers are packaged and supported.
Prerequisites
Three database nodes and at least one proxy location. Sizing follows the workload, not a fixed recipe.
RAM is driven by the working set. InnoDB wants its buffer pool at roughly 70 percent of a dedicated database host’s memory, so a 10 GB working set points at 16 GB or more per node rather than a number picked from a template. Disk is the dataset plus binary logs plus the Galera write set cache plus around 30 percent headroom, on storage that handles sync writes quickly, because every commit waits on certification and durability. A real OLTP workload commonly lands between 16 GB and 64 GB of RAM per node.
Two constraints are specific to synchronous replication. Cluster members must be sized identically, because the cluster commits at the pace of its slowest member and one undersized node throttles every write. Network latency between members is a direct input to commit time, so members belong on the same low latency segment rather than spread across regions.
The proxy tier is cheap. ProxySQL is a connection multiplexer, so its scaling knobs are file descriptor limits and max_connections rather than CPU or memory. A small instance handles thousands of client connections.
The cluster in this guide ran on 2 vCPU and 4 GB nodes with 30 GB disks, and proxy hosts at 2 vCPU and 2 GB. That is a floor for following along, not a production recommendation.
Schema requirements are enforced, not advisory. Every table needs an explicit primary key and must use InnoDB. The reason appears in Step 8.
These ports carry the stack:
| Port | Protocol | Purpose |
|---|---|---|
| 3306 | TCP | MySQL client traffic to the database nodes |
| 4567 | TCP and UDP | Galera replication and cluster membership |
| 4568 | TCP | Incremental state transfer |
| 4444 | TCP | State snapshot transfer |
| 6032 | TCP | ProxySQL admin interface and cluster config sync |
| 6033 | TCP | ProxySQL MySQL traffic, the port applications use |
| 6070 | TCP | ProxySQL REST API and Prometheus metrics |
Step 1: Set the cluster variables
Node addresses and credentials repeat across dozens of commands. Export them once per SSH session so only this block needs editing.
export PXC1="192.168.1.221"
export PXC2="192.168.1.222"
export PXC3="192.168.1.223"
export PROXY1="192.168.1.224"
export PROXY2="192.168.1.225"
export PROXY3="192.168.1.226"
export CLUSTER_NAME="pxc-cluster"
export MONITOR_USER="monitor"
export MONITOR_PASS="MonPass2026x"
export APP_USER="appuser"
export APP_PASS="AppPass2026x"
export APP_DB="shopdb"One rule about those passwords, learned the hard way on this build. Do not put a # character in any value that ends up inside a MySQL configuration file. The my.cnf parser strips everything from a # to end of line before quoting is applied, so the value silently truncates and the server refuses to start with an error naming only the fragment before the hash. Both double and single quoting were tested against this and neither helps.
Confirm the variables are populated before running anything destructive:
echo "nodes: ${PXC1} ${PXC2} ${PXC3}"
echo "cluster: ${CLUSTER_NAME}"
echo "app db: ${APP_DB} as ${APP_USER}"They live only in the current shell. Re run the block after reconnecting or after switching to a root shell with sudo -i.
Give every node name resolution for the others while the variables are loaded:
sudo vim /etc/hostsAppend the three cluster members:
192.168.1.221 pxc01
192.168.1.222 pxc02
192.168.1.223 pxc03Step 2: Add the Percona repositories
Percona ships a small helper package that manages which of its repositories are active. Install it first on all three database nodes.
sudo apt update
sudo apt install -y curl gnupg2 lsb-release
curl -fsSL -O https://repo.percona.com/apt/percona-release_latest.generic_all.deb
sudo apt install -y ./percona-release_latest.generic_all.debWith the helper in place, enable the 8.4 LTS repository and nothing else. The enable-only form disables every other Percona repository first, which prevents an 8.0 package being pulled in by accident:
sudo percona-release enable-only pxc-84-lts releaseThe helper reports what it switched off and on, then refreshes the package lists:
* Disabling all Percona Repositories
* Enabling the Percona Packaging Repository repository
Get:5 http://repo.percona.com/pxc-84-lts/apt noble InRelease [12.8 kB]
Get:9 http://repo.percona.com/pxc-84-lts/apt noble/main amd64 Packages [43.9 kB]State snapshot transfers use Percona XtraBackup, which lives in its own repository. Enable that one too, additively. Use the pxb-84-lts component rather than the older tools component, which still pins XtraBackup at 8.4.0-2 and would pair a stale backup tool with an 8.4.10 server:
sudo percona-release enable pxb-84-lts releaseBoth repositories should now be listed as enabled:
sudo percona-release showFour entries appear, of which pxc-84-lts and pxb-84-lts are the ones that matter:
The following repositories are enabled on your system:
prel - release
pxb-84-lts - release
pxc-84-lts - release
telemetry - releaseStep 3: Install Percona XtraDB Cluster on all three nodes
Check what the repository is offering before installing, so the version in the article and the version on the box are the same thing:
apt-cache policy percona-xtradb-cluster percona-xtrabackup-84 | grep -E '^[a-z]|Candidate'The candidate versions confirm the LTS line and the matching backup tool:
percona-xtradb-cluster:
Candidate: 1:8.4.10-10-1.noble
percona-xtrabackup-84:
Candidate: 8.4.0-6-1.nobleInstall the server and the backup tool together on each of the three nodes:
sudo apt install -y percona-xtradb-cluster percona-xtrabackup-84On a fresh install the package initialises the data directory and leaves the server stopped; on an upgrade it starts it. Stop it on every node before touching the configuration either way, because the first real start has to be a bootstrap:
sudo systemctl stop mysqlOn Debian and Ubuntu the packaged root account authenticates through the auth_socket plugin, so a local root shell reaches MySQL with no password. That is the account used for the administrative commands below.
Step 4: Configure the wsrep provider
Percona ships a working template rather than an empty file, and it lives in the main server configuration rather than a separate wsrep file. Open it on the first node:
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnfEdit these keys in place rather than appending a second copy, because the shipped template already defines wsrep_provider, wsrep_cluster_name, wsrep_sst_method, innodb_autoinc_lock_mode and pxc_strict_mode. Set the identity and membership values, and comment out the two 8.0 era lines the template still ships, binlog_format=ROW and wsrep_slave_threads=8, so they do not fight the settings below:
[mysqld]
server-id=1
wsrep_provider=/usr/lib/galera4/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.1.221,192.168.1.222,192.168.1.223
wsrep_cluster_name=pxc-cluster
wsrep_node_address=192.168.1.221
wsrep_node_name=pxc01
wsrep_applier_threads=8
wsrep_sst_method=xtrabackup-v2
innodb_autoinc_lock_mode=2
pxc_strict_mode=ENFORCINGRepeat on the other two nodes, changing only server-id, wsrep_node_address and wsrep_node_name. The cluster address stays identical everywhere because it lists all members, not peers.
Verify the provider path rather than trusting it, since it differs between packaging families:
dpkg -L percona-xtradb-cluster-server | grep -i galeraBoth a versioned directory and a top level symlink are present, and either path works:
/usr/lib/galera4
/usr/lib/galera4/libgalera_smm.so
/usr/lib/libgalera_smm.so
/usr/lib/mysql/plugin/component_test_table_access_galera.soInherited settings that no longer belong
Almost every Percona XtraDB Cluster tutorial in circulation instructs you to create an SST user and declare it with wsrep_sst_auth. That is 5.7 era advice: the variable was removed back in 8.0, and setting it on 8.4 stops the server dead:
2026-07-30T17:01:17.898080Z 0 [ERROR] [MY-000067] [Server] unknown variable 'wsrep_sst_auth=sstuser:SstLab2026x'.
2026-07-30T17:01:17.898812Z 0 [ERROR] [MY-010119] [Server] AbortingAsk the binary what it actually accepts and the removal is unambiguous:
mysqld --verbose --help | grep -oE '^\s*--wsrep-sst[a-z-]*' | sort -uFive options remain and an authentication option is not among them:
--wsrep-sst-allowed-methods
--wsrep-sst-donor
--wsrep-sst-donor-rejects-queries
--wsrep-sst-method
--wsrep-sst-receive-addressState transfers now authenticate through internal accounts that the server manages itself. You can see them in the user table after the first start, as mysql.pxc.sst.role and mysql.pxc.internal.session. No SST user to create, no credentials to rotate, and Percona’s own shipped template correctly omits the variable.
The second correction is smaller. The shipped template still uses wsrep_slave_threads and sets binlog_format=ROW, and 8.4 warns about both. ROW is already the default, and the thread setting has been renamed, which is why the configuration above uses wsrep_applier_threads and why both shipped lines get commented out. Leaving them in place reproduces the MY-011070 and MY-011068 warnings the configuration is meant to avoid, and sets the applier thread count twice.
Step 5: Distribute the TLS certificates before joining
This step is missing from a lot of walkthroughs and it is the single most common reason a second node refuses to join. Percona XtraDB Cluster encrypts Galera traffic by default, and each node generates its own self signed certificate set when its data directory is initialised, which on Debian and Ubuntu happens during installation. Two nodes with unrelated certificates cannot complete a handshake.
The failure is misleading. What surfaces at error level is a timeout, which sends people to check firewall rules on port 4567:
2026-07-30T17:06:12.000231Z 0 [ERROR] [MY-000000] [Galera] failed to open gcomm backend connection: 110: failed to reach primary view (pc.wait_prim_timeout)
2026-07-30T17:06:12.000512Z 0 [ERROR] [MY-000000] [WSREP] Provider/Node (gcomm://...) failed to establish connection with cluster (reason: 7)The actual cause sits above it, logged only at note level and repeating roughly every second and a half until the timeout expires:
2026-07-30T17:05:59.998467Z 0 [Note] [MY-000000] [Galera] Failed to establish connection: invalid padding: certificate signature failureA state transfer does not cause this and will not fix it, because the transfer script preserves .pem files. The problem is that every node trusts only the authority it generated for itself. Fix it by giving all three one shared set, staged outside the data directory so it is obvious which files are canonical. Stage them on the first node:
sudo mkdir -p /etc/mysql/certs
sudo cp /var/lib/mysql/ca.pem \
/var/lib/mysql/server-cert.pem /var/lib/mysql/server-key.pem \
/var/lib/mysql/client-cert.pem /var/lib/mysql/client-key.pem \
/etc/mysql/certs/
sudo chown -R mysql:mysql /etc/mysql/certs
sudo chmod 600 /etc/mysql/certs/*-key.pem
sudo chmod 644 /etc/mysql/certs/ca.pem /etc/mysql/certs/*-cert.pemca-key.pem is deliberately absent from that list. It is the certificate authority’s signing key, joiners never read it, and a full state transfer was tested with it missing. Keep it on the first node only. The copy below assumes root SSH between nodes, which stock Ubuntu permits by key only, so install the key first or run it as a normal user with sudo tar on the receiving side:
sudo tar -C /etc/mysql -cf - certs | ssh root@${PXC2} "tar -C /etc/mysql -xf - && chown -R mysql:mysql /etc/mysql/certs"
sudo tar -C /etc/mysql -cf - certs | ssh root@${PXC3} "tar -C /etc/mysql -xf - && chown -R mysql:mysql /etc/mysql/certs"Confirm all three now agree on the certificate authority:
md5sum /etc/mysql/certs/ca.pemThe same digest must appear on every node:
9d51b99a18dfb555c48ae1443596596f /etc/mysql/certs/ca.pemPoint both the server and the state transfer at those files. Create a dedicated fragment on each node so the change survives package updates to the main template:
sudo vim /etc/mysql/conf.d/pxc-encryption.cnfOnly the [mysqld] values decide anything here. While pxc_encrypt_cluster_traffic is on, which is the default, the transfer script re-reads the certificate paths from [mysqld] and overwrites whatever it parsed from [sst]. The [sst] block below is harmless and appears in most guides, so it stays for recognisability rather than effect:
[mysqld]
ssl-ca=/etc/mysql/certs/ca.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-key=/etc/mysql/certs/server-key.pem
[sst]
encrypt=4
ssl-ca=/etc/mysql/certs/ca.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-key=/etc/mysql/certs/server-key.pemStep 6: Open the firewall ports
Restrict the cluster ports to the cluster members rather than opening them broadly. On Ubuntu that means ufw, which ships inactive, so none of these rules take effect until it is enabled. Allow SSH first. Enabling ufw with only database rules in place applies the default deny policy to your own session and locks you out of the host, which is how one node in this series’ lab was lost during testing:
sudo ufw allow OpenSSH
for PEER in ${PXC1} ${PXC2} ${PXC3}; do
sudo ufw allow from ${PEER} to any port 3306 proto tcp
sudo ufw allow from ${PEER} to any port 4567
sudo ufw allow from ${PEER} to any port 4568 proto tcp
sudo ufw allow from ${PEER} to any port 4444 proto tcp
done
for PROXY in ${PROXY1} ${PROXY2} ${PROXY3}; do
sudo ufw allow from ${PROXY} to any port 3306 proto tcp
done
sudo ufw --force enable
sudo ufw status verbosePort 4567 needs both TCP and UDP, which is why that rule carries no protocol qualifier. The second loop is what lets the proxy tier reach the database nodes at all, and it is the rule most often missed until ProxySQL reports every backend as unreachable.
The proxy hosts need their own rules. Port 6032 carries admin traffic and configuration sync between ProxySQL instances, 6033 is the port applications connect to, and 6070 serves metrics to whatever scrapes them:
sudo ufw allow OpenSSH
for PROXY in ${PROXY1} ${PROXY2} ${PROXY3}; do
sudo ufw allow from ${PROXY} to any port 6032 proto tcp
done
sudo ufw allow from 192.168.1.0/24 to any port 6033 proto tcp
sudo ufw allow from 192.168.1.0/24 to any port 6070 proto tcp
sudo ufw --force enableNarrow the 6033 and 6070 sources to the application hosts and the monitoring host rather than a whole subnet once you know what they are.
On Rocky Linux and AlmaLinux the equivalent uses firewalld, where it is installed, since the cloud images often ship without it. SELinux stays enforcing. All three cluster ports already carry a label, two of them not mysqld’s, so relabel them:
for PEER in ${PXC1} ${PXC2} ${PXC3}; do
for P in 3306 4567 4568 4444; do
sudo firewall-cmd --permanent --add-rich-rule="rule family=ipv4 source address=${PEER} port port=${P} protocol=tcp accept"
done
sudo firewall-cmd --permanent --add-rich-rule="rule family=ipv4 source address=${PEER} port port=4567 protocol=udp accept"
done
for PROXY in ${PROXY1} ${PROXY2} ${PROXY3}; do
sudo firewall-cmd --permanent --add-rich-rule="rule family=ipv4 source address=${PROXY} port port=3306 protocol=tcp accept"
done
sudo firewall-cmd --reload
sudo semanage port -a -t mysqld_port_t -p tcp 4567 || sudo semanage port -m -t mysqld_port_t -p tcp 4567
sudo semanage port -a -t mysqld_port_t -p tcp 4568 || sudo semanage port -m -t mysqld_port_t -p tcp 4568
sudo semanage port -a -t mysqld_port_t -p tcp 4444 || sudo semanage port -m -t mysqld_port_t -p tcp 4444All three ports already carry a label before you touch them, 4444 as kerberos_port_t and 4567 as tram_port_t from the base policy, and 4568 added to mysqld_port_t by the Percona package’s own post install script, so semanage port -a prints Port tcp/NNNN already defined, modifying instead and exits cleanly. The -m fallback covers the cases where it does not. After the first state transfer completes, check for denials with sudo ausearch -m avc -ts today, because -ts recent only reaches back ten minutes and will look clean when it is not. Relabelling 4444 moves it from kerberos_port_t to mysqld_port_t in local policy, which is fine on a dedicated database node and worth knowing on a co-located one. The labelling is defensive rather than load bearing while the package leaves mysqld_t permissive, as covered in the RHEL section further down, and it becomes load bearing if you ever confine that domain again, which today also means shipping working policy modules yourself, because Percona’s do not load on el10.
Proxy hosts on this family need the same treatment for the ProxySQL ports:
for PROXY in ${PROXY1} ${PROXY2} ${PROXY3}; do
sudo firewall-cmd --permanent --add-rich-rule="rule family=ipv4 source address=${PROXY} port port=6032 protocol=tcp accept"
done
sudo firewall-cmd --permanent --add-rich-rule="rule family=ipv4 source address=192.168.1.0/24 port port=6033 protocol=tcp accept"
sudo firewall-cmd --permanent --add-rich-rule="rule family=ipv4 source address=192.168.1.0/24 port port=6070 protocol=tcp accept"
sudo firewall-cmd --reloadThose rules mirror the ufw set above. Note firewalld has to actually be running for any of this to apply, and its default zone already permits SSH, which is why there is no equivalent of the ufw allow OpenSSH line.
Step 7: Bootstrap the cluster and join the other nodes
The first node has no peers to sync from, so it starts through a dedicated systemd unit that tells Galera to form a new primary component. Run this on the first node only:
sudo systemctl start [email protected]Confirm the single member cluster came up before touching the others:
sudo mysql -u root -e "SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME IN ('wsrep_cluster_size','wsrep_cluster_status','wsrep_local_state_comment');"A size of one with primary status is the expected state at this point:
VARIABLE_NAME VARIABLE_VALUE
wsrep_cluster_size 1
wsrep_cluster_status Primary
wsrep_local_state_comment SyncedThe remaining nodes use the ordinary service unit. Start them one at a time and let each finish before the next:
sudo systemctl start mysqlEach joiner requests a state transfer, and the donor streams the dataset with XtraBackup. On this dataset the second node took 24 seconds end to end and the third took 30:
2026-07-30T17:09:09.821325Z 3 [System] [MY-000000] [WSREP] SST completed
2026-07-30T17:09:09.827120Z 0 [Note] [MY-000000] [Galera] 1.0 (pxc02): State transfer from 0.0 (pxc01) complete.
2026-07-30T17:09:09.827168Z 0 [Note] [MY-000000] [Galera] SST leaving flow control
2026-07-30T17:09:09.827781Z 0 [Note] [MY-000000] [Galera] Member 1.0 (pxc02) synced with group.Hand the first node back to the ordinary unit once the other two are synced. A server started through mysql@bootstrap has to be stopped through the same unit, and systemctl stop mysql against it is a silent no-op:
sudo systemctl stop mysql@bootstrap
sudo systemctl start mysqlLeave node 1 running under the bootstrap unit and the next restart of that unit tries to form a brand new cluster rather than rejoin the existing one. With peers up, a graceful stop writes safe_to_bootstrap: 0 into grastate.dat and Galera then refuses to start under that unit, so the usual outcome is a node that will not come back rather than a silent split. Either way it is an outage you created at handover time.
With all three running, every node should report the same membership.
A quick write on one node and a read on another proves replication rather than assuming it:
sudo mysql -u root -e "CREATE DATABASE ${APP_DB};"
sudo mysql -u root -e "CREATE TABLE ${APP_DB}.orders (id INT AUTO_INCREMENT PRIMARY KEY, item VARCHAR(50), created TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;"
sudo mysql -u root -e "INSERT INTO ${APP_DB}.orders (item) VALUES ('widget'),('sprocket');"Query the third node and both rows are already there:
sudo mysql -u root -e "SELECT COUNT(*) FROM shopdb.orders;"The count matches the write that was issued elsewhere:
COUNT(*)
2Step 8: Confirm pxc_strict_mode is doing its job
Strict mode is enabled by default and blocks the operations that break Galera replication. What surprises people is when it blocks them. Creating a table without a primary key succeeds, and so does creating a MyISAM table:
sudo mysql -u root -e "CREATE TABLE shopdb.nopk (id INT, note VARCHAR(20)) ENGINE=InnoDB;"
sudo mysql -u root -e "CREATE TABLE shopdb.legacy (id INT PRIMARY KEY, note VARCHAR(20)) ENGINE=MyISAM;"Both land in the schema without complaint. Percona’s current documentation describes strict mode forcing sql_require_primary_key on and rejecting the CREATE outright; the 8.4.10 build ships no such coupling, and the rejection still lands at DML time as shown below. Ask the schema what it now holds:
sudo mysql -u root -e "SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA='shopdb';"The primary-key-less table and the MyISAM table both exist:
TABLE_NAME ENGINE
legacy MyISAM
nopk InnoDB
orders InnoDBThe enforcement happens on the first write, which means a schema migration passes review and the application fails in production.
Three rejections are worth knowing by their exact text, because they are what appears in an application log rather than in a migration tool. A write to a table with no explicit primary key returns error 1105 naming the table. A write to a non transactional engine returns the same error code with a different reason. An explicit LOCK TABLES is refused outright.
Leave strict mode at ENFORCING. Relaxing it to PERMISSIVE converts those errors into warnings and lets a table drift into a state where rows silently diverge between nodes. Fix the schema instead.
Step 9: Install ProxySQL and wire up the Galera hostgroups
Add the Stable tier repository on the proxy hosts. It is a flat repository, so the trailing ./ matters:
curl -fsSL https://repo.proxysql.com/ProxySQL/repo_pub_key | sudo gpg --dearmor -o /usr/share/keyrings/proxysql-keyring.gpg
echo "deb [signed-by=/usr/share/keyrings/proxysql-keyring.gpg] https://repo.proxysql.com/ProxySQL/proxysql-3.0.x/noble/ ./" | sudo tee /etc/apt/sources.list.d/proxysql.list
sudo apt update
sudo apt install -y proxysql mysql-client
sudo systemctl enable --now proxysqlThe database nodes need two accounts. One lets ProxySQL poll cluster state, the other is what the application uses. Create both on any node and Galera replicates them:
sudo mysql -u root -e "CREATE USER '${MONITOR_USER}'@'%' IDENTIFIED BY '${MONITOR_PASS}';"
sudo mysql -u root -e "GRANT USAGE, REPLICATION CLIENT, PROCESS ON *.* TO '${MONITOR_USER}'@'%';"
sudo mysql -u root -e "CREATE USER '${APP_USER}'@'%' IDENTIFIED BY '${APP_PASS}';"
sudo mysql -u root -e "GRANT ALL PRIVILEGES ON ${APP_DB}.* TO '${APP_USER}'@'%';"ProxySQL is configured through a MySQL protocol admin interface on port 6032 rather than a config file. Connect to it:
mysql -u admin -padmin -h 127.0.0.1 -P 6032 --protocol=TCPAdd all three nodes to the writer hostgroup. Sorting them into writers and readers is ProxySQL’s job, not yours:
DELETE FROM mysql_servers;
INSERT INTO mysql_servers (hostgroup_id,hostname,port) VALUES
(10,'192.168.1.221',3306),
(10,'192.168.1.222',3306),
(10,'192.168.1.223',3306);The Galera hostgroup definition is where the routing policy actually lives:
DELETE FROM mysql_galera_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,2,100);Three of those values carry most of the behaviour. Setting max_writers to 1 is what enforces the single writer policy discussed earlier. Setting writer_is_also_reader to 2 keeps read traffic off the active writer while still using the backup writers as readers, so the writer spends its capacity on writes. And max_transactions_behind is the threshold at which a node whose apply queue is growing gets pulled from the reader pool automatically.
Tell the monitor which credentials to poll with, then activate everything:
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='MonPass2026x' WHERE variable_name='mysql-monitor_password';
LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;ProxySQL keeps three copies of its configuration. Changes land in an in memory table first, LOAD ... TO RUNTIME promotes them into the running instance, and SAVE ... TO DISK persists them across restarts. Skipping the save is how a working configuration disappears on the next reboot.
Give the monitor a few seconds to poll, then look at how the nodes were sorted.
Reading that table takes a moment of adjustment. One node is ONLINE in hostgroup 10 and is the active writer. The other two show as SHUNNED in hostgroup 10, which looks alarming but simply means they are not the current writer. Those same two are ONLINE in hostgroup 20 as promotion candidates and ONLINE in hostgroup 30 serving reads. The active writer is deliberately absent from the reader hostgroup, which is writer_is_also_reader=2 behaving as configured.
Step 10: Authenticate ProxySQL against MySQL 8.4
MySQL 8.4 defaults to caching_sha2_password and no longer loads the older plugin that many proxy setup guides still ask for. Confirm the state of the plugins rather than working around a problem that no longer exists:
sudo mysql -u root -e "SELECT PLUGIN_NAME, PLUGIN_STATUS FROM information_schema.PLUGINS WHERE PLUGIN_NAME LIKE '%password%';"The legacy plugin is present but switched off:
PLUGIN_NAME PLUGIN_STATUS
sha256_password ACTIVE
caching_sha2_password ACTIVE
mysql_native_password DISABLEDThe good news, verified on this build, is that none of that needs changing. The current ProxySQL Stable release authenticates to 8.4 backends using caching_sha2_password without complaint. Check the monitor’s own connection log to confirm it on your cluster:
SELECT hostname,port,connect_success_time_us,connect_error FROM mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 3;A NULL in the error column on every row is what you want to see:
+---------------+------+-------------------------+---------------+
| hostname | port | connect_success_time_us | connect_error |
+---------------+------+-------------------------+---------------+
| 192.168.1.221 | 3306 | 2081 | NULL |
| 192.168.1.223 | 3306 | 1795 | NULL |
| 192.168.1.222 | 3306 | 18871 | NULL |
+---------------+------+-------------------------+---------------+Do not enable mysql_native_password to make a proxy work. It is a deprecated plugin, and turning it on weakens authentication across the whole cluster to solve a problem the current proxy release does not have.
Register the application user with ProxySQL and set its default hostgroup to the writer:
INSERT INTO mysql_users (username,password,default_hostgroup,transaction_persistent,active)
VALUES ('appuser','AppPass2026x',10,1,1);
LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;Two ProxySQL defaults are worth correcting while here. The frontend still advertises the deprecated authentication plugin to clients, and the version string it reports is a placeholder from a much older MySQL release:
UPDATE global_variables SET variable_value='caching_sha2_password' WHERE variable_name='mysql-default_authentication_plugin';
UPDATE global_variables SET variable_value='8.4.10' WHERE variable_name='mysql-server_version';
LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;That second one is not cosmetic. A packaged install reports 5.5.30, because the shipped /etc/proxysql.cnf sets it explicitly, even though ProxySQL’s compiled-in default has been 8.0.11 since 2.6.0. Either way the number is wrong for this backend, and connectors and ORMs gate features on the reported server version, so an application can silently lose capabilities the 8.4 cluster supports.
Connect through the traffic port and confirm the whole path works:
mysql -u appuser -p -h 127.0.0.1 -P 6033 --protocol=TCP -e "SELECT @@hostname AS served_by, VERSION() AS reported_version;"The reply names the backend that served the query and its real server version, which comes from the backend itself rather than the handshake string set above:
served_by reported_version
pxc03 8.4.10-10.1Step 11: Split reads from writes with query rules
Query rules are evaluated in rule_id order, and the first match with apply set stops evaluation. That ordering is the whole design. The narrow rule has to come first:
DELETE FROM mysql_query_rules;
INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES
(100,1,'(?i)^SELECT.*(FOR UPDATE|FOR SHARE|LOCK IN SHARE MODE)',10,1),
(200,1,'(?i)^SELECT',30,1);
LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;Rule 100 catches locking reads and sends them to the writer, because a SELECT ... FOR UPDATE served by a reader takes a lock on the wrong node. The alternation matters and the trailing anchor most guides use does not survive contact with real queries: FOR UPDATE NOWAIT, SKIP LOCKED, FOR SHARE and the older LOCK IN SHARE MODE all take locks too, and an end-anchored pattern lets every one of them fall through to the reader pool silently. Rule 200 then sweeps every remaining SELECT to the reader pool. Everything that matches neither rule falls through to the user’s default hostgroup, which is the writer, so INSERT, UPDATE, DELETE and DDL need no rules of their own.
The (?i) prefix makes both patterns case insensitive. Applications and ORMs are inconsistent about statement casing, and a case sensitive rule quietly sends lowercase queries to the writer.
Verify with real traffic rather than by reading the rules back. Driving ten plain reads, eight locking reads and three inserts through the proxy produces a routing table that can be checked line by line.
Every plain SELECT landed in hostgroup 30 and every write in hostgroup 10, with rule hit counts of 10 and 8 matching the traffic exactly. The locking reads went to the writer whether or not they were wrapped in an explicit transaction.
One line in that output confuses almost everyone the first time. The select @@version_comment limit ? entry shows 21 executions against the writer hostgroup, which looks like reads leaking. It is the handshake query the MySQL command line client issues on every connection, and ProxySQL serves it on the user’s default hostgroup without evaluating query rules. The rule hit counters prove it never touched rule 200. Filter that digest out before judging a split.
A second measurement quirk is worth knowing before it wastes an afternoon. The stats_mysql_query_rules table is not real time. Reading it immediately after a burst of traffic can return zeros for a rule that is firing correctly. Allow ten to fifteen seconds before trusting the counters, and restart the service when a genuinely clean baseline is needed, because the reset tables did not reliably zero the counters here.
Connection pool counters give the other half of the picture:
SELECT hostgroup,srv_host,status,Queries FROM stats_mysql_connection_pool ORDER BY hostgroup,srv_host;Reads spread across both reader nodes while writes concentrated on one. This is an excerpt; the full result carries seven rows, including the shunned hostgroup 10 entries covered earlier:
+-----------+---------------+---------+---------+
| hostgroup | srv_host | status | Queries |
+-----------+---------------+---------+---------+
| 10 | 192.168.1.223 | ONLINE | 19 |
| 30 | 192.168.1.221 | ONLINE | 7 |
| 30 | 192.168.1.222 | ONLINE | 3 |
+-----------+---------------+---------+---------+One property of the split deserves naming before you point an application at it. Galera certifies every commit cluster-wide before it returns, but applying the write set on the other nodes happens after that, so a SELECT routed to a reader immediately after a commit can miss it. Set wsrep_sync_wait=1 on sessions that read their own writes, which makes the reader block until it has caught up, at the cost of some latency on those queries.
Step 12: Remove the proxy as a single point of failure
A three node database cluster behind one proxy is not a highly available system. The numbers in the next section make that concrete. The fix is ProxySQL Cluster, which replicates configuration between instances so running several of them costs no extra maintenance.
Every participating instance needs matching cluster credentials and the same peer list. Apply this on each ProxySQL admin interface, including the ones running on application hosts:
UPDATE global_variables SET variable_value='admin:admin;cluster1:clusterpass' WHERE variable_name='admin-admin_credentials';
UPDATE global_variables SET variable_value='cluster1' WHERE variable_name='admin-cluster_username';
UPDATE global_variables SET variable_value='clusterpass' WHERE variable_name='admin-cluster_password';
DELETE FROM proxysql_servers;
INSERT INTO proxysql_servers (hostname,port,weight,comment) VALUES
('192.168.1.224',6032,1,'px01'),
('192.168.1.225',6032,1,'px02'),
('192.168.1.226',6032,1,'app01-local');
LOAD ADMIN VARIABLES TO RUNTIME; SAVE ADMIN VARIABLES TO DISK;
LOAD PROXYSQL SERVERS TO RUNTIME; SAVE PROXYSQL SERVERS TO DISK;The cluster user has to exist inside admin-admin_credentials as well as being named as the cluster username, which is why the first line lists two accounts. Change clusterpass before this touches anything real. The literal admin account is loopback only whatever its password, but the cluster account authenticates across the network and the shipped configuration binds the admin interface to 0.0.0.0:6032, so restrict that port to the peer addresses as well.
New instances will now sit there doing nothing, and the reason is buried in the ProxySQL log. A peer whose configuration version is still 1 is not considered a valid sync source:
[WARNING] Cluster: detected a peer 192.168.1.224:6032 with mysql_servers version 1, epoch 1785433290, diff_check 30. Own version: 1, epoch: 1785433401. diff_check is increasing, but version 1 doesn't allow sync. This message will be repeated every 30 checks until LOAD MYSQL SERVERS TO RUNTIME is executed on candidate master.That version counter only increments on a runtime load. Run one more on whichever node should be the source of truth and it moves to version 2:
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
LOAD MYSQL VARIABLES TO RUNTIME;Within seconds the other instances pull the full configuration. Checking an application host that was never configured by hand shows the backends, the query rules and the users all present. From here the application connects to 127.0.0.1:6033 and the shared proxy disappears from the failure path entirely.
Where a local proxy is impossible, run a pair of proxy hosts behind a Keepalived virtual IP instead, with ip_nonlocal_bind enabled and a vrrp_script that checks port 6033 rather than just the process. The Keepalived and virtual IP setup from the MariaDB build applies unchanged, since the VRRP layer does not care which proxy it is fronting.
Step 13: Measure the failover
Everything above is a claim until it is broken on purpose. Each of the following ran under a continuous single threaded write probe through ProxySQL, one INSERT per iteration on a fresh connection, with a median latency of 58 ms in steady state.
Killing the mysqld process on the active writer
A pkill -9 mysqld on the writer produced 1,051 write attempts and zero failures. The worst single write took 6,168 ms, which was the transaction already in flight when the process died.
The reason it healed itself is that the packaged unit restarts on abnormal exit, and the node came back through an incremental state transfer rather than a full re copy:
2026-07-30T17:25:15.480857Z 2 [Note] [MY-000000] [Galera] IST received: c3af059c-8c38-11f1-b92f-1e7a21fc095e:502
2026-07-30T17:25:15.486193Z 0 [Note] [MY-000000] [Galera] 2.0 (pxc03): State transfer from 1.0 (pxc02) complete.
2026-07-30T17:25:15.487034Z 0 [Note] [MY-000000] [Galera] Shifting JOINED -> SYNCED (TO: 564)A crashed database process on a PXC node is therefore self healing, and testing only this scenario gives a misleadingly good impression of the cluster.
Losing the writer node entirely
Hard stopping the virtual machine removes systemd from the equation. Across 1,050 attempts there were again zero failed writes, but exactly one write took 15,800 ms.
That number is not arbitrary. It is Galera’s membership timeout, visible in the configuration the provider prints at startup as evs.inactive_timeout = PT15S, and the survivors log the moment it expires:
2026-07-30T17:28:16.958729Z 0 [Note] [MY-000000] [Galera] declaring node with index 2 inactive (evs.inactive_timeout)
2026-07-30T17:28:16.966044Z 0 [Note] [MY-000000] [Galera] New COMPONENT: primary = yes, bootstrap = no, my_idx = 0, memb_num = 2ProxySQL promoted a backup writer without intervention. The practical lesson is about client configuration rather than cluster configuration. ProxySQL never returned an error, but an application with a query timeout below 15 seconds would have raised one anyway. Size client timeouts against the membership timeout, or lower evs.inactive_timeout knowing that aggressive values cause false evictions on a busy network.
Bringing the lost node back
The rebooted node did not rejoin on its own, and that is deliberate:
WARNING: Node has been rebooted, /var/lib/mysql/grastate.dat: seqno = -1, mysql service has not been started automaticallyA seqno of -1 means the node did not shut down cleanly and cannot prove where it stopped in the cluster history, so it refuses to rejoin unattended. Do not resolve this by setting safe_to_bootstrap: 1. That declares a stale node the source of truth while the survivors are still taking writes, which is the textbook way to manufacture a split brain.
With the rest of the cluster healthy, the correct action is simply to start the service. One catch: the packaged unit runs a check-grastate pre-start step that refuses to start a node whose seqno is -1 within five minutes of boot, printing that same explanation as a start failure. Wait out the window or start it again after five minutes. Recovery took 9 seconds and used an incremental transfer:
2026-07-30T17:39:01.081737Z 0 [Note] [MY-000000] [Galera] Member 2.0 (pxc03) requested state transfer from '*any*'. Selected 1.0 (pxc02)(SYNCED) as donor.
2026-07-30T17:39:02.762260Z 0 [Note] [MY-000000] [WSREP-SST] xtrabackup_ist received from donor: Running ISTIncremental transfer works only while the writes missed during the outage still fit in the donor’s write set cache, which defaults to 128 MB. Size gcache.size against write rate multiplied by worst case node downtime, otherwise every restart becomes a full dataset copy.
Taking a node out for maintenance
Desync is the clean way to drain a node without removing it from the cluster:
sudo mysql -u root -e "SET GLOBAL wsrep_desync=ON;"Within about twelve seconds ProxySQL moved that node out of every serving hostgroup and into the offline group, with its Galera monitor recording wsrep_desync as YES and a local state of 2. Turning it back off returned the node to the reader and backup writer groups just as quickly. No configuration edits on either tier.
Killing the proxy instead of the database
The same probe, pointed at a single remote ProxySQL, with the proxy stopped for 19 seconds. The result is the reason Step 12 exists.
| Failure injected | Attempts | Failed writes | Worst single write | Client visible outage |
|---|---|---|---|---|
| mysqld killed on the writer | 1,051 | 0 | 6,168 ms | none |
| Writer node hard stopped | 1,050 | 0 | 15,800 ms | none, one long stall |
| Single remote ProxySQL stopped | 1,807 | 1,282 | n/a | 17,403 ms |
| Remote ProxySQL stopped, local proxy in use | 714 | 0 | 111 ms | none |
Losing the proxy produced 1,282 hard connection refusals:
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.224:6033' (111)Repeating that identical failure with ProxySQL running on the application host changed the outcome completely. Of 714 write attempts, 517 happened while the remote proxy was down, and none of them failed. Median latency was 56 ms against 58 ms through the remote proxy, so the local instance cost nothing measurable.
Three nodes of synchronous replication protect the data tier well enough that two separate ways of destroying the writer produced no application errors at all. A single proxy in front of them produced 1,282. Whatever effort goes into the database cluster, the routing tier deserves the same.
Package and path differences on the RHEL family
Every measurement above came off Ubuntu 24.04 hosts. The same stack was then built a second time on Rocky Linux 10.1 with SELinux left enforcing, and enough details move that copying commands across without checking causes avoidable failures. Several of them are not in any install guide.
| Item | Ubuntu 24.04 | Rocky Linux 10 and AlmaLinux 10 |
|---|---|---|
| Repository helper | percona-release_latest.generic_all.deb | percona-release-latest.noarch.rpm |
| Enable the repository | percona-release enable-only pxc-84-lts release | percona-release setup pxc-84-lts |
| XtraBackup component | percona-release enable pxb-84-lts release | percona-release enable pxb-84-lts release |
| Server package | percona-xtradb-cluster | percona-xtradb-cluster |
| Main config file | /etc/mysql/mysql.conf.d/mysqld.cnf | /etc/my.cnf (drop-ins are NOT read) |
| Galera provider | /usr/lib/galera4/libgalera_smm.so | /usr/lib64/galera4/libgalera_smm.so |
| Socket and error log | /var/run/mysqld/mysqld.sock, /var/log/mysql/error.log | /var/lib/mysql/mysql.sock, /var/log/mysqld.log |
| Root authentication | auth_socket, no password locally | Temporary password in /var/log/mysqld.log, must be changed first |
| Firewall tool | ufw | firewall-cmd, if firewalld is installed |
| Mandatory access control | AppArmor, no action needed | SELinux reports enforcing, but the package leaves mysqld permissive; label the ports anyway |
| ProxySQL package | proxysql_3.0.10-ubuntu24_amd64.deb | proxysql-3.0.10-1-almalinux10.x86_64.rpm |
The provider path is the one to verify rather than assume, since the Debian layout and the RHEL layout do not match. Ask the package manager instead of guessing:
rpm -ql percona-xtradb-cluster-server | grep -i galeraThe one that costs the most time is silent. Percona’s /etc/my.cnf on RHEL carries no !includedir line at all, so /etc/my.cnf.d/ is never read. Dropping a pxc-encryption.cnf in there the way Step 5 does on Ubuntu produces no error and no effect, the TLS settings are discarded, and the joiners then fail with the certificate handshake error from Step 5 for a reason that looks nothing like a config problem. On RHEL the wsrep and [sst] sections go directly into /etc/my.cnf.
The second is the backup tool, and the answer is the same on both families. The legacy tools repository has no XtraBackup build for el10 at all, and on Ubuntu it carries only 8.4.0-2, so a reader who enables it there gets a backup tool four releases behind the current XtraBackup. pxb-84-lts supplies percona-xtrabackup-84 at 8.4.0-6 for both. Enable the wrong one on el10 and the SST script is installed while the binary it calls is missing.
Certificate timing is the next trap and it is specific to the RPM path. The Debian and Ubuntu package initialises the data directory during installation, so the .pem files Step 5 copies are already sitting in /var/lib/mysql. The RPM does not initialise anything, so that directory is empty until the first server start and the copy fails with cannot stat '/var/lib/mysql/ca.pem'. Adding the ssl- lines before the files exist is worse, because mysqld then refuses to start at all:
[ERROR] [Galera] Bad value '/etc/mysql/certs/server-cert.pem' for SSL parameter 'socket.ssl_cert': No such file or directory
[ERROR] [WSREP] Failed to load provider
[ERROR] [MY-010119] [Server] AbortingOn el10 the order is therefore: write the base configuration with no ssl- lines, bootstrap the first node so the certificates get generated, then stage and distribute them, add the ssl- lines to all three, and restart node 1 with systemctl restart mysql@bootstrap so it picks them up before the joiners start. The same applies to any node whose data directory has been wiped to retry a failed join.
Root also does not use socket authentication. The first login needs the temporary password from /var/log/mysqld.log, and any statement other than ALTER USER is refused with ERROR 1820 until the password is changed.
Also ignore any instruction to run dnf module disable mysql. Rocky 10 has no MySQL module, Red Hat deprecated modularity in RHEL 10 and ships no modular content, and dnf module list mysql answers with No matching Modules to list. That step belongs to RHEL 8 and 9.
SELinux does need handling on el10, just not the kind most guides describe. The Percona RPM runs semanage permissive -a mysqld_t in its post install scriptlet, so getenforce reports Enforcing while mysqld itself runs unconfined. That exception is load bearing. Remove it and the bootstrap node still starts, which is misleading, but the first joiner dies during the state transfer with posix_spawnp() failed: 13 and then mysqld got signal 11. The reason is that Percona’s own policy modules are built at policydb version 24 while Rocky 10 reads up to 23, so the semodule call in the scriptlet fails silently and neither module ever loads. A permissive domain still logs its denials, tagged permissive=1, so a quiet audit log proves nothing on its own. Disabling SELinux outright to get past a state transfer failure trades a five minute fix for a permanently weaker host.
Everything else transfers directly. The wsrep configuration keys, the certificate contents themselves, the removal of wsrep_sst_auth, the ProxySQL hostgroup definitions and every query rule behave identically, because they are properties of the 8.4 line and the proxy rather than of the distribution. Only the ordering and the file locations change. The existing Rocky Linux cluster walkthrough covers the RPM path in more detail, though it predates the 8.4 changes described here and should be read alongside them.
For a single node rather than a cluster, the Percona Server install on Ubuntu and Debian is the simpler starting point, and a lighter weight variation of this routing setup exists in the MariaDB Galera cluster with ProxySQL build for teams already standardised on MariaDB.
What to watch in the metrics
ProxySQL exposes Prometheus metrics on port 6070, and the database nodes expose the wsrep status variables. A handful of them predict trouble early enough to act.
The REST API is disabled by default, so 6070 refuses connections until it is switched on from the admin interface:
UPDATE global_variables SET variable_value='true' WHERE variable_name='admin-restapi_enabled';
LOAD ADMIN VARIABLES TO RUNTIME;
SAVE ADMIN VARIABLES TO DISK;Opening the port without this is the usual reason a scrape target sits permanently down.
| Metric | Where | What a bad value means |
|---|---|---|
wsrep_cluster_size | Each node | Below the expected count, a member is gone. Alert immediately. |
wsrep_cluster_status | Each node | Anything but Primary means this partition cannot take writes. |
wsrep_local_state_comment | Each node | Not Synced means the node is joining, donating or desynced. |
wsrep_local_recv_queue_avg | Each node | Rising above zero means this node is applying slower than the cluster writes. |
wsrep_flow_control_paused | Each node | Above zero means a slow node is throttling every writer. |
wsrep_local_cert_failures | Each node | Climbing steadily means write conflicts, usually more than one writer. |
| Offline hostgroup membership | ProxySQL | Anything sitting in hostgroup 40 is not serving traffic. |
connect_error in the connect log | ProxySQL | Non NULL values are authentication or reachability problems. |
Two of those deserve alerts rather than dashboards. A wsrep_cluster_size below the expected count means redundancy is already gone and the next failure could cost quorum. A non zero wsrep_flow_control_paused means one node is dictating the write throughput of the whole cluster, which is the failure mode that looks like a mysterious application slowdown rather than an outage.
Certification failures are the metric that tells you the routing tier has drifted. On a correctly configured single writer setup they stay near zero. A steady climb means writes are reaching more than one node, which points at an application bypassing the proxy or a query rule sending traffic somewhere it should not. For a full metrics pipeline, the existing Prometheus and Grafana monitoring setup covers exporter configuration and dashboards, and pairs with the Patroni based PostgreSQL cluster if you run both engines and want one alerting standard across them.
