Share on Social Media

Learn how to install Percona XtraDB Cluster on Rocky Linux 9 with this comprehensive guide. Follow step-by-step instructions to set up a robust and scalable database cluster for your infrastructure. #centlinux #linux #mysql

What is Percona XtraDB Cluster?:

Percona XtraDB is a storage engine for the MariaDB and Percona Server databases, and is intended as a drop-in replacement to InnoDB, which is the default engine in MySQL.

Up until version 10.1, MariaDB used Percona XtraDB in place of InnoDB as the default storage engine. As of MariaDB 10.2, InnoDB is the default again.

XtraDB incorporates InnoDB’s ACID-compliant design and MVCC architecture, and allows for a greater degree of tuning and scalability. The engine is also better suited to multi-core processing, which addresses some of the issues known to InnoDB.

A Percona XtraDB Cluster (PXC) is consist of two or more MySQL nodes, configured as a single unit for high availability. (Source: Wikipedia)

How Percona XtraDB Cluster works?

Percona XtraDB Cluster (PXC) is an open-source, high-availability and high-scalability MySQL clustering solution. It is based on the Galera replication technology, which enables synchronous multi-master replication for MySQL databases. Here’s how Percona XtraDB Cluster works:

  1. Multi-Master Replication: In a traditional MySQL setup, you typically have a single master server that handles all write operations, while one or more replica servers handle read operations. Percona XtraDB Cluster, on the other hand, allows multiple database nodes to act as both read and write masters simultaneously. This means that any node in the cluster can accept write operations, providing high availability and load distribution.
  2. Synchronous Replication with Galera: Percona XtraDB Cluster uses Galera replication technology to achieve synchronous replication among database nodes. Unlike asynchronous replication, where data consistency can lag behind due to replication delays, synchronous replication ensures that changes are propagated to all nodes in the cluster before a write operation is acknowledged as committed. This guarantees data consistency across all nodes in real-time.
  3. Quorum-based Decision Making: In Percona XtraDB Cluster, decisions about whether to commit a transaction are based on a quorum of nodes. A quorum is the minimum number of nodes required to agree on a transaction before it is considered committed. This ensures that even in the event of network partitions or node failures, the cluster can continue to operate and maintain data consistency.
  4. Automatic Node Recovery: Percona XtraDB Cluster includes built-in mechanisms for automatic node recovery. If a node goes offline due to network issues or hardware failures, the cluster automatically detects the failure and adjusts its behavior to continue serving requests without interruption. Once the failed node recovers, it can rejoin the cluster seamlessly, and the cluster will automatically resynchronize data with the recovered node.
  5. Load Balancing and Read Scaling: With Percona XtraDB Cluster, you can distribute read requests across multiple nodes in the cluster, providing load balancing and read scaling capabilities. This allows you to distribute read traffic evenly across all nodes, improving overall system performance and responsiveness.

Overall, Percona XtraDB Cluster provides a robust and scalable solution for building highly available MySQL database clusters. Its synchronous replication, automatic failover, and load balancing features make it well-suited for demanding production environments where data integrity, availability, and scalability are critical requirements.

Recommended Online Training: Complete MySQL Database Administration Course

4323756 577f 2show?id=oLRJ54lcVEg&offerid=1074530.4323756&bids=1074530

Environment Specification:

We have provisioned three identical virtual machines with minimal Rocky Linux 9 operating system and following specifications.

  • CPU – 3.4 Ghz (2 cores)
  • Memory – 2 GB
  • Storage – 20 GB
  • Operating System – Rocky Linux release 9.1 (Blue Onyx)

If MariaDB database server is new for you, then you may attend online training Complete MySQL Database Administration Courseshow?id=oLRJ54lcVEg&bids=1060093

Update your Linux Operating System:

To update your software packages, login to your Linux server as root user and execute following command at Linux terminal.

# dnf update -y

You may need to reboot your Linux operating system, if the above command updates your Linux Kernel.

# reboot

Check the Linux Operating System and Kernel versions.

# cat /etc/rocky-release && uname -r
Rocky Linux release 9.1 (Blue Onyx)
5.14.0-162.22.2.el9_1.x86_64

Repeat this step on each PXC node.

Configure Linux Networking:

In the first step, you must configure the network for your Percona XtraDB Cluster.

Login to your First PXC node (dbserver-01) as root user by using a ssh client.

Execute following set of commands to set a hostname, IP Address, Gateway and other network configurations for your Percona node.

# hostnamectl set-hostname dbserver-01.centlinux.com
# nmcli connection modify ens160 ipv4.addresses 192.168.116.101/24 ipv4.gateway 192.168.116.2 ipv4.method manual ipv4.dns 192.168.116.2
# nmcli c down ens160 ; nmcli c up ens160

Similarly, execute following commands on your Second PXC node (dbserver-02).

# hostnamectl set-hostname dbserver-02.centlinux.com
# nmcli connection modify ens160 ipv4.addresses 192.168.116.102/24 ipv4.gateway 192.168.116.2 ipv4.method manual ipv4.dns 192.168.116.2
# nmcli c down ens160 ; nmcli c up ens160

Finally, execute following commands on your Third PXC node (dbserver-03).

# hostnamectl set-hostname dbserver-03.centlinux.com
# nmcli connection modify ens160 ipv4.addresses 192.168.116.103/24 ipv4.gateway 192.168.116.2 ipv4.method manual ipv4.dns 192.168.116.2
# nmcli c down ens160 ; nmcli c up ens160

Configure Name Resolution:

If you have configured a DNS Server for your network, then add the A records of all three PXC nodes in your DNS Server.

Or you can use the local DNS resolver as well.

To configure Local DNS resolver, edit /etc/hosts file by using vim text editor.

# vi /etc/hosts

Add following lines in this file.

192.168.116.101 dbserver-01 dbserver-01.centlinux-com.preview-domain.com
192.168.116.102 dbserver-02 dbserver-02.centlinux-com.preview-domain.com
192.168.116.103 dbserver-03 dbserver-03.centlinux-com.preview-domain.com

You need to repeat above steps on all Percona XtraDB nodes.

Configure Linux Firewall:

Percona XtraDB Cluster requires following service ports for communication, therefore, you need to allow these service ports in your Linux firewall.

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

Repeat above step on each PXC node.

Install Percona Yum Repository:

Percona XtraDB Cluster software is available to download at their official website. Or you can get it from their offical yum repository, if installing on a Red Hat based Linux distribution.

During installation of Percona XtraDB Cluster, you will need software packages from EPEL and Percona official yum repositories.

Execute following dnf commands to install EPEL and Percona Official yum repositories.

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

Enable Percona XtraDB Cluster 8.0 repository by executing following Linux command.

# percona-release setup pxc-80
* Disabling all Percona Repositories
On Red Hat 8 systems it is needed to disable the following DNF module(s): mysql  to install Percona XtraDB Cluster
Do you want to disable it? [y/N] y
Disabling dnf module...
Last metadata expiration check: 0:07:31 ago on Sat 08 Apr 2023 09:14:33 PM PKT.
Unable to resolve argument mysql
Error: Problems in request:
missing groups or modules: mysql
DNF mysql module was disabled
* Enabling the Percona XtraDB Cluster 8.0 repository
* Enabling the Percona Tools repository
<*> All done!

Build cache for newly installed yum repositories.

# dnf makecache
Extra Packages for Enterprise Linux 9 - x86_64  3.1 kB/s | 4.6 kB     00:01
Percona Release release/noarch YUM repository   1.8 kB/s | 1.5 kB     00:00
Percona Server 8.0 release/x86_64 YUM repositor 178 kB/s | 851 kB     00:04
Percona Tools release/x86_64 YUM repository      96 kB/s | 290 kB     00:03
Rocky Linux 9 - BaseOS                          1.4 kB/s | 4.1 kB     00:02
Rocky Linux 9 - AppStream                       2.1 kB/s | 4.5 kB     00:02
Rocky Linux 9 - Extras                          1.1 kB/s | 2.9 kB     00:02
Metadata cache created.

This step is also need to repeat on each PXC node.

Install Percona XtraDB Cluster on Rocky Linux 9:

All repositories has been installed successfully. Now, you can easily install Percona XtraDB Cluster software by executing following command at Linux terminal.

# dnf install -y percona-xtradb-cluster

Start MySQL service by using systemctl command.

# systemctl start mysql

During installation, a temporary password of MySQL root user has been generated.

You can get this temporary password from /var/log/mysqld.log file with the help of grep command.

# grep 'temporary password' /var/log/mysqld.log
2023-04-08T16:30:08.237872Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: aqFFXEjkc5,r

Login to your MySQL server as root user and the temporary password.

# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 18
Server version: 8.0.31-23.2

Copyright (c) 2009-2022 Percona LLC and/or its affiliates
Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql>

Set a new password for your MySQL root user.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'P@ssw0rd';
Query OK, 0 rows affected (0.03 sec)

mysql> quit
Bye

Stop MySQL service, because it is a prerequisite of PXC initialization.

# systemctl stop mysql

Repeat above step to install Percona XtraDB Cluster software on all nodes.

Configure Percona XtraDB Cluster:

Edit MySQL configuration file by using vim text editor.

# vi /etc/my.cnf

Find and set following directives on each PXC node.

wsrep_provider=/usr/lib/galera4/libgalera_smm.so
wsrep_cluster_name=pxc-cluster
wsrep_cluster_address=gcomm://192.168.116.101,192.168.116.102,192.168.116.103
pxc_strict_mode=ENFORCING

You may also need to set node specific directives in this file.

For dbserver-01:

wsrep_node_name=dbserver-01
wsrep_node_address=192.168.116.101

For dbserver-02:

wsrep_node_name=dbserver-02
wsrep_node_address=192.168.116.102

For dbserver-03:

wsrep_node_name=dbserver-03
wsrep_node_address=192.168.116.103

In Percona XtraDB Cluster 8.0 SSL encryption is enabled by default. Therefore, you need to copy necessary SSL certificates from dbserver-01 to all other nodes.

Login to dbserver-01 as root user and execute following scp commands at Linux terminal to copy SSL certificates to other PXC nodes.

# scp /var/lib/mysql/{server*,ca*}.pem root@dbserver-02:/var/lib/mysql
# scp /var/lib/mysql/{server*,ca*}.pem root@dbserver-03:/var/lib/mysql

Bootstrap First PXC Node:

If you have perform the above steps correctly, then your Percona XtraDB Cluster is ready to initialize now.

Login to First node (dbserver-01) of Percona XtraDB Cluster, that contains the data for replication to other nodes.

Bootstrap the dbserver-01 node by using following command.

# systemctl start mysql@bootstrap.service

Execue following command at MySQL prompt.

mysql> show status like 'wsrep_local_state_comment%';
+---------------------------+--------+
| Variable_name             | Value  |
+---------------------------+--------+
| wsrep_local_state_comment | Synced |
+---------------------------+--------+
1 row in set (0.00 sec)

The value ‘Synced’ shows that the Percona cluster is fully connected and ready for write-set replication.

Similarly, execute following command at MySQL prompt.

mysql> show status like 'wsrep_cluster%';
+----------------------------+--------------------------------------+
| Variable_name              | Value                                |
+----------------------------+--------------------------------------+
| wsrep_cluster_weight       | 1                                    |
| wsrep_cluster_capabilities |                                      |
| wsrep_cluster_conf_id      | 1                                    |
| wsrep_cluster_size         | 1                                    |
| wsrep_cluster_state_uuid   | a5a110c8-d62a-11ed-af13-72dd16baa3b6 |
| wsrep_cluster_status       | Primary                              |
+----------------------------+--------------------------------------+
6 rows in set (0.01 sec)

Variable wsrep_cluster_size shows the current nodes in cluster.

It’s value is currently ‘1’, because don’t added a node to our Database Cluster.

Node Addition in Percona XtraDB Cluster:

Login to dbserver-02 as root user and start MySQL service.

# ssh root@dbserver-02
# systemctl start mysql

Login to MySQL server and check wsrep_cluster_size variable.

mysql> show status like 'wsrep_cluster%';
+----------------------------+--------------------------------------+
| Variable_name              | Value                                |
+----------------------------+--------------------------------------+
| wsrep_cluster_weight       | 2                                    |
| wsrep_cluster_capabilities |                                      |
| wsrep_cluster_conf_id      | 2                                    |
| wsrep_cluster_size         | 2                                    |
| wsrep_cluster_state_uuid   | a5a110c8-d62a-11ed-af13-72dd16baa3b6 |
| wsrep_cluster_status       | Primary                              |
+----------------------------+--------------------------------------+
6 rows in set (0.04 sec)

You can see that, the value is now ‘2’. It means now your Percona XtraDB cluster has two nodes.

Repeat the above step for dbserver-03.

# ssh root@dbserver-03
# systemctl start mysql

Now, check the PXC cluster size again.

mysql> show status like 'wsrep_cluster%';
+----------------------------+--------------------------------------+
| Variable_name              | Value                                |
+----------------------------+--------------------------------------+
| wsrep_cluster_weight       | 3                                    |
| wsrep_cluster_capabilities |                                      |
| wsrep_cluster_conf_id      | 3                                    |
| wsrep_cluster_size         | 3                                    |
| wsrep_cluster_state_uuid   | a5a110c8-d62a-11ed-af13-72dd16baa3b6 |
| wsrep_cluster_status       | Primary                              |
+----------------------------+--------------------------------------+
6 rows in set (0.08 sec)

It’s value is now ‘3’. It means, you have successfully added all three nodes to Percona XtraDB Cluster.

Test Write-Set Replication:

Login to MySQL server at dbserver-03 node.

Execute following command to create some database objects therein.

mysql> create database CENTLINUX;
Query OK, 1 row affected (0.01 sec)

mysql> use CENTLINUX;
Database changed

mysql> CREATE TABLE TAB1 (CONTACT_ID INT PRIMARY KEY, CONTACT_NAME VARCHAR(20));
Query OK, 0 rows affected (0.20 sec)

mysql> INSERT INTO TAB1 VALUES (1,'Ahmer');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO TAB1 VALUES (3,'Salman');
Query OK, 1 row affected (0.01 sec)

mysql> QUIT;
Bye

Now, login to MySQL server at dbserver-02 node, and verify that the objects that were created at dbserver-03 are replicated there.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| CENTLINUX          |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use CENTLINUX;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT * FROM TAB1;
+------------+--------------+
| CONTACT_ID | CONTACT_NAME |
+------------+--------------+
|          1 | Ahmer        |
|          3 | Salman       |
+------------+--------------+
2 rows in set (0.00 sec)

The above output shows that, the write-set replication is working fine.

Video: How to install Percona XtraDB Cluster on Linux

YouTube player

Final Thoughts

Setting up Percona XtraDB Cluster on Rocky Linux 9 brings scalability and reliability to your database infrastructure. By following the installation process carefully, you ensure a smooth setup that aligns with your system requirements. With Percona XtraDB Cluster in place, you can expect enhanced performance and fault tolerance, empowering your organization with a resilient database solution.

Leave a Reply

Your email address will not be published. Required fields are marked *