August 20, 2021

Installing Percona XtraDB Cluster (PXC) 8

Percona XtraDB Cluster (PXC) 8.0 Installation/Configuration - Part 1


Here are the installation/deployment and configuration steps of PXC 8 (Percona XtraDB Cluster) for MySQL, with Galera cluster, on three nodes in a multi-master replication, on Oracle Linux (OEL)/Red Hat (RHEL)/CentOS/Ubuntu/Debian.

Multi-master (active-active) replication allows us to write operations on any node, even if one node fails, we can work on the other nodes. 

  Role Hostname       IP address
master 1     percona1     192.168.1.4
master 2     percona2     192.168.1.5
master 3     percona3     192.168.1.6

Part 2 of the article - ProxySQL 2 configuration/installation
Part 4 of the article - Percona Xtrabackup Backup & Recovery
Part 5 of the article - Percona Monitoring and Management


Step I. Common Percona XtraDB Cluster installation steps (on all nodes)

Perform below 6 steps on all nodes, in PXC cluster.

i) Update /etc/hosts with all 3 node details
# vi /etc/hosts
192.168.1.4 percona1 percona1.localdomain
192.168.1.5 percona2 percona2.localdomain
192.168.1.6 percona3 percona3.localdomain

ii) Open firewall ports
Percona requires the following ports for the cluster nodes. Create the appropriate firewall rules on Percona nodes.
3306
4444
4567
4568

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

iii) Enable SELinux (Security-Enhanced Linux)
Make sure SELinux is enabled on all Percona nodes.

# cat /etc/sysconfig/selinux
# sestatus

/etc/selinux/config file controls if SELinux is disabled or enabled, and if enabled, whether SELinux operates in enforcing mode or permissive mode.

iv) Add Percona repository
Add percona repository to Linux Distribution, so we can download the latest version of PXC.
For Ubuntu/Debian Operating Systems, please use apt-get instead of yum/dnf.

# yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
# percona-release enable-only pxc-80 release
# percona-release enable tools release

v) Installing PXC (Percona XtraDB Cluster) from yum/dnf repo
Install the Percona XtraDB Cluster software and including all dependences.

# yum module disable mysql -y
# yum list all | grep percona
# yum install percona-xtradb-cluster

vi) securing MySQL deployment
We need to perform a post-installation task to secure the MySQL deployment. Run the following command and answer the questions accordingly:

# mysql_secure_installation



Step II. 
Configuring Percona XtraDB Cluster on the first node

i) Initial set up
Start the MySQL service for PXC node1.
# systemctl start mysql

To get the temporary root mysql password you need to run the below command.
# grep password /var/log/mysqld.log

ii) Change root password
# mysql -u root -p
Enter the password you got in the above step.

Change the root user password to the MySQL database.
MySQL> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Percona@pxc8';

Verify wsrep parameters in the database.
MySQL> show variables like '%wsrep%';

Stop MySQL server on the first node.
# systemctl stop mysql

iii) Configure PXC Write-Set Replication (WSP) settings
Update my.cnf with custom values according to your requirement.

# cp /etc/my.cnf /etc/my.cnf.orig
# vi /etc/my.cnf

# Cluster name
wsrep_cluster_name=democluster
# Cluster connection URL contains the IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://192.168.1.4,192.168.1.5,192.168.1.6
# First node hostname
wsrep_node_name=percona1
# First node IP address
wsrep_node_address=192.168.1.4
# pxc_encrypt_cluster_traffic is ON by default, in PXC 8. I don't have encryption keys/certificates, so turning OFF encryption
pxc_encrypt_cluster_traffic=OFF

Initialization parameter wsrep_sst_auth is NOT required in Percona XtraDB Cluster version 8. And no need to create a replication user also, because Percona XtraDB Cluster 8.0 automatically creates the system user mysql.pxc.internal.session. During SST, the user mysql.pxc.sst.user and the role mysql.pxc.sst.role are created on the donor node.

iv) Bootstrap the cluster
The below command will start the first node and bootstrap the cluster.
# systemctl start mysql@bootstrap.service
# systemctl status mysql@bootstrap.service
# tail -f /var/log/mysqld.log



Step III. Configuring Percona XtraDB Cluster on Second/Third node

i) Configuring PXC Cluster with WSP (Write-Set Replication)
Update my.cnf with custom values as per your requirement, on all other nodes.

# cp /etc/my.cnf /etc/my.cnf.orig
# vi /etc/my.cnf

# Cluster name
wsrep_cluster_name=democluster
# Cluster connection URL contains the IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://192.168.1.4,192.168.1.5,192.168.1.6
# Second/Third node hostname
wsrep_node_name=percona2            percona3, on third node
# Second/Third node IP address
wsrep_node_address=192.168.1.5      192.168.1.6, on third node
# pxc_encrypt_cluster_traffic is ON by default, in PXC 8. I don't have encryption keys/certificates, so turning OFF encryption
pxc_encrypt_cluster_traffic=OFF

ii) Start MySQL to join the cluster
# systemctl start mysql
# systemctl status mysql

# tail -f /var/log/mysqld.log

After MySQL server has been started, the new nodes should receive SST automatically. State Snapshot Transfer (SST) is a full data copy from one node (donor) to the joining node (joiner).

iii) Bouncing MySQL database server on first node
The first node was started in bootstrap mode, you can stop it and start the first node in normal mode.

# systemctl stop mysql@bootstrap.service
# systemctl start mysql
# systemctl status mysql

iv) Verify cluster setup
Cluster status can now be checked on all nodes.

# mysql -uroot -pPercona@pxc8 -e "select * from mysql.wsrep_cluster_members;"
+--------------------------------------+--------------------------------------+-----------+-----------------------+
| node_uuid                            | cluster_uuid                         | node_name | node_incoming_address |
+--------------------------------------+--------------------------------------+-----------+-----------------------+
| 275888d3-fde3-11eb-ba65-6b7198e764f9 | d6a6b71a-fda4-11eb-89f2-f7703766b53c | percona1  | 192.168.1.4:3306      |
| 41261eff-fde9-11eb-b83c-963c55b86b90 | d6a6b71a-fda4-11eb-89f2-f7703766b53c | percona3  | 192.168.1.6:3306      |
| cb8695c7-fde3-11eb-b8a1-d6cf0e345170 | d6a6b71a-fda4-11eb-89f2-f7703766b53c | percona2  | 192.168.1.5:3306      |

The above output shows that all 3 nodes are part of the cluster.

v) Verify default parameter values
By default, you will be seeing below parameter values (and along with other default parameters).

# cat /etc/my.inf
wsrep_provider=/usr/lib64/galera4/libgalera_smm.so
wsrep_sst_method=xtrabackup-v2
pxc_strict_mode=ENFORCING
binlog_format=ROW
innodb_autoinc_lock_mode=2

Step IV. Testing Replication on Percona XtraDB Cluster

To test the replication, let us create a new database on the second node, create a table for that database on the third node, add one record to the table on the first node, and verify on the third or second node.

i) Create a new database on the second node

MySQL@percona2> CREATE DATABASE satyadb;

ii) Create a table on the third node

MySQL@percona3> USE satyadb;
MySQL@percona3> CREATE TABLE employee (emp_id INT PRIMARY KEY, emp_name VARCHAR(50));

iii) Insert records on the first node

MySQL@percona1> INSERT INTO satyadb.employee VALUES (111, 'percona');

iv) Retrieve all the rows from the table, on the second or third node:
MySQL@percona2> SELECT * FROM satyadb.employee;
+---------+-----------+
| emp_id  | emp_name |
+---------+-----------+
|     111 | percona  |
+---------+-----------+


Percona XtraDB Cluster 8.0 is based on Percona Server for MySQL 8.0 embedded with Galera writeset replication API and Galera replication (version 4) library, to form a highly available active-active (master-master) replication for Oracle MySQL based database server.
Percona XtraDB Cluster, a database clustering solution, ensures high availability, prevents downtime and data loss, and provides linear scalability.

Related MySQL Articles:  ProxySQL 2 installation setup   PMM server and client installation

No comments:

Post a Comment