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.
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
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)
sudo dnf module disable mysql
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
sudo service mysql start
sudo grep 'temporary password' /var/log/mysqld.log
mysql -u root -p
-- 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;
sudo service mysql stop
# On pxc1 only
sudo mysql_ssl_rsa_setup --uid=mysql
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/
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
[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
Use the same configuration as Node 1, but update:
wsrep_node_address=10.40.24.119
wsrep_node_name=pxc2
Use the same configuration as Node 1, but update:
wsrep_node_address=10.40.24.175
wsrep_node_name=pxc3
# On Node 1 only
sudo systemctl start mysql@bootstrap.service
mysql -u root -p'Galera2025' -e "SHOW STATUS LIKE 'wsrep_cluster_size';"
Expected Output:
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 1 |
+--------------------+-------+
# On Node 2
sudo systemctl start mysql
# On Node 3
sudo systemctl start mysql
# 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 |
+--------------------------+-----------------------------------------+
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
mysql -u root -p'Galera2025' -e "SELECT * FROM galera_test.test_table;"
mysql -u root -p'Galera2025' -e "INSERT INTO galera_test.test_table (id, node_name) VALUES (2, 'From Node 3 after cluster join');"
# On any node
mysql -u root -p'Galera2025' -e "SELECT * FROM galera_test.test_table ORDER BY id;"
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
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';
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
Verify network connectivity between nodes
Check firewall rules
Ensure SST user permissions are correct
Verify SSL certificates are properly copied
Always bootstrap from the same node
Use mysql@bootstrap.service only on one node
Check wsrep_cluster_address configuration
Verify sstuser has correct privileges
Check disk space on all nodes
Ensure SSL certificates are valid