Setting Up Percona XtraDB Cluster on RHEL 9

Introduction

In today's data-driven world, database high availability is not just a luxury—it's a necessity. Percona XtraDB Cluster (PXC) provides a robust, open-source MySQL clustering solution that ensures your data remains available even during node failures. In this comprehensive guide, I'll walk you through setting up a 3-node PXC cluster on Red Hat Enterprise Linux 9, complete with SSL encryption and best practices.

Architecture Overview

Our cluster will consist of three nodes:

• Node 1 (pxc1): 10.40.24.81 - Bootstrap node

• Node 2 (pxc2): 10.40.24.119 - Secondary node

• Node 3 (pxc3): 10.40.24.175 - Secondary node

Prerequisites

• Three RHEL 9 servers with network connectivity

• Root or sudo access on all nodes

• Firewall ports open: 3306, 4444, 4567, 4568

• Minimum 2GB RAM per node (4GB+ recommended for production)

Step 1: Installation on All Nodes

Disable Default MySQL Module

sudo dnf module disable mysql

Install Percona Repository and Packages

sudo yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm

sudo percona-release setup pxc-80
sudo yum install -y percona-xtradb-cluster

Start MySQL and Secure Installation

sudo service mysql start

sudo grep 'temporary password' /var/log/mysqld.log
mysql -u root -p

Create Administrative Users

-- Create remote root user (use secure password in production)

CREATE USER 'root'@'localhost' IDENTIFIED BY 'Galera2025';

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;

-- Create SST user for cluster synchronization

CREATE USER 'sstuser'@'%' IDENTIFIED BY 'Galera2025';

GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'%';

FLUSH PRIVILEGES;
EXIT;

Stop MySQL for Configuration

sudo service mysql stop

Step 2: SSL Certificate Setup

Generate SSL Certificates on Primary Node

# On pxc1 only
sudo mysql_ssl_rsa_setup --uid=mysql

Copy Certificates to Other Nodes

bash

# Generate SSH key for secure transfer

-- Node 1
ssh-keygen -t rsa -b 4096

cat ~/.ssh/id_rsa.pub 

-- Node  2 and Node 3
echo "PASTE_PUBLIC_KEY_HERE" >> /root/.ssh/authorized_keys

chmod 600 /root/.ssh/authorized_keys 

# Copy certificates to other nodes

-- Node 1
sudo scp /var/lib/mysql/ca.pem /var/lib/mysql/server-cert.pem /var/lib/mysql/server-key.pem 10.40.24.119:/var/lib/mysql/
sudo scp /var/lib/mysql/ca.pem /var/lib/mysql/server-cert.pem /var/lib/mysql/server-key.pem 10.40.24.175:/var/lib/mysql/

Set Proper Permissions on All Nodes

sudo chown -R mysql:mysql /var/lib/mysql/

sudo chmod 600 /var/lib/mysql/server-key.pem
sudo chmod 644 /var/lib/mysql/ca.pem /var/lib/mysql/server-cert.pem

Step 3: Configuration Files

Node 1 Configuration (/etc/my.cnf)

[client]

socket=/var/lib/mysql/mysql.sock

[mysqld]

server-id=1

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

# Binary log expiration (7 days)

binlog_expire_logs_seconds=604800

######## wsrep Configuration ###############

wsrep_provider=/usr/lib64/galera4/libgalera_smm.so

wsrep_cluster_address=gcomm://10.40.24.81,10.40.24.119,10.40.24.175

binlog_format=ROW

wsrep_slave_threads=8

wsrep_log_conflicts

innodb_autoinc_lock_mode=2

wsrep_node_address=10.40.24.81

wsrep_cluster_name=pxc-cluster

wsrep_node_name=pxc1

pxc_strict_mode=ENFORCING

wsrep_sst_method=xtrabackup-v2

# SSL Configuration for secure replication

wsrep_provider_options="socket.ssl_key=/var/lib/mysql/server-key.pem;socket.ssl_cert=/var/lib/mysql/server-cert.pem;socket.ssl_ca=/var/lib/mysql/ca.pem"

[sst]

encrypt=4

ssl-key=/var/lib/mysql/server-key.pem

ssl-ca=/var/lib/mysql/ca.pem

ssl-cert=/var/lib/mysql/server-cert.pem

Node 2 Configuration

Use the same configuration as Node 1, but update:

wsrep_node_address=10.40.24.119
wsrep_node_name=pxc2

Node 3 Configuration

Use the same configuration as Node 1, but update:

wsrep_node_address=10.40.24.175
wsrep_node_name=pxc3

Step 4: Bootstrap the Cluster

Start the Bootstrap Node

# On Node 1 only

sudo systemctl start mysql@bootstrap.service

Verify Bootstrap Success

mysql -u root -p'Galera2025' -e "SHOW STATUS LIKE 'wsrep_cluster_size';"

Expected Output:

+--------------------+-------+

| Variable_name      | Value |

+--------------------+-------+

| wsrep_cluster_size | 1     |
+--------------------+-------+

Step 5: Add Additional Nodes

Start Node 2

# On Node 2
sudo systemctl start mysql

Start Node 3

# On Node 3 
sudo systemctl start mysql

Verify Cluster Status

# On any node
mysql -u root -p'Galera2025' -e "SHOW STATUS LIKE 'wsrep_cluster_size'; SHOW STATUS LIKE 'wsrep_incoming_addresses';"

Expected Output:

+--------------------+-------+

| Variable_name      | Value |

+--------------------+-------+

| wsrep_cluster_size | 3     |

+--------------------+-------+

+--------------------------+-----------------------------------------+

| Variable_name            | Value                                   |

+--------------------------+-----------------------------------------+

| wsrep_incoming_addresses | 10.40.24.81:3306,10.40.24.119:3306,10.40.24.175:3306 |
+--------------------------+-----------------------------------------+

Step 6: Test Replication

Create Test Data on Node 1

mysql -u root -p'Galera2025' <<EOF

CREATE DATABASE IF NOT EXISTS galera_test;

USE galera_test;

CREATE TABLE IF NOT EXISTS test_table (

    id INT PRIMARY KEY, 

    node_name VARCHAR(50),

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

INSERT INTO test_table (id, node_name) VALUES (1, 'From Node 1 after cluster join');

SELECT * FROM test_table;
EOF

Verify Replication on Node 2

mysql -u root -p'Galera2025' -e "SELECT * FROM galera_test.test_table;"

Test Write on Node 3

mysql -u root -p'Galera2025' -e "INSERT INTO galera_test.test_table (id, node_name) VALUES (2, 'From Node 3 after cluster join');"

Verify All Nodes See the Data

# On any node
mysql -u root -p'Galera2025' -e "SELECT * FROM galera_test.test_table ORDER BY id;"

Step 7: Firewall Configuration

Open Required Ports on All Nodes

sudo firewall-cmd --permanent --add-port=3306/tcp

sudo firewall-cmd --permanent --add-port=4444/tcp

sudo firewall-cmd --permanent --add-port=4567/tcp

sudo firewall-cmd --permanent --add-port=4568/tcp

sudo firewall-cmd --permanent --add-port=9200/tcp
sudo firewall-cmd --reload

Step 8: Monitoring and Maintenance

Key Status Variables to Monitor

SHOW STATUS LIKE 'wsrep_cluster_size';

SHOW STATUS LIKE 'wsrep_ready';

SHOW STATUS LIKE 'wsrep_connected';

SHOW STATUS LIKE 'wsrep_local_state_comment';
SHOW STATUS LIKE 'wsrep_flow_control_paused';

Create Monitoring Script

sudo tee /usr/local/bin/pxc-status.sh > /dev/null <<'EOF'

#!/bin/bash

echo "=== Percona XtraDB Cluster Status ==="

mysql -u root -p'Galera2025' -e "

SHOW STATUS LIKE 'wsrep_cluster_size';

SHOW STATUS LIKE 'wsrep_ready';

SHOW STATUS LIKE 'wsrep_connected';

SHOW STATUS LIKE 'wsrep_local_state_comment';

SHOW STATUS LIKE 'wsrep_flow_control_paused';

" 2>/dev/null

EOF

sudo chmod +x /usr/local/bin/pxc-status.sh

Troubleshooting Common Issues

1. Node Won't Join Cluster

• Verify network connectivity between nodes

• Check firewall rules

• Ensure SST user permissions are correct

• Verify SSL certificates are properly copied

2. Split-Brain Scenario

• Always bootstrap from the same node

• Use mysql@bootstrap.service only on one node

• Check wsrep_cluster_address configuration

3. SST Failures

• Verify sstuser has correct privileges

• Check disk space on all nodes

• Ensure SSL certificates are valid