Operating System Debian

How to Set Up a Percona XtraDB Cluster 8.4 on Debian 13

Install Percona XtraDB Cluster 8.4 on three Debian 13 servers, configure Galera replication with encrypted traffic, bootstrap the cluster and verify that writes replicate.

Percona XtraDB Cluster (PXC) is a MySQL-compatible database with synchronous multi-primary replication built on Galera: every node accepts writes and all nodes hold the same data. By the end of this guide you will have a three-node cluster running PXC 8.4 on Debian 13 "trixie". The same steps apply to Debian 12 "bookworm"; Percona publishes packages for both. Debian 7, which older tutorials used together with PXC 5.6, is long past end of life.

Requirements

  • Three servers running Debian 13 (or Debian 12), each with a user that can run sudo. Use an odd number of nodes: a two-node cluster cannot keep a majority when one node or the link between them fails, so it stops accepting writes.
  • Network connectivity between the nodes, preferably over a private network. The examples use these names and addresses:
NodeNameAddress
1pxc1198.51.100.11
2pxc2198.51.100.12
3pxc3198.51.100.13
  • TCP ports 3306 (clients), 4444 (state snapshot transfer), 4567 (group communication) and 4568 (incremental state transfer) open between the nodes. Do not expose them to the internet. If you use ufw, this rule on each node allows the cluster subnet:
sudo ufw allow from 198.51.100.0/24 to any port 3306,4444,4567,4568 proto tcp
  • No other MySQL or MariaDB server installed on the nodes.

Install Percona XtraDB Cluster on every node

Run the commands in this section on all three servers. Install the tools the repository setup needs:

sudo apt update
sudo apt install -y wget gnupg2 lsb-release curl

Download and install the percona-release package, which manages Percona's repositories:

wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
sudo dpkg -i percona-release_latest.generic_all.deb
sudo apt update

Enable the PXC 8.4 LTS repository and install the cluster packages:

sudo percona-release setup pxc-84-lts
sudo apt install -y percona-xtradb-cluster

The installer asks for a password for the database root account. You can use the same password on all nodes; once nodes 2 and 3 join, they take over the accounts of node 1 anyway.

Stop the server on every node before you configure it:

sudo systemctl stop mysql

Share one set of TLS certificates

PXC 8.4 encrypts replication traffic by default (pxc-encrypt-cluster-traffic is on). All nodes must use identical key and certificate files, otherwise nodes 2 and 3 cannot join. MySQL generated ca.pem, server-cert.pem and server-key.pem in the data directory during installation; use the set from node 1 everywhere and keep it outside the data directory.

On node 1, copy the files to /etc/mysql/certs:

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 /etc/mysql/certs/
sudo chown -R mysql:mysql /etc/mysql/certs

Still on node 1, send the directory to the other two nodes. This writes an archive readable only by you into the home directory of deploy on each target:

sudo tar -C /etc/mysql -cf - certs | ssh deploy@198.51.100.12 'umask 077; cat > pxc-certs.tar'
sudo tar -C /etc/mysql -cf - certs | ssh deploy@198.51.100.13 'umask 077; cat > pxc-certs.tar'

On node 2 and node 3, unpack the archive and remove it:

sudo tar -C /etc/mysql -xf ~/pxc-certs.tar
sudo chown -R mysql:mysql /etc/mysql/certs
rm ~/pxc-certs.tar

Configure the nodes

On Debian the cluster settings live in /etc/mysql/mysql.conf.d/mysqld.cnf. Open it on each node:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

The packaged file already contains several wsrep_ lines with placeholder values. Change the existing lines rather than adding duplicates, so that the [mysqld] section ends up with these settings on node 1:

wsrep_provider=/usr/lib/galera4/libgalera_smm.so
wsrep_cluster_name=pxc-cluster
wsrep_cluster_address=gcomm://198.51.100.11,198.51.100.12,198.51.100.13

wsrep_node_name=pxc1
wsrep_node_address=198.51.100.11

pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
innodb_autoinc_lock_mode=2

ssl-ca=/etc/mysql/certs/ca.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-key=/etc/mysql/certs/server-key.pem

Use the same block on the other nodes and change only the two node-specific lines. On node 2:

wsrep_node_name=pxc2
wsrep_node_address=198.51.100.12

On node 3:

wsrep_node_name=pxc3
wsrep_node_address=198.51.100.13

What the settings do:

  • wsrep_provider is the path of the Galera library on Debian and Ubuntu.
  • wsrep_cluster_name must be identical on all nodes.
  • wsrep_cluster_address lists the nodes a starting node may contact. Listing all of them lets a node rejoin through whichever peer is up.
  • pxc_strict_mode=ENFORCING (the default) rejects operations Galera cannot replicate safely, such as writes to non-InnoDB tables.
  • wsrep_sst_method=xtrabackup-v2 is the only supported method for copying a full data set to a joining node.

Bootstrap the first node

A new cluster has to be started from exactly one node in bootstrap mode. On node 1 only:

sudo systemctl start mysql@bootstrap.service

Check that the cluster exists and has one member:

sudo mysql -u root -p -e "SHOW STATUS WHERE Variable_name IN ('wsrep_cluster_size','wsrep_cluster_status','wsrep_local_state_comment','wsrep_ready');"

You should see wsrep_cluster_size 1, wsrep_cluster_status Primary, wsrep_local_state_comment Synced and wsrep_ready ON.

Add the second and third node

On node 2, start the service normally. It contacts the cluster and receives a full copy of the data (a state snapshot transfer, SST), replacing whatever was in its own data directory:

sudo systemctl start mysql

Run the status query from the previous section on node 2 and wait until the state is Synced and the cluster size is 2. A state of Joiner means the transfer is still running. Join one node at a time; only then start node 3 the same way:

sudo systemctl start mysql

When all three nodes report a cluster size of 3, switch node 1 from bootstrap mode to the normal service. The plain mysql unit does not stop an instance started with mysql@bootstrap, so stop that unit explicitly:

sudo systemctl stop mysql@bootstrap.service
sudo systemctl start mysql

Verify replication

Create a database on node 2:

sudo mysql -u root -p -e "CREATE DATABASE percona;"

Create a table in it from node 3:

sudo mysql -u root -p -e "CREATE TABLE percona.example (node_id INT PRIMARY KEY, node_name VARCHAR(30));"

Insert a row on node 1:

sudo mysql -u root -p -e "INSERT INTO percona.example VALUES (1, 'percona1');"

Read it back on node 2:

sudo mysql -u root -p -e "SELECT * FROM percona.example;"

If the row appears, every node is accepting writes and replicating them. Note the primary key in the table definition: with strict mode enforcing, tables without one cannot be written to.

Maintenance and troubleshooting

  • Restarting one node: use sudo systemctl restart mysql. The node rejoins and catches up from a peer. Restart nodes one at a time so the cluster keeps a majority.
  • Starting after a full shutdown: if all nodes were stopped, bootstrap again from the node that was stopped last. Look for safe_to_bootstrap: 1 in /var/lib/mysql/grastate.dat, run sudo systemctl start mysql@bootstrap.service on that node, then start the others normally.
  • A node refuses to join: read /var/log/mysql/error.log on the joining node. The usual causes are a closed port among 4444, 4567 and 4568, certificate files that differ between nodes, or a wrong address in wsrep_cluster_address.
  • Updates: packages come from the Percona repository, so sudo apt update && sudo apt upgrade picks up new 8.4 releases. Upgrade and restart one node at a time.

Percona's documentation covers the remaining topics, including load balancing with ProxySQL: Percona XtraDB Cluster 8.4 documentation.

Frequently asked questions

Do I still need to create an SST user as older tutorials show?

No. Guides for PXC 5.6 and 5.7 create an sstuser account and set wsrep_sst_auth. Since version 8.0 the cluster creates a temporary internal account for each transfer, and that variable no longer exists.

Can I run the cluster with two nodes?

It will start, but it is not highly available. When the two nodes lose contact, neither holds a majority and both stop accepting writes. Add a third data node, or run the lightweight Galera arbitrator from the percona-xtradb-cluster-garbd package on a third machine to provide the tie-breaking vote.

Should applications write to all nodes at once?

They can, but simultaneous writes to the same rows on different nodes cause certification conflicts that surface as deadlock errors your application must retry. Many deployments send writes to one node and reads to all of them through a proxy such as ProxySQL or HAProxy.

Can I turn off traffic encryption to simplify the setup?

You can set pxc-encrypt-cluster-traffic=OFF on every node before bootstrapping, but then anyone who can reach the replication ports can join the cluster and copy your data. Sharing one set of certificates is little work and is the safer choice.

Share story:

Continue reading other guides on How VPS.