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
Table of Contents
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:
- 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.
- 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.
- 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.
- 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.
- 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
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
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
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 --reloadRepeat 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/mysqlBootstrap 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.
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.