Installing Percona XtraDB Cluster on CentOS 7

Welcome to another blog. This time I am here to show you guys how to setup percona XtraDB cluster. Ok, so what is percona XtraDB? XtraDB is a MySQL clustering solution, It ensures high availability, prevents downtime and data loss and provides linear scalability for a growing environment. When you execute a query, it is executed locally on the node. All data is available locally, no need for remote access. A good solution for scaling a read workload. You can put read queries to any of the nodes. Once you install percona XtraDB you don’t need to install MySQL separately, XtraDB will get it from the percona repository and you will be able to get the cluster ready after going through this article.

prerequisite:

To configure a percona XtraDB cluster on Linux Servers we need three servers, information is like the following:

server percona1: 192.168.56.110

server percona2: 192.168.56.113

Server percona3: 192.168.56.114

OS: CentOS 7

you can also go with two servers but the official document recommends an odd number of servers. e.g 3,5,7 and so on. just don’t go for any even number of nodes, they call it split-brain. A split-brain is a state in which the nodes lose contact with one another and then both try to take control of shared resources or provide simultaneously the cluster service

NOTE: We need root access on the node where we will be installing Percona XtraDB Cluster
(either logged in as a user with root privileges or be able to run commands with Sudo

Prechecks:

Add below entries in your /etc/hosts file ——> on all server (percona1,percona2,percona3)

192.168.56.110 percona1.localdomain percona1
192.168.56.113 percona2.localdomain percona2
192.168.56.114 percona3.localdomain percona3

Open firewall ports:

We need to open below ports on all server (percona1,percona2,percona3)
3306
4444
4567
4568

firewall-cmd --zone=public --add-service=mysql --permanent
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --zone=public --add-port=4567/tcp --permanent
firewall-cmd --zone=public --add-port=4568/tcp --permanent
firewall-cmd --zone=public --add-port=4444/tcp --permanent
firewall-cmd --zone=public --add-port=4567/udp --permanent
firewall-cmd --reload
firewall-cmd --list-all
[root@percona1 ~]# firewall-cmd --list-all
public (active)
target: default
icmp-block-inversion: no
interfaces: enp0s3 enp0s8
sources: 
services: ssh dhcpv6-client mysql
ports: 3306/tcp 4567/tcp 4568/tcp 4444/tcp 4567/udp
protocols: 
masquerade: no
forward-ports: 
source-ports: 
icmp-blocks: 
rich rules

Disable SELinux: ——->on all server(percona1,percona2,percona3)

[root@percona1 ~]# setenforce 0

This will put the SELinux in permissive mode for the current session but we need to disable it permanently.
edit /etc/sysconfig/selinux file set selinux variable as disabled on all servers (percona1,percona2,percona3):
vi /etc/sysconfig/selinux
SELINUX=disabled

All servers should be accessible to each other, In order to check the accessibility ping each server.

Instll below prereq RPM: ——->on all server(percona1,percona2,percona3)

epel-release
libev
socat

[root@percona1 ~]# yum -y install epel-release 
Installed:
epel-release.noarch 0:7-11

[root@percona1 yum.repos.d]# yum -y install libev 
Installed:
libev.x86_64 0:4.15-7.el7

[root@percona1 Desktop]# yum -y install socat 
Installed:
socat.x86_64 0:1.7.3.2-2.el7

Add Percona Repository: ——->on all server(percona1,percona2,percona3)

Now we will add the percona repository in all of the three servers (percona1,percona2,percona3):

[root@percona1 ~]# sudo yum -y install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
Installed:
percona-release.noarch 0:1.0-13 
Complete!

Install Percona-XtraDB-Cluster: ——->on all server(percona1,percona2,percona3)

[root@percona1 ~]# yum install -y Percona-XtraDB-Cluster-57
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
.
.
.

Installed:
Percona-XtraDB-Cluster-57.x86_64 0:5.7.27-31.39.1.el7 Percona-XtraDB-Cluster-shared-57.x86_64 0:5.7.27-31.39.1.el7 
Percona-XtraDB-Cluster-shared-compat-57.x86_64 0:5.7.27-31.39.1.el7

Dependency Installed:
Percona-XtraDB-Cluster-client-57.x86_64 0:5.7.27-31.39.1.el7 Percona-XtraDB-Cluster-server-57.x86_64 0:5.7.27-31.39.1.el7 
percona-xtrabackup-24.x86_64 0:2.4.17-1.el7 perl-DBD-MySQL.x86_64 0:4.023-6.el7 
perl-Digest.noarch 0:1.17-245.el7 perl-Digest-MD5.x86_64 0:2.52-3.el7 
qpress.x86_64 0:11-1.el7

Replaced:
mariadb-libs.x86_64 1:5.5.60-1.el7_5
Complete!

Let check out the Percona XtraDB is installed or not.

[root@percona1 ~]# yum search Percona-XtraDB

NOTE: Till here all above steps should be perfomed on all servers (percona1,percona2,percona3)
Configuring Nodes for Write-Set Replication
ON Percona1:

Let start the MySQL server and change the root password only on percona1 server

[root@percona1 ~]# systemctl start mysql
[root@percona1 ~]# grep password /var/log/mysqld.log
2019-12-11T10:35:12.135133Z 1 [Note] A temporary password is generated for root@localhost: hJ(l8ySe>f>E

Note : copy the password "hJ(l8ySe>f>E" we are going to change this password :

[root@percona1 ~]# mysql_secure_installation ------->on all server(percona1,percona2,percona3)

Securing the MySQL server deployment.
Enter password for user root:

The existing password for the user account root has expired. Please set a new password.

New password:

Re-enter new password:

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: 
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) :

... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : yes
Success.

Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : yes
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) :

... skipping.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : yes
Success.

All done!

Now stop the MySQL server on percona1.

[root@percona1 ~]# systemctl stop mysql

Now we will configure our replication, we will add replication information in my.cnf file.

Configure Replication Settings on percona1:
cat >>/etc/my.cnf<<EOF
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_name=perconacluster
wsrep_cluster_address=gcomm://192.168.56.110,192.168.56.113,192.168.56.114
wsrep_node_name=percona1
wsrep_node_address=192.168.56.110
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=perconarep:perconapasswd
pxc_strict_mode=ENFORCING
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
EOF

 

Now initialize the cluster by bootstrapping the first node. This must be the node with your main database, which will be used as the data source for the cluster in our case it is percona1 server

[root@percona1 ~]# systemctl start mysql@bootstrap

Before configuring replication we need to create a separate user for replication which will be used for the replication process. why we need replication user? when you add a new node to the cluster it dumps the current node configuration and transfers it to the second node for that reason we need a separate user.

NOTE: Make sure the user should be the same mentioned against the variable “wsrep_sst_auth” in the replication configuration used in my.cnf file.

[root@percona1 ~]#mysql -u root -p 
mysql>create user perconarep@'%' identified by 'perconapasswd';
mysql>grant all on *.* to perconarep@'%';
mysql>flush privileges;
ON Percona2:

Configure Replication Settings on percona2:

cat >>/etc/my.cnf<<EOF
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_name=perconacluster
wsrep_cluster_address=gcomm://192.168.56.110,192.168.56.113,192.168.56.114
wsrep_node_name=percona2
wsrep_node_address=192.168.56.113
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=perconarep:perconapasswd
pxc_strict_mode=ENFORCING
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
EOF

Now start the mysql and this will add this node to the cluster.

[root@percona2 ~]# systemctl start mysql
ON Percona3:

Configure Replication Settings on percona3:

cat >>/etc/my.cnf<<EOF
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_name=perconacluster
wsrep_cluster_address=gcomm://192.168.56.110,192.168.56.113,192.168.56.114
wsrep_node_name=percona3
wsrep_node_address=192.168.56.114
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=perconarep:perconapasswd
pxc_strict_mode=ENFORCING
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
EOF

Now start the mysql and this will add this node to the cluster.

[root@percona3 ~]# systemctl start mysql

you will observer such messages in mysql log (var/log/mysqld.log)

(percona2): State transfer from 0.0 (percona1) complete

1.0 (percona3): State transfer from 0.0 (percona1) complete

To make sure that the cluster has been initialized, run the following:

mysql> show status like 'wsrep%';
+----------------------------------+-------------------------------------------------------------+
| Variable_name | Value |
+----------------------------------+-------------------------------------------------------------+
| wsrep_local_state_uuid | 6d95cd36-1c1a-11ea-999a-83631204f6e3 |
| wsrep_protocol_version | 9 |
| wsrep_last_applied | 6 |
.
.
.
| wsrep_flow_control_recv | 0 |
| wsrep_flow_control_interval | [ 173, 173 ] |
| wsrep_flow_control_interval_low | 173 |
| wsrep_flow_control_interval_high | 173 |
| wsrep_flow_control_status | OFF |
.
.
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cert_index_size | 1 |
| wsrep_cert_bucket_count | 22 |
| wsrep_gcache_pool_size | 2384 |
.
.
| wsrep_incoming_addresses | 192.168.56.110:3306,192.168.56.114:3306,192.168.56.113:3306 |
| wsrep_cluster_weight | 3 |
| wsrep_desync_count | 0 |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 0/0/0/0/0 |
| wsrep_evs_state | OPERATIONAL |
| wsrep_gcomm_uuid | 1d16f574-1c1b-11ea-b5dd-574c9a653584 |
| wsrep_cluster_conf_id | 3 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | 6d95cd36-1c1a-11ea-999a-83631204f6e3 |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_index | 0 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <[email protected]> |
| wsrep_provider_version | 3.39(rb3295e6) |
| wsrep_ready | ON |
+----------------------------------+-------------------------------------------------------------+
71 rows in set (0.00 sec)

As you can see that the cluster size is 3 “wsrep_cluster_size | 3” and you can also see the IP of our three nodes
wsrep_incoming_addresses | 192.168.56.110:3306,192.168.56.114:3306,192.168.56.113:3306

Test our cluster:

I am creating a database on percona 1 and then we will check on percoan 3 that it has been replicated.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.03 sec)
mysql> CREATE DATABASE perconatest;
Query OK, 1 row affected (0.03 sec)

Now let’s check on percona3 we will see that ‘perconatest’ DB will be created

[root@percona3 ~]# mysql -uroot -p -e "show databases"
Enter password: 
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| perconatest |
| performance_schema |
| sys |
+--------------------+

About Mughees Ahmed

Over 2-year experience of Administration in Oracle, SQL Server, and MySQL databases using various tools and technologies. Keen on learning new database technologies having very good analytical skills. Working knowledge of Red Hat Linux, UNIX, Solaris and Windows Server 2012 is a valuable addition to my knowledge desk. KNOWLEDGE & SKILLS • Oracle Database Server 10g/11g/12c. • Oracle RAC, Data guard. • Oracle Golden Gate (Oracle to Oracle, Oracle to MySQL and MySQL to Oracle) • Oracle Enterprise Manager Grid Control, Toad, SQL developer. • SQL Server 2005/2008/2012/2016. • SQL Server Failover clustering, mirroring & log shipping, Always On availability groups. • MySQL 5 Administration, MySQL Workbench, MySQL Enterprise Monitor, SQLyog • MySQL NDB Cluster Installation,Administration. • MySQL Asynchronous/Semi-synchronous replication. • Oracle OEM Contact me on [email protected]

Leave a Reply

Your email address will not be published. Required fields are marked *