In this Linux tutorial, you will learn how to install Percona XtraDB Cluster on Rocky Linux 9 or other Red Hat based Linux distributions.
Table of Contents:
- What is Percona XtraDB Cluster?
- Environment Specification
- Updating your Linux Operating System
- Configure Network
- Configure Name Resolution
- Configure Linux Firewall
- Installing Third Party Yum Repositories
- Installing Percona XtraDB Cluster
- Configure Percona XtraDB Cluster
- Bootstrap the First PXC Node
- Adding Node to Percona XtraDB Cluster
- Testing Write-Set Replication
- Conclusion
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)
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 Course
Updating 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 Network:
In the first step, you must configure the network for your PXC nodes.
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 192.168.116.102 dbserver-02 dbserver-02.centlinux.com 192.168.116.103 dbserver-03 dbserver-03.centlinux.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.
Installing Third Party Yum Repositories:
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.
Installing Percona XtraDB Cluster:
All repositories has been installed successfully. Now, you can easily install PXC 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 the 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.
Adding Node to 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 PXC 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.
Testing 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.
Conclusion:
In this article, you have learned how to install Percona XtraDB Cluster on Rocky Linux 9 or other Red Hat based Linux distributions.
