Setup Percona XtraDB Cluster on CentOS 7

Follow our step-by-step guide to setup Percona XtraDB Cluster on CentOS 7. Learn how to configure a high-availability MySQL cluster for optimal performance and reliability. #centlinux #linux #mysql

What is Percona XtraDB Cluster?

Percona XtraDB Cluster (PXC) is a free and open source, high availability solution for MySQL database servers. It integrates Percona Server and Percona XtraBackup with the Galera library to enable synchronous multi-master replication. PXC is supported by Percona Experts and it is distributed under Attribution-ShareAlike 2.0 Generic (CC BY-SA 2.0) license.

To setup Percona XtraDB Cluster, there can be two or more nodes. Each node has the same data that was synchronized across the Cluster. Each node is an actual MySQL database server (Percona, MariaDB, MySQL, etc.) and we can easily add it to the cluster.

In this article, we will install and setup Percona XtraDB Cluster on CentOS 7. We are using just two nodes here, but the configurations do not vary, even if you are scaling to hundreds of nodes.

Read Also: Install Percona XtraDB Cluster on Rocky Linux 9

Features in Percona XtraDB Cluster

PXC provides the following MySQL Cluster advantages.

• Cost-effective HA and scalability for MySQL
• Higher availability
• Multi-master replication
• Automatic node provisioning
• Percona XtraDB Cluster “strict-mode”
• Zero data Loss
• Increased read/write scalability
• ProxySQL load balancer
• ProxySQL-assisted Percona XtraDB Cluster maintenance mode
• Percona Monitoring and Management compatibility

For more details please visit PXC Documentation.

Recommended Courses

If you’re serious about mastering databases and boosting your tech career, The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert by Colt Steele is one of the most practical and highly-rated online courses available. Designed for both beginners and professionals, this course walks you step-by-step from the fundamentals of SQL to advanced database management skills using real-world examples.

Whether you’re a developer, data analyst, or aspiring system administrator, this bootcamp will give you the confidence to work with MySQL like a pro. [Enroll today] and start building skills that employers value.

Disclaimer: This post contains affiliate links. If you purchase through these links, I may earn a small commission at no additional cost to you.

MySQL Cluster Specification

We are using two CentOS 7 virtual machines with following specifications.

Node 1

• CPU – 3.4 Ghz (2 Cores)
• Memory – 1 GB
• Storage – 60 GB
• Hostname – percona-01.example.com
• IP Address – 192.168.116.204 /24
• Operating System – CentOS 7.6

Node 2

• CPU – 3.4 Ghz (2 Cores)
• Memory – 1 GB
• Storage – 60 GB
• Hostname – percona-02.example.com
• IP Address – 192.168.116.205 /24
• Operating System – CentOS 7.6

It is essential to equip your environment with reliable hardware that matches the demands of database clustering. For robust performance and seamless multitasking, consider the WD_BLACK SN850 NVMe SSD, a top seller renowned for its blazing read/write speeds, which drastically improves database transaction times.

Pair this with the ASUS ROG Strix B550-F Gaming motherboard, which supports stable multi-node networking and extensive connectivity, perfect for managing clustered environments. Investing in these proven products ensures your Percona XtraDB setup runs smoothly and efficiently.

Disclaimer: As an Amazon Associate, this blog earns from qualifying purchases at no extra cost to you. This helps support continued content creation and recommendations.

Allow PXC Service Ports in Linux Firewall

Connect with percona-01.example.com using ssh as root user.

To setup Percona XtraDB Cluster, we require following service ports for communication, therefore, we are allowing these service ports in CentOS 7 firewall.

firewall-cmd --permanent --add-port={3306,4444,4567,4568}/tcp
firewall-cmd --reload

Percona XtraDB Cluster is not fully compatible with SELinux, and it is recommended in PXC official documentation to put SELinux in permissive mode prior to installation.

setenforce 0
sed -i 's/^SELINUX=.*/SELINUX=permissive/g' /etc/selinux/config && cat /etc/selinux/config

Output:

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=permissive
# SELINUXTYPE= can take one of three values:
#     targeted - Targeted processes are protected,
#     minimum - Modification of targeted policy. Only selected processes are protected.
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted

Install Percona Yum Repository

Download and install rpm package for Percona yum repository as follows.

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

Output:

Loaded plugins: fastestmirror
percona-release-latest.noarch.rpm                        |  17 kB     00:00
Examining /var/tmp/yum-root-umE9yV/percona-release-latest.noarch.rpm: percona-release-1.0-13.noarch
Marking /var/tmp/yum-root-umE9yV/percona-release-latest.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package percona-release.noarch 0:1.0-13 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================
 Package            Arch      Version   Repository                         Size
================================================================================
Installing:
 percona-release    noarch    1.0-13    /percona-release-latest.noarch     20 k

Transaction Summary
================================================================================
Install  1 Package

Total size: 20 k
Installed size: 20 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : percona-release-1.0-13.noarch                                1/1
* Enabling the Percona Original repository
<*> All done!
The percona-release package now contains a percona-release script that can enable additional repositories for our newer products.

For example, to enable the Percona Server 8.0 repository use:

  percona-release setup ps80

Note: To avoid conflicts with older product versions, the percona-release setup command may disable our original repository for some products.

For more information, please visit:
  https://www.percona.com/doc/percona-repo-config/percona-release.html

  Verifying  : percona-release-1.0-13.noarch                                1/1

Installed:
  percona-release.noarch 0:1.0-13

Complete!

Build cache for all yum repositories.

yum makecache fast

We have installed Percona yum repository.

Now, we can easily setup Percona XtraDB Cluster using yum command.

yum install -y Percona-XtraDB-Cluster-57

Enable and start Percona database service.

systemctl enable --now mysql.service

Percona installer generates a temporary password for root user in /var/log/mysqld.log file.

We can obtain this password using grep command.

grep 'temporary password' /var/log/mysqld.log

Output:

2019-09-18T17:12:24.404976Z 1 [Note] A temporary password is generated for root@localhost: EAFR7kje,_HR

Login to Percona instance using this temporary password.

mysql -u root -p

Set a new password for root user.

ALTER USER 'root'@'localhost' IDENTIFIED BY '123';
flush privileges;
exit

Stop Percona database service.

systemctl stop mysql.service

Repeat the above steps on percona-02.example.com.

Configure PXC nodes for Write-set Replication

Setup Percona XtraDB Cluster configurations on percona-01.example.com.

vi /etc/percona-xtradb-cluster.conf.d/wsrep.cnf

Find and set following directives therein.

wsrep_cluster_address=gcomm://192.168.116.204,192.168.116.205
wsrep_node_address=192.168.116.204
wsrep_node_name=percona-01
wsrep_sst_auth="sstuser:Ahm3r"

Configure Percona XtraDB Cluster settings on percona-02.example.com.

vi /etc/percona-xtradb-cluster.conf.d/wsrep.cnf

Find and set following directives therein.

wsrep_cluster_address=gcomm://192.168.116.204,192.168.116.205
wsrep_node_address=192.168.116.205
wsrep_node_name=percona-02
wsrep_sst_auth="sstuser:Ahm3r"

Bootstrap the first node

We have configured both PXC nodes on CentOS 7.

Now its time to bootstap or initialize the first node of the MySQL Cluster.

First node of Percona XtraDB Cluster is the one that contains the data, that you want to replicate to other nodes.

Bootstrap the percona-01.example.com node using following command.

(Note: Please ensure that the mysql.service is stopped on all nodes.)

systemctl start mysql@bootstrap.service

Connect with Percona database instance.

mysql -u root -p

Check the environment variables related to cluster.

show status like 'wsrep_cluster%';

Output:

+----------------------------------+--------------------------------------+
| Variable_name                    | Value                                |
+----------------------------------+--------------------------------------+
| wsrep_cluster_conf_id            | 1                                    |
| wsrep_cluster_size               | 1                                    |
| wsrep_cluster_state_uuid         | 83b4278c-da37-11e9-82ae-1e52dc50d51c |
| wsrep_cluster_status             | Primary                              |
+----------------------------------+--------------------------------------+
4 rows in set (0.00 sec)

Before adding any node to our cluster, we must create a user for SST (Snapshot State Transfer) for complete synchronization of new nodes with cluster.

CREATE USER 'sstuser'@'%' IDENTIFIED BY 'Ahm3r';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'%';
FLUSH PRIVILEGES;
EXIT

Add Nodes in Percona XtraDB Cluster

Connect to percona-02.example.com using ssh as root user.

Start Percona service using systemctl command.

systemctl start mysql.service

If our configurations are correct then the percona-02 node should receive the SST automatically.

mysql -u root -p

Check the environment variables related to cluster.

show status like 'wsrep_cluster%';

Output:

+----------------------------------+-------------------------------------------+
| Variable_name                    | Value                                     |
+----------------------------------+-------------------------------------------+
| wsrep_cluster_conf_id            | 2                                         |
| wsrep_cluster_size               | 2                                         |
| wsrep_cluster_state_uuid         | 83b4278c-da37-11e9-82ae-1e52dc50d51c      |
| wsrep_cluster_status             | Primary                                   |
+----------------------------------+-------------------------------------------+
4 rows in set (0.00 sec)

You can see that the wsrep_cluster_size is now 2, it shows that the percona-02 node has joined our Database Cluster.

Verify Database Replication

We can verify replication by manipulating data on one node and check if it replicated on the other node.

Connect with percona-02.example.com using ssh as root user.

Connect to Percona database instance and execute following commands.

CREATE DATABASE RECIPES;
USE RECIPES;
CREATE TABLE TAB1 (CONTACT_ID INT PRIMARY KEY, CONTACT_NAME VARCHAR(20));
INSERT INTO TAB1 VALUES (1,'Ahmer');
INSERT INTO TAB1 VALUES (2,'Mansoor');
INSERT INTO TAB1 VALUES (3,'Salman');

Connect with percona-01.example.com using ssh as root user.

Connect with Percona database instance and query the data that we have inserted on percona-02 node.

mysql -u root -p

Perform some queries on database.

USE RECIPES;
SELECT * FROM TAB1;

Output:

+------------+--------------+
| CONTACT_ID | CONTACT_NAME |
+------------+--------------+
|          1 | Ahmer        |
|          2 | Mansoor      |
|          3 | Salman       |
+------------+--------------+
3 rows in set (0.00 sec)

It shows that, our Percona XtraDB Cluster is working fine.

Read Also: How to install ProxySQL on CentOS 7

Final Thoughts

Setting up Percona XtraDB Cluster on CentOS 7 is a fantastic way to enhance the performance, reliability, and scalability of your MySQL database environment. By following the steps outlined in this guide, you can achieve a robust, high-availability solution tailored to your needs.

Need expert AWS and Linux system administration? From cloud architecture to server optimization, I provide reliable and efficient solutions tailored to your needs. Hire me today!

FAQs

1. How do I add the Percona repository on CentOS 7?
Install the Percona repository RPM with:

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

Then enable the appropriate Percona release channel using percona-release commands.

2. What packages are needed to install Percona XtraDB Cluster on CentOS 7?
Install Percona-XtraDB-Cluster-server, Percona-XtraDB-Cluster-client, Percona-XtraDB-Cluster-shared, percona-toolkit, percona-xtrabackup, and galera packages using YUM.

3. How do I configure the my.cnf or wsrep.cnf file for cluster nodes?
Set the cluster name, node address, the Galera cluster addresses (all nodes), SST user credentials, and other Galera parameters under [mysqld] or the cluster-specific configuration files like /etc/percona-xtradb-cluster.conf.d/wsrep.cnf.

4. How do I bootstrap and start the Percona XtraDB Cluster on CentOS 7?
On the first node run:

systemctl start mysql@bootstrap.service

On other nodes run:

systemctl start mysql.service

This will initiate the cluster and join other nodes.

5. How do I verify the Percona XtraDB Cluster status?
Log into MySQL shell and run:

SHOW STATUS LIKE 'wsrep_cluster_size';
SHOW STATUS LIKE 'wsrep_cluster_status';

to confirm node count and cluster health.