How to Build a 3-Node Percona XtraDB Cluster Lab for MySQL High Availability

Percona XtraDB Cluster, commonly known as PXC, is a high-availability MySQL solution based on Percona Server for MySQL and Galera replication.

Unlike a traditional MySQL primary-replica architecture, every node in a Percona XtraDB Cluster contains a complete copy of the database and can participate in transaction processing.

This makes PXC especially interesting for DevOps, SRE, and Database Reliability Engineering environments where high availability, automated recovery, rolling maintenance, and failure handling are important.

In this guide, we will build a three-node Percona XtraDB Cluster lab and explore not only how to install it, but also how the cluster behaves when nodes fail, replication falls behind, or quorum is lost.

The goal is to create a lab where we can safely break things and understand how Percona behaves under real operational scenarios.

Percona XtraDB Cluster Architecture

A basic PXC architecture looks like this:

 1                Application
 2                     |
 3                HAProxy
 4                     |
 5          +----------+----------+
 6          |          |          |
 7        PXC01      PXC02      PXC03
 8        MySQL      MySQL      MySQL
 9          \          |          /
10           \------ Galera -----/

Each node contains a full copy of the database.

This differs from traditional MySQL replication, where one primary typically handles writes and replicas asynchronously receive changes:

1Primary
2   |
3   | asynchronous replication
4   v
5Replica

With Percona XtraDB Cluster, transactions are replicated using Galera's write-set replication.

Although PXC supports writes through multiple nodes, that does not necessarily mean an application should randomly distribute writes across every database server.

A common production architecture is to use a preferred writer while keeping the remaining nodes available for failover:

1                 HAProxy / ProxySQL
2                         |
3                     Writes
4                         |
5                       PXC01
6                      /     \
7                   PXC02   PXC03

This reduces the probability of transaction conflicts while still providing high availability.

How Transactions Work in PXC

Imagine an application executes the following transaction against pxc01:

1BEGIN;
2
3UPDATE accounts
4SET balance = balance - 100
5WHERE id = 10;
6
7COMMIT;

The transaction executes locally first.

Before it is committed, Galera creates a write-set describing the changes made by the transaction.

The write-set is distributed to the other members of the cluster:

 1PXC01
 2  |
 3Transaction executes
 4  |
 5  v
 6Write-set generated
 7  |
 8  +------------+
 9  |            |
10  v            v
11PXC02         PXC03
12  |            |
13Certify       Certify
14  \            /
15   \----------/
16        |
17      Commit

Each node performs a process known as certification.

Certification determines whether another concurrent transaction modified the same data.

For example:

 1PXC01                          PXC02
 2
 3UPDATE users                   UPDATE users
 4SET name='Alice'               SET name='Bob'
 5WHERE id=10                    WHERE id=10
 6
 7        \                        /
 8         \                      /
 9              Certification
10                    |
11             Conflict detected
12                    |
13          One transaction aborts

This behavior is one reason multi-primary replication should be used carefully.

Applications generating many concurrent writes against the same rows can experience certification conflicts.

Why a PXC Cluster Should Have Three Nodes

One of the most important concepts in Galera-based clusters is quorum.

Consider a three-node cluster:

1PXC01 ---- PXC02 ---- PXC03
2
3  1          1          1

The cluster has three voting members.

To remain operational, a majority of the cluster must remain connected.

With three nodes:

13 nodes available → quorum
22 nodes available → quorum
31 node available  → no quorum

If one node fails:

1PXC01 -------- PXC02        X PXC03
2
32 / 3 nodes available

The remaining two nodes still represent a majority, so the cluster continues processing transactions.

If another node fails:

1PXC01
2
3X PXC02
4X PXC03

The remaining node no longer has quorum.

You may see states such as:

1wsrep_cluster_status = Non-Primary
2wsrep_ready          = OFF

The database intentionally stops accepting normal application traffic.

This protects the cluster against one of the most dangerous problems in distributed databases:

split brain.

Without quorum protection, two isolated sides of a network partition could both believe they are authoritative and accept conflicting writes.

Understanding IST and SST

Another important PXC concept is how a node synchronizes after being offline.

Imagine our cluster starts healthy:

1PXC01   PXC02   PXC03
2  ✓       ✓       ✓

Now pxc03 goes offline:

1PXC01   PXC02   PXC03
2  ✓       ✓       X

Applications continue generating transactions while the node is unavailable.

When pxc03 returns, it must synchronize with the cluster.

There are two primary mechanisms for this.

Incremental State Transfer — IST

If another cluster member still has the missing write-sets in its Galera cache, the returning node can receive only the transactions it missed.

 1PXC01 GCache
 2
 3TX1001
 4TX1002
 5TX1003
 6TX1004
 7TX1005
 8
 9     |
10     | Missing transactions
11     v
12
13   PXC03

This process is called:

Incremental State Transfer, or IST.

IST is normally much faster because it does not require transferring the entire database.

State Snapshot Transfer — SST

If the returning node has been offline long enough that the required transactions are no longer available in GCache, a complete copy of the database must be transferred.

1PXC01
2
3Full database
4     |
5     |
6     v
7
8   PXC03

This process is called:

State Snapshot Transfer, or SST.

SST can place considerably more load on storage, networking, and the donor node than IST.

Understanding the relationship between:

1IST
2SST
3GCache
4Donor
5Joiner

is important when operating Percona clusters.

Building the Lab

For this lab we will use four virtual machines:

1Server          IP Address          Purpose
2
3pxc01           192.168.50.11       Percona node
4pxc02           192.168.50.12       Percona node
5pxc03           192.168.50.13       Percona node
6mysql-proxy     192.168.50.10       HAProxy

A reasonable configuration for each database VM is:

12 vCPU
24 GB RAM
330–50 GB disk
4Ubuntu 24.04

For learning purposes, virtual machines are preferable to containers because they allow us to reproduce infrastructure-level failures such as:

• Network partitions

• VM crashes

• Disk exhaustion

• Storage latency

• Firewall problems

• Rolling operating-system maintenance

Required Network Ports

PXC nodes need to communicate over several ports.

13306    MySQL client connections
24444    State Snapshot Transfer
34567    Galera replication
44568    Incremental State Transfer

If UFW is enabled:

1sudo ufw allow 3306/tcp
2sudo ufw allow 4444/tcp
3sudo ufw allow 4567/tcp
4sudo ufw allow 4567/udp
5sudo ufw allow 4568/tcp

In a real environment, these rules should be restricted to the database network instead of being accessible from every source.

Configure the Hostnames

On the first node:

1sudo hostnamectl set-hostname pxc01

On the second:

1sudo hostnamectl set-hostname pxc02

And on the third:

1sudo hostnamectl set-hostname pxc03

Add the nodes to /etc/hosts:

1192.168.50.11 pxc01
2192.168.50.12 pxc02
3192.168.50.13 pxc03

Verify connectivity between all three nodes before continuing.

Install Percona XtraDB Cluster

Perform the installation on every database node.

1sudo apt update
2
3sudo apt install -y \
4    wget \
5    gnupg2 \
6    lsb-release \
7    curl

Download the Percona repository package:

1wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb

Install it:

1sudo dpkg -i percona-release_latest.generic_all.deb

Enable the PXC repository:

1sudo percona-release setup pxc-84-lts

Update the package list:

1sudo apt update

Install Percona XtraDB Cluster:

1sudo apt install -y percona-xtradb-cluster

After installation, stop MySQL on all three nodes:

1sudo systemctl stop mysql

We want to configure the cluster before starting it.

Configure PXC01

First inspect the MySQL configuration directories:

1ls -la /etc/mysql/
2ls -la /etc/mysql/mysql.conf.d/

Create a PXC configuration file:

1sudo nano /etc/mysql/mysql.conf.d/pxc.cnf

For pxc01:

 1[mysqld]
 2
 3server-id=1
 4
 5datadir=/var/lib/mysql
 6user=mysql
 7
 8default_storage_engine=InnoDB
 9innodb_autoinc_lock_mode=2
10
11wsrep_provider=/usr/lib/libgalera_smm.so
12
13wsrep_cluster_name=pxc-lab
14
15wsrep_cluster_address=gcomm://192.168.50.11,192.168.50.12,192.168.50.13
16
17wsrep_node_name=pxc01
18wsrep_node_address=192.168.50.11
19
20wsrep_sst_method=clone

The most important settings here are:

1wsrep_cluster_name
2wsrep_cluster_address
3wsrep_node_name
4wsrep_node_address

These tell Galera which cluster the server belongs to and how to discover the other nodes.

Configure PXC02

Use the same configuration with node-specific values:

 1[mysqld]
 2
 3server-id=2
 4
 5datadir=/var/lib/mysql
 6user=mysql
 7
 8default_storage_engine=InnoDB
 9innodb_autoinc_lock_mode=2
10
11wsrep_provider=/usr/lib/libgalera_smm.so
12
13wsrep_cluster_name=pxc-lab
14
15wsrep_cluster_address=gcomm://192.168.50.11,192.168.50.12,192.168.50.13
16
17wsrep_node_name=pxc02
18wsrep_node_address=192.168.50.12
19
20wsrep_sst_method=clone

Configure PXC03

For the third server:

 1[mysqld]
 2
 3server-id=3
 4
 5datadir=/var/lib/mysql
 6user=mysql
 7
 8default_storage_engine=InnoDB
 9innodb_autoinc_lock_mode=2
10
11wsrep_provider=/usr/lib/libgalera_smm.so
12
13wsrep_cluster_name=pxc-lab
14
15wsrep_cluster_address=gcomm://192.168.50.11,192.168.50.12,192.168.50.13
16
17wsrep_node_name=pxc03
18wsrep_node_address=192.168.50.13
19
20wsrep_sst_method=clone

Bootstrap the Cluster

The first node must be started differently from the other members.

On pxc01:

1sudo systemctl start mysql@bootstrap

Bootstrapping tells Galera that this server is creating a new Primary Component rather than attempting to join an existing cluster.

Connect to MySQL:

1mysql -uroot -p

Check the cluster status:

1SHOW STATUS LIKE 'wsrep_cluster_size';
2
3SHOW STATUS LIKE 'wsrep_cluster_status';
4
5SHOW STATUS LIKE 'wsrep_local_state_comment';
6
7SHOW STATUS LIKE 'wsrep_ready';

A healthy first node should report something similar to:

1wsrep_cluster_size          1
2wsrep_cluster_status        Primary
3wsrep_local_state_comment   Synced
4wsrep_ready                 ON

Add PXC02

Now start MySQL normally on the second node:

1sudo systemctl start mysql

Watch the logs:

1journalctl -u mysql -f

The node should discover pxc01 and synchronize its state.

Check the cluster:

1SHOW STATUS LIKE 'wsrep_cluster_size';

The expected result is:

12

Also check:

1SHOW STATUS LIKE 'wsrep_local_state_comment';

The node should eventually report:

1Synced

Add PXC03

Start MySQL normally:

1sudo systemctl start mysql

Check the cluster again:

1SHOW STATUS LIKE 'wsrep_cluster_size';

You should now have:

13

Verify:

1SHOW STATUS LIKE 'wsrep_cluster_status';

The expected state is:

1Primary
2

Our three-node cluster is now operational.

Test Replication

Connect to pxc01 and create a database:

1CREATE DATABASE dbre_lab;
2
3USE dbre_lab;

Create a table:

1CREATE TABLE transactions (
2    id BIGINT PRIMARY KEY AUTO_INCREMENT,
3    description VARCHAR(255),
4    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
5);

Insert a record:

1INSERT INTO transactions(description)
2VALUES ('Inserted through pxc01');

Now connect to pxc02:

1SELECT *
2FROM dbre_lab.transactions;

The row should already exist.

Insert another transaction through pxc02:

1INSERT INTO dbre_lab.transactions(description)
2VALUES ('Inserted through pxc02');

Connect to pxc03:

1SELECT *
2FROM dbre_lab.transactions;

Both transactions should appear.

This demonstrates one of the fundamental characteristics of PXC: every cluster member maintains the database state.

Important WSREP Metrics

Running:

1SHOW STATUS LIKE 'wsrep%';

returns a very large number of metrics.

For day-to-day troubleshooting, a smaller group is particularly useful:

 1SHOW STATUS WHERE Variable_name IN (
 2    'wsrep_cluster_size',
 3    'wsrep_cluster_status',
 4    'wsrep_connected',
 5    'wsrep_ready',
 6    'wsrep_local_state_comment',
 7    'wsrep_local_recv_queue',
 8    'wsrep_flow_control_paused',
 9    'wsrep_local_cert_failures',
10    'wsrep_local_bf_aborts'
11);

For a healthy three-node cluster we normally expect:

1wsrep_cluster_size           3
2wsrep_cluster_status         Primary
3wsrep_connected              ON
4wsrep_ready                  ON
5wsrep_local_state_comment    Synced

These are excellent metrics to include in monitoring and alerting.

Failure Exercise 1: Lose One Node

Now the interesting part begins.

Stop MySQL on pxc03:

1sudo systemctl stop mysql

Check the cluster from another node:

1SHOW STATUS LIKE 'wsrep_cluster_size';

The cluster should report:

12

Because two of the three voting members remain available, quorum still exists.

Writes should continue succeeding:

1INSERT INTO dbre_lab.transactions(description)
2VALUES ('Transaction while PXC03 is unavailable');

Bring the node back:

1sudo systemctl start mysql

Watch its synchronization process:

1journalctl -u mysql -f

Failure Exercise 2: Observe an IST

Stop pxc03 again:

1sudo systemctl stop mysql

Generate additional transactions through pxc01:

1INSERT INTO dbre_lab.transactions(description)
2VALUES ('PXC03 is offline');

Now restart the third node:

1sudo systemctl start mysql

Review the logs:

1journalctl -u mysql

Search for references to:

1IST

If the required write-sets remain available in GCache, Galera should perform an Incremental State Transfer.

The important relationship is:

1Donor
2  |
3  | Missing write-sets
4  v
5Joiner

The returning server receives only the transactions it missed.

Failure Exercise 3: Force an SST

Next we can intentionally make IST impossible.

The idea is:

11. Stop PXC03
22. Generate large amounts of data
33. Overflow the available GCache
44. Restart PXC03

Because the donor can no longer provide all of the missing write-sets, PXC must perform a full State Snapshot Transfer.

This is useful because SST introduces very different operational concerns.

Monitor:

1SST duration
2CPU utilization
3Disk throughput
4Network utilization
5Donor performance
6Joiner status
7Application latency

This is the type of scenario DBRE teams should understand before it happens in production.

Failure Exercise 4: Lose Quorum

Start from a healthy cluster:

1PXC01 ✓
2PXC02 ✓
3PXC03 ✓

Stop the second node:

1sudo systemctl stop mysql

The cluster still has quorum.

Now stop the third node:

1sudo systemctl stop mysql

Only pxc01 remains.

Check:

1SHOW STATUS LIKE 'wsrep_cluster_status';
2
3SHOW STATUS LIKE 'wsrep_ready';

The remaining server should no longer behave like a normal healthy cluster member.

This is an important demonstration of the difference between:

1MySQL is running

and:

1The database is safe to receive traffic

A process being alive does not necessarily mean the database should be included in the application load balancer.

Failure Exercise 5: Create a Network Partition

Database processes are not the only things that fail.

Networks fail too.

Block Galera traffic on one node:

1sudo iptables -A INPUT -p tcp --dport 4567 -j DROP
2sudo iptables -A OUTPUT -p tcp --dport 4567 -j DROP

Observe:

1SHOW STATUS LIKE 'wsrep%';

Pay particular attention to:

1wsrep_cluster_size
2wsrep_cluster_status
3wsrep_connected
4wsrep_ready

Remove the firewall rules afterward:

1sudo iptables -D INPUT -p tcp --dport 4567 -j DROP
2sudo iptables -D OUTPUT -p tcp --dport 4567 -j DROP

This exercise demonstrates that:

1mysqld crash
2VM crash
3network partition
4slow network
5disk failure

are very different failure modes.

A reliable database platform needs to detect and respond appropriately to each of them.

Add HAProxy

Our next improvement is to stop applications from connecting directly to individual database nodes.

The architecture becomes:

 1                    Application
 2                         |
 3                         v
 4                 +---------------+
 5                 |    HAProxy    |
 6                 | 192.168.50.10 |
 7                 +-------+-------+
 8                         |
 9            +------------+------------+
10            |            |            |
11            v            v            v
12        +-------+    +-------+    +-------+
13        | PXC01 |    | PXC02 |    | PXC03 |
14        +-------+    +-------+    +-------+

Install HAProxy:

1sudo apt update
2sudo apt install -y haproxy

One particularly important consideration is the health check.

Checking only:

1TCP port 3306

is not sufficient.

For example, a database can have:

1mysqld running
23306 listening

while simultaneously reporting:

1wsrep_ready = OFF

That server should not receive application traffic.

A PXC-aware health check should therefore evaluate cluster state before declaring a node healthy.

Monitoring the Cluster

Once the basic architecture works, add monitoring.

Percona Monitoring and Management, or PMM, is a natural choice for a Percona lab.

The environment could eventually look like this:

 1                      Application
 2                           |
 3                        HAProxy
 4                           |
 5              +------------+------------+
 6              |            |            |
 7            PXC01        PXC02        PXC03
 8              |            |            |
 9              +------------+------------+
10                           |
11                          PMM

Useful metrics include:

 1CPU utilization
 2Memory consumption
 3Disk latency
 4Buffer pool utilization
 5Connections
 6Query latency
 7Slow queries
 8Replication queues
 9Galera flow control
10Certification failures
11SST activity
12IST activity

In particular, metrics such as:

1wsrep_local_recv_queue
2wsrep_flow_control_paused
3wsrep_local_cert_failures
4wsrep_local_bf_aborts

can tell us much more about the health of the cluster than simply verifying whether MySQL is running.

Turning the Lab Into a DBRE Training Environment

Installing Percona is only the beginning.

The real value comes from operating and breaking it.

A useful learning progression would be:

Stage 1 — Build

Deploy three PXC nodes manually.

Understand every configuration parameter instead of immediately automating everything.

Stage 2 — Operate

Add:

1HAProxy
2ProxySQL
3PMM
4Backups
5Monitoring
6Alerts

Stage 3 — Break

Simulate:

1Node failures
2VM failures
3Network partitions
4Disk full conditions
5High latency
6Firewall failures
7Configuration errors
8Replication problems

Stage 4 — Recover

Practice:

1IST recovery
2SST recovery
3Quorum recovery
4Node replacement
5Full-cluster recovery

Stage 5 — Maintain

Perform:

1Rolling restarts
2Operating-system maintenance
3Database upgrades
4Configuration changes
5Certificate rotation

without taking the entire database offline.

Stage 6 — Automate

After understanding the manual process, rebuild the environment using tools such as:

1Terraform
2Ansible
3GitHub Actions

The objective should be to make the cluster reproducible.

Stage 7 — Observe

Build alerts and dashboards around:

1WSREP state
2Flow control
3Certification conflicts
4Node synchronization
5Query latency
6Disk latency
7SST and IST events

Stage 8 — Document

Finally, write operational runbooks.

Examples:

1PXC node failed
2Cluster is Non-Primary
3Node cannot rejoin
4SST is stuck
5Disk is full
6Flow control is high
7Rolling restart procedure
8Rolling upgrade procedure
9Complete cluster outage

This turns a simple home lab into something that closely resembles the operational work performed by Database Reliability Engineering teams.

Full-Cluster Recovery

One scenario deserves special attention:

all cluster nodes are down.

Imagine:

1PXC01 X
2PXC02 X
3PXC03 X

At this point, we should not simply select a random node and bootstrap the cluster.

Each server may contain a different Galera sequence position depending on which transactions it successfully processed before the outage.

During recovery, administrators need to determine which node contains the most advanced trustworthy state.

A typical recovery investigation includes tools such as:

1mysqld --wsrep-recover

The objective is to determine the Galera position associated with each node and bootstrap from the correct database state.

Choosing the wrong node can potentially mean starting the new cluster from an older state.

That makes full-cluster recovery one of the most valuable exercises to practice in a PXC lab.

What This Lab Teaches

A Percona XtraDB Cluster lab teaches much more than MySQL installation.

It provides hands-on experience with distributed database concepts such as:

 1Quorum
 2Consensus
 3Write-set replication
 4Transaction certification
 5Split-brain prevention
 6Synchronous replication
 7Failure domains
 8State synchronization
 9Flow control
10Automated failover

It also highlights an important reliability engineering principle:

A running database process is not necessarily a healthy database service.

A node may have MySQL running and port 3306 listening while being disconnected from the Primary Component or unable to safely process application traffic.

That distinction is why health checks, observability, quorum awareness, and automated failover are critical parts of database infrastructure.

Conclusion

Percona XtraDB Cluster provides an excellent platform for learning how highly available MySQL environments operate.

A three-node lab gives us a safe environment to explore behaviors that are difficult—or dangerous—to experiment with in production.

Instead of stopping after a successful installation, intentionally break the cluster.

Kill a node.

Disconnect the network.

Force an SST.

Lose quorum.

Fill the disk.

Restart every server.

Recover the cluster.

Then automate everything and repeat the exercises.

The objective is not simply to learn how to install Percona.

The objective is to understand what happens when the database infrastructure stops behaving the way we expect.

That is where the lab starts becoming valuable from a Database Reliability Engineering perspective.