MySQL High Availability – Percona XtraDB Cluster (Installation and Features)

MySQL High Availability – PXC Cluster (Installation and Features)

PXC is a multi‑master synchronous replication plugin based on Galera, designed for OLTP workloads. MySQL's built‑in asynchronous replication cannot guarantee full consistency between master and slave.

OLAP focuses on data analysis and is suited to MyISAM, while OLTP emphasizes transaction consistency and CRUD operations, which fit InnoDB. Galera only supports InnoDB. PXC solves strong data synchronization in MySQL clusters and is widely regarded as a preferred solution.

Cluster Characteristics

Multi‑master architecture: True multi‑node read/write cluster without master/slave distinction; any node can read or write the latest data.

Synchronous replication: Transactions are committed on all nodes simultaneously; failure of any node aborts the transaction, ensuring no data loss and zero lag.

Strong consistency: Data is consistent across all nodes; writes must be replicated to every node before succeeding, which can affect performance as node count grows.

Parallel replication: Apply phase runs in parallel on replica nodes, improving performance.

Failover: Multi‑write capability makes node failover straightforward.

Hot‑plug: If a node crashes, rapid monitoring minimizes downtime; the impact of a failed node on the cluster is minimal.

Automatic node cloning: New or maintenance nodes automatically pull data from online nodes, achieving eventual consistency without manual backup.

Application transparency: Cluster maintenance is invisible to applications.

PXC Cluster Drawbacks

1. Only InnoDB data can be synchronized; other storage engines are excluded.

2. Adding a new node requires a full data copy, which may block reads/writes until completion.

3. Cluster performance is limited by the slowest node due to global checks.

4. Every table must have a primary key.

5. Explicit lock operations like LOCK TABLE are not supported.

6. More nodes slow down data synchronization.

Installing a PXC Cluster

Remove MariaDB packages

yum -y remove mari*

Open 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

3306: MySQL service port; 4567: cluster communication; 4444: SST (State Snapshot Transfer); 4568: IST (Incremental State Transfer).

Disable SELinux

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

Download and install PXC on all nodes

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

yum localinstall *.rpm

Modify configuration files

vi /etc/my.cnf
# there are directories my.cnf.d and percona-xtradb-cluster.conf.d
cd /etc/percona-xtradb-cluster.conf.d
vi mysqld.cnf   # configure MySQL settings
# add the following
character_set_server = utf8
bind-address = 0.0.0.0
skip-name-resolve
# later edit wsrep.cnf with:
server-id=1
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

Initialize MySQL on all nodes

# start MySQL
systemctl start mysqld
# view temporary root password
cat /var/log/mysqld.log | grep "A temporary password"
# change root password
mysql_secure_installation
# create remote admin user
mysql -u root -p
CREATE USER 'admin'@'%' IDENTIFIED BY 'Abc_123456';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%';
FLUSH PRIVILEGES;
exit

Stop MySQL on all nodes and bootstrap the cluster

systemctl stop mysqld
# edit wsrep.cnf with unique server-id, wsrep_node_name, wsrep_node_address for each node
# start first node
systemctl start [email protected]
# start other nodes
service mysql start
service mysql stop
service mysql restart

Verification

Run the following SQL on any node to view cluster status:

show status like 'wsrep_cluster%'