Optimize Database Clustering: Set Up Percona XtraDB on Debian 11

Percona XtraDB Cluster is a powerful open-source database clustering solution for MySQL. It offers high availability, robust performance, and data replication across multiple servers. In this article, we will guide you through the process of installing and configuring Percona XtraDB Cluster on Debian 11 servers. By following these steps, you’ll be able to create a reliable, high-performance MySQL server deployment that ensures data consistency and minimizes downtime.

Prerequisites

Before we begin, make sure you have the following prerequisites in place:

• Two or more servers running Debian 11
• A non-root user with sudo/root administrator privileges on all servers
• Basic knowledge of MySQL and database management

Step 1: Setup Hosts and Firewall

To start the installation process, you need to set up the /etc/hosts file on each server to ensure proper hostname resolution. Additionally, you must configure the firewall to allow the necessary ports for Percona XtraDB Cluster.

First, open the /etc/hosts file using a text editor:

sudo nano /etc/hosts

Add the following lines to the file, replacing the IP addresses and hostnames with those of your servers:

192.168.5.15 pxc01
192.168.5.16 pxc02
192.168.5.17 pxc03

Save and exit the file.

Next, install the ufw firewall package by running the following command:

sudo apt install ufw

Once installed, add the OpenSSH service to the firewall to ensure you don’t get locked out of your server:

sudo ufw allow OpenSSH

Now, open the necessary ports for Percona XtraDB Cluster:

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

Enable the firewall and verify its status:

sudo ufw enable
sudo ufw status

Step 2: Installing Percona XtraDB Cluster

Now that your servers are properly configured, it’s time to install Percona XtraDB Cluster. We’ll start by adding the Percona XtraDB repository and then proceed with the installation.

Begin by installing some basic dependencies:

sudo apt install wget gnupg2 lsb-release curl -y

Download and install the Percona XtraDB repository package:

wget -q https://repo.percona.com/apt/percona-release_latest.generic_all.deb
sudo dpkg -i percona-release_latest.generic_all.deb

Update the package index and set up the Percona XtraDB Cluster repository:

sudo apt update
sudo percona-release setup pxc80

Once the repository is enabled, install the percona-xtradb-cluster package:

sudo apt install percona-xtradb-cluster

During the installation process, you will be prompted to set the MySQL root password and choose the default authentication plugin. Follow the prompts and enter the required information.

To verify the installation, check the status of the MySQL service:

sudo systemctl is-enabled mysql
sudo systemctl status mysql

If the service is enabled and running without any errors, the installation was successful.

Step 3: Copying SSL/TLS Certificates

Percona XtraDB Cluster uses SSL/TLS certificates for encrypting client-server connections and replication traffic. During the installation, SSL/TLS certificates are automatically generated in the /var/lib/mysql directory.

To ensure all servers have the same certificates, you need to copy the CA and Server certificates from the first node ( pxc01 ) to the other nodes ( pxc02  and pxc03 ).

On the pxc01 server, navigate to the /var/lib/mysql directory:

cd /var/lib/mysql

Copy the CA and Server certificates to the other servers using the scp command:

scp server-key.pem server-cert.pem ca.pem root@pxc02:/var/lib/mysql
scp server-key.pem server-cert.pem ca.pem root@pxc03:/var/lib/mysql

Step 4: Initializing Percona XtraDB Cluster on the First Node

Now that the SSL/TLS certificates are in place, we can initialize Percona XtraDB Cluster on the first node ( pxc01 ).

Open the MySQL configuration file using a text editor:

sudo nano /etc/mysql/my.cnf

Add the following lines to the file, replacing the IP addresses with those of your servers:

[mysqld]
datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib/libgalera_smm.so

# Cluster connection URL contains the IPs of pxc01, pxc02, and pxc03
wsrep_cluster_address=gcomm://192.168.5.15,192.168.5.16,192.168.5.17

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# Using the MyISAM storage engine is not recommended.
default_storage_engine=InnoDB

# This InnoDB autoincrement locking mode is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node 1 address
wsrep_node_address=192.168.5.15

# SST method
wsrep_sst_method=xtrabackup-v2

# Cluster name
wsrep_cluster_name=pxc_cluster

#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCING

wsrep_provider_options="socket.ssl_key=server-key.pem;socket.ssl_cert=server-cert.pem;socket.ssl_ca=ca.pem"

[sst]
encrypt=4
ssl-key=server-key.pem
ssl-ca=ca.pem

Save and close the file.

Next, start the Percona XtraDB Cluster service:

sudo systemctl start mysql@bootstrap

To verify the status of the cluster, log in to the MySQL shell:

sudo mysql -u root -p

Enter your MySQL root password when prompted. Once logged in, run the following query to check the cluster status:

show status like 'wsrep%';

If everything is set up correctly, you should see the cluster size, incoming addresses, and the “Synced” state.

Step 5: Adding Node2 and Node3 to the Cluster

With the first node initialized, it’s time to add the second and third nodes ( pxc02  and pxc03 ) to the cluster. The process is similar to the initialization on the first node.

On the pxc02 server, open the MySQL configuration file:

sudo nano /etc/mysql/my.cnf

Add the following lines to the file, replacing the IP addresses with those of your servers:

[mysqld]

datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib/libgalera_smm.so

# Cluster connection URL contains the IPs of pxc01, pxc02, and pxc03
wsrep_cluster_address=gcomm://192.168.5.15,192.168.5.16,192.168.5.17

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# Using the MyISAM storage engine is not recommended
default_storage_engine=InnoDB

# This InnoDB autoincrement locking mode is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node #2 address
wsrep_node_address=192.168.5.16

# Cluster name
wsrep_cluster_name=pxc_cluster

# SST method
wsrep_sst_method=xtrabackup-v2

wsrep_provider_options="socket.ssl_key=server-key.pem;socket.ssl_cert=server-cert.pem;socket.ssl_ca=ca.pem"

[sst]
encrypt=4
ssl-key=server-key.pem
ssl-ca=ca.pem
ssl-cert=server-cert.pem

Save and close the file.

Start the MySQL service on pxc02:

sudo systemctl start mysql

Repeat the above steps for pxc03, modifying the IP addresses accordingly.

To verify that all nodes are part of the cluster, log in to the MySQL shell on any node and run the cluster status query:

show status like 'wsrep%';

If the cluster size is reflected correctly and all nodes are in the “Synced” state, the nodes have been successfully added to the cluster.

Step 6: Testing Replication

To ensure data replication is working correctly, we can perform a simple test by creating a new database and table on one node and checking if it is replicated to the other nodes.

Log in to the MySQL shell on pxc02:

sudo mysql -u root -p

Create a new database:

CREATE DATABASE testdb;

Switch to the newly created database:

USE testdb;

Create a table:

CREATE TABLE example (node_id INT PRIMARY KEY, node_name VARCHAR(30));

Insert some data into the table:

INSERT INTO percona.example VALUES (1, 'pxc01');
INSERT INTO percona.example VALUES (2, 'pxc02');
INSERT INTO percona.example VALUES (3, 'pxc03');

Log in to the MySQL shell on pxc03 and verify that the database and table have been replicated:

sudo mysql -u root -p
USE testdb;
SELECT * FROM example;

If the data is present on pxc03, replication is working successfully.

Conclusion

Congratulations! You have successfully installed and configured Percona XtraDB Cluster on Debian 11 servers. By following these steps, you have created a highly available and scalable MySQL server deployment that ensures data consistency and minimizes downtime.

Percona XtraDB Cluster offers a robust solution for businesses looking to achieve high performance and reliability in their database infrastructure. With the ability to add additional nodes as needed, you can easily scale your cluster to meet the demands of your growing business.

If you’re looking for a reliable cloud hosting provider to support your Percona XtraDB Cluster deployment, consider Shape.host. With their Cloud VPS services, you can benefit from scalable and secure hosting solutions tailored to your specific needs. Shape.host is committed to providing efficient and reliable cloud hosting to empower your business’s growth.