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 3 of the article - Percona Toolkit Usage and 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.
No comments:
Post a Comment