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
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)
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:
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
We have provisioned three identical virtual machines with minimal Rocky Linux 9 operating system and following specifications.
If MariaDB database server is new for you, then you may attend online training Complete MySQL Database Administration Course
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.
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
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.
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.
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.
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.
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
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.
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.
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.
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.
Learn how to change Apache document root in Linux by following this step-by-step guide. Adjust…
Discover how to change Apache port in Linux easily. Follow our simple guide to modify…
Learn how to create a virtual host in Apache Server with this comprehensive guide. Set…
Discover 10 practical tasks for the RHCSA exam with step-by-step solutions. Boost your Linux skills…
Discover the ultimate Fail2ban configuration guide. Learn how to set up, customize, and optimize Fail2ban…
Explore the ultimate guide to VPS servers and learn everything about Virtual Private Servers, including…
This website uses cookies.