Percona XtraDB Cluster is a robust, open-source database clustering solution for MySQL, ensuring high availability and performance. Fully compatible with MySQL Server Community Edition 8.0, it supports synchronous replication, multi-source replication, automatic node provisioning, SSL encryption, and optimized performance.
This guide will walk you through installing and setting up a MySQL cluster using Percona XtraDB Cluster (PXC) on Debian 11 servers. We’ll demonstrate installing Percona XtraDB Cluster across multiple Debian servers and setting up MySQL cluster replication between them.
Prerequisites
To follow this tutorial, you’ll need at least two Debian 11 servers. These should all have a non-root user with sudo or root privileges.
For our example setup, we’ll use three servers with the following details:
Hostname IP Address -------------------------- pxc01 192.168.5.15 pxc02 192.168.5.16 pxc03 192.168.5.17
Once your servers are ready, you can proceed with installing Percona XtraDB Cluster.
Setup Hosts and Firewall
Start by updating the /etc/hosts file on each server to resolve hostnames to IP addresses. Also, configure the firewall to allow ports used by Percona XtraDB Cluster across all servers.
Open the required ports for Percona XtraDB Cluster:
Ports Used for ------------------------ 3306 MySQL client connection and SST (State Snapshot Transfer) 4444 SST via Percona XtraBackup 4567 Write-set replication (TCP) and multicast replication (TCP/UDP) 4568 IST (Incremental State Transfer)
Edit your /etc/hosts file:
sudo nano /etc/hosts
and add:
192.168.5.15 pxc01 192.168.5.16 pxc02 192.168.5.17 pxc03
Install UFW and configure necessary firewall rules:
sudo apt install ufw sudo ufw allow OpenSSH sudo ufw allow from 192.168.5.1/24 proto tcp to any port 3306 sudo ufw allow from 192.168.5.1/24 proto tcp to any port 4444 sudo ufw allow from 192.168.5.1/24 proto tcp to any port 4567 sudo ufw allow from 192.168.5.1/24 proto udp to any port 4567 sudo ufw allow from 192.168.5.1/24 proto tcp to any port 4568
Activate the UFW firewall:
sudo ufw enable
Verify UFW status:
sudo ufw status
Installing Percona XtraDB Cluster
Add Percona repository and install necessary dependencies:
sudo apt install wget gnupg2 lsb-release curl -y
Get repository package and install it:
wget -q https://repo.percona.com/apt/percona-release_latest.generic_all.deb sudo dpkg -i percona-release_latest.generic_all.deb
Update and set up Percona XtraDB Cluster repository:
sudo apt update sudo percona-release setup pxc80
Install Percona XtraDB Cluster:
sudo apt install percona-xtradb-cluster
During installation, you’ll be prompted to set up a root password. Ensure you select the “Use strong password encryption” option.
Verify MySQL service status:
sudo systemctl is-enabled mysql sudo systemctl status mysql
Login to MySQL to check root password:
sudo mysql -u root -p
Stopping Percona XtraDB Cluster Service
Stop MySQL service to configure Percona XtraDB Cluster:
sudo systemctl stop mysql
Copying SSL/TLS Certificates
SSL/TLS is automatically generated at the installation:
ls /var/lib/mysql/*.pem
Copy certificates from pxc01 to other nodes:
scp server-key.pem server-cert.pem ca.pem root@pax02:/var/lib/mysql scp server-key.pem server-cert.pem ca.pem root@pax03:/var/lib/mysql
Initializing Percona XtraDB Cluster on First Node
Edit MySQL configuration on pxc01:
sudo nano /etc/mysql/my.cnf
Add cluster settings and save:
[mysqld] datadir=/var/lib/mysql # [configurations here...]
Initialize Cluster:
systemctl start mysql@bootstrap.service
Verify cluster status:
show status like 'wsrep%';
Adding Node2 and Node3 to the Cluster
Repeat configuration and startup process for nodes pxc02 and pxc03 as in the pxc01 setup:
[mysqld] ...
Verify Cluster Status on each node with:
show status like 'wsrep%';
Testing Replication
Create a database and a table across the nodes to verify replication:
CREATE DATABASE percona; USE percona; CREATE TABLE example (node_id INT PRIMARY KEY, node_name VARCHAR(30)); INSERT INTO percona.example VALUES (1, 'pxc01');
Verify data is replicated by checking the table on another node:
SELECT * FROM percona.example;
Conclusion
You’ve successfully installed and configured a three-node Percona XtraDB Cluster on Debian 11, ensuring high availability and data security. Use these steps to expand your cluster or enhance its configurations. For further information, refer to the official Percona XtraDB Cluster documentation.
FAQ
- What is Percona XtraDB Cluster?
- Percona XtraDB Cluster is an open-source, high-availability clustering solution for MySQL databases, offering features like synchronous replication and multi-source replication.
- What are the prerequisites for setting up Percona XtraDB Cluster?
- You will need at least two Debian 11 servers with a non-root user having sudo or root privileges.
- Which ports need to be opened for Percona XtraDB Cluster?
- Ensure that ports 3306, 4444, 4567, and 4568 are open across your cluster environment for necessary MySQL and cluster communication.
- How can I verify if my Percona XtraDB Cluster is working?
- You can test replication by creating a database and table on one node, then confirming that they exist on another node.
- Where can I learn more about Percona XtraDB Cluster?
- For more details, consult the official Percona XtraDB Cluster documentation available at the Percona website.