How to Build a High‑Availability MySQL PXC Cluster: Installation & Features

Overview

Percona XtraDB Cluster (PXC) is a Galera‑based multi‑master synchronous replication plugin designed for OLTP workloads. It provides strong consistency across all nodes, which MySQL's native asynchronous replication cannot guarantee.

Key Features

Multi‑master architecture : any node can accept reads and writes; changes are replicated to all other nodes instantly.

Synchronous replication : a transaction is committed only after it has been written to every node; a failure on any node aborts the transaction.

Strong consistency : data is visible on all nodes only after the commit succeeds.

Parallel apply : replica nodes apply received writes in parallel, improving throughput.

Automatic failover : because every node can write, replacing a failed node is straightforward.

Hot‑plug capability : nodes can be added or removed with minimal downtime.

Automatic node cloning : a new or maintenance node automatically pulls missing data from online peers.

Application transparency : the cluster operates without code changes in client applications.

Limitations

Only InnoDB tables are replicated; other storage engines are ignored.

Adding a new node requires a full data copy, which can block writes on the donor until the copy finishes.

Cluster performance is bounded by the slowest node because all nodes must agree on each transaction.

Every table must have a primary key.

Explicit table locks such as LOCK TABLE are not supported.

Increasing the number of nodes raises synchronization latency.

Installation Procedure

1. Remove existing MariaDB packages

yum -y remove mari*

2. Open required firewall ports

firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --zone=public --add-port=4444/tcp --permanent
firewall-cmd --zone=public --add-port=4567/tcp --permanent
firewall-cmd --zone=public --add-port=4568/tcp --permanent

Port 3306 is the MySQL service port. Port 4567 is used for Galera cluster communication. Port 4444 is the SST (State Snapshot Transfer) port for full data sync. Port 4568 is the IST (Incremental State Transfer) port for incremental sync.

3. Disable SELinux

vi /etc/selinux/config   # set SELINUX=disabled
reboot

4. Download Percona XtraDB Cluster packages

Download the latest RPM for Percona‑XtraDB‑Cluster‑5.7 from https://www.percona.com/downloads/Percona-XtraDB-Cluster-57/LATEST/ and also the qpress-11-1.el7.x86_64 package.

yum localinstall *.rpm

5. Configure the cluster (wsrep)

vi /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
# Example configuration
character_set_server = utf8
bind-address = 0.0.0.0
skip-name-resolve
wsrep_provider = /usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_name = pxc-cluster
wsrep_cluster_address = gcomm://1.1.1.1,1.1.1.2,1.1.1.3
wsrep_node_name = pxc1
wsrep_node_address = 1.1.1.1
wsrep_sst_method = xtrabackup-v2
wsrep_sst_auth = admin:Abc_123456
pxc_strict_mode = ENFORCING
binlog_format = ROW
default_storage_engine = InnoDB
innodb_autoinc_lock_mode = 2

6. Initialise MySQL on each host

# Start MySQL service
systemctl start mysqld
# Retrieve the temporary root password generated on first start
grep "A temporary password" /var/log/mysqld.log
# Secure the installation (set root password, remove test DB, etc.)
mysql_secure_installation
# Create a remote admin user for SST operations
mysql -u root -p
CREATE USER 'admin'@'%' IDENTIFIED BY 'Abc_123456';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%';
FLUSH PRIVILEGES;
exit

7. Bootstrap the first node and join the remaining nodes

# Stop MySQL on all nodes
systemctl stop mysqld
# Ensure each node has a unique wsrep_node_name and wsrep_node_address in wsrep.cnf
# On the first node start the bootstrap service
systemctl start [email protected]
# On the other nodes start the normal service
systemctl start mysqld
# Verify that all nodes have joined the cluster
mysql -e "SHOW STATUS LIKE 'wsrep_cluster%';"

Verification

Execute SHOW STATUS LIKE 'wsrep_cluster%'; on any node. Important fields include wsrep_cluster_size (number of members), wsrep_cluster_status (Primary/Non‑Primary), and wsrep_ready (1 = ready).