How to Setup Percona Cluster with HAproxy Loadbalancer on CentOS 7

We earlier shown how to setup MariaDB Galera cluster with HAproxy, and today we will do the similar setup with Percona's distribution of MySQL. Percona is the company that has deep expertise with MySQL and MongoDB, and they make their own distributions of those databases as well as storage engines for both databases. Today we will only concentrate on MySQL, and not on MongoDB offering of this company.

Setting up hosts, firewall and repositories

First start of by setting hosts file. We have three nodes for Percona cluster and one node for HAproxy. My hosts file on all 4 servers has those four lines:

10.17.0.8 centos-percona01
10.17.0.9 centos-percona02
10.17.0.10 centos-percona03
10.17.0.11 centos-haproxy

Next lets setup the firewalld on all hosts except HAproxy one. That one will require different settings and we will do that latter. First lets start firewalld on all three percona nodes.

systemctl start firewalld

Then we allow mysql service. Pecona is mysql distro so it uses same port as mysql.

firewall-cmd --zone=public --add-service=mysql --permanent

Next we add other needed ports:

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 --zone=public --add-port=9200/tcp --permanent

And reload the firewall

 firewall-cmd --reload

When that is done, next we need to install epel release

yum install epel-release

Next we install socat from EPEL repository

yum install socat

And then we remove mariadb-libs because it conflicts with percona

yum remove mariadb-libs

Installing and setting up Percona

We need to add repository containing percona

yum install https://www.percona.com/redir/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm

Now we can install percona cluster and all other dependancies

yum install Percona-XtraDB-Cluster-server-56 Percona-XtraDB-Cluster-client-56 Percona-XtraDB-Cluster-shared-56 percona-toolkit percona-xtrabackup Percona-XtraDB-Cluster-galera-3 rsync nc

And start mysql

systemctl start mysql

First thing we do on all mysql servers is to run mysql_secure_installation script. So lets do it.

mysql_secure_installation

You need to enter your new root password and answer y to all questions.

When finished, log in to your root account

mysql -u root -p

enter password

And create sstuser your cluster

mysql> create user sstuser@'%' identified by 'strongpassword';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all on *.* to sstuser@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

After this we must stop Mysql to edit the configuration files.

systemctl stop mysql

nano /etc/my.cnf

In configuration find those lines and make them look exactly like this, just change for your own password and change node name and node address to your hostname for each sever

wsrep_cluster_address = gcomm://centos-percona01,centos-percona02,centos-percona03
wsrep_provider = /usr/lib64/galera3/libgalera_smm.so

wsrep_slave_threads = 8
wsrep_cluster_name = Cluster Percona XtraDB
wsrep_node_name = centos-percona01
wsrep_node_address = centos-percona01
wsrep_sst_method = xtrabackup-v2
wsrep_sst_auth = sstuser:strongpassword

Note that the first line on first server (centos-percona01) can be empty, like this:

wsrep_cluster_address = gcomm://

After configuration have been done, we need to bootstrap the first node, and then normally start second and third.

On first server run

systemctl start mysql@bootstrap

on second and third run

systemctl start mysql

Next we need to test whether cluster is working.

mysql -u root -p

enter password

And then run this commands:

SHOW STATUS LIKE 'wsrep_local_state_comment';

show global status like 'wsrep_cluster_size';

They should get you output like this:

With this cluster setup is concluded.

HAproxy setup

First we need clustercheck installed on all cluster nodes so that the cluster is serviceable with HAproxy. Lets get the script with wget

wget https://raw.githubusercontent.com/olafz/percona-clustercheck/master/clustercheck

Script need to be made executable and moved to one of your $PATH directories.

chmod +x clustercheck

mv clustercheck /usr/bin/

Now we also need mysqlchk which is included in xinetd package:

yum install xinetd

Next we move create clustercheck user on database. We can type this on first node only

mysql -u root -p

mysql> GRANT PROCESS ON *.* TO 'clustercheckuser'@'localhost' IDENTIFIED BY 'clustercheckpassword!';

exit;

Next we can test if clustercheck works as expected:

[root@centos-percona01 ~]# clustercheck
HTTP/1.1 200 OK
Content-Type: text/plain
Connection: close
Content-Length: 40

Percona XtraDB Cluster Node is synced.

Next we move to config of xinetd, xinetd needs to be added to services list.

nano /etc/services

We use CTRL-W to find part where is port 9200, then we commend services that use that port and instead add new line. It needs to look like this:

mysqlchk 9200/tcp # mysqlchk
#wap-wsp 9200/tcp # WAP connectionless session service
#wap-wsp 9200/udp # WAP connectionless session service

After we are done we save. Note that all cluster nodes except HAproxy needs to have this done.

Now it is time to log in to our HAporoxy server. First we need to backup the haproxy configuration.

mv /etc/haproxy/haproxy.cfg /etc/haproxy/haproxy.cfg.bk

Then we will make new one from clean slate

nano /etc/haproxy/haproxy.cfg

We can copy HAproxy config from here except that these three lines need to be changed:

server centos-percona01 10.132.84.186:3306 check port 9200 inter 12000 rise 3 fall 3
server centos-percona02 10.132.84.141:3306 check port 9200 inter 12000 rise 3 fall 3
server centos-percona03 10.132.84.67:3306 check port 9200 inter 12000 rise 3 fall 3

The highlighted parts need to be changed with your hostnames and your addresses. Next we need to start firewalld on haproxy server and have allow ports that we need to use

systemctl start firewalld
firewall-cmd --permanent --add-port=9000/tcp
firewall-cmd --permanent --add-port=3030/tcp

After that we need to reload the firewall

firewall-cmd --reload

At last, start haproxy

systemctl start haproxy

The setup have done we now need to test.

Testing HAproxy

Lets just point the browser to the public ip adress of HAproxy server on port 9000:

All nodes are online. Next lets install the percona client on haproxy server so we can try to query the cluster from there.

yum install https://www.percona.com/redir/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm

yum install Percona-XtraDB-Cluster-client-56

And lets try to see if we can have a query from this haproxy server:

mysql -u root -p -h 10.132.83.13 -P 3306 -e "select Host, User, Password from mysql.user"

Conclusion

We have set up the 3 node cluster of Percona XtraDB load balanced by HAproxy. It is similar setup like last week article with MariaDB and Galera, but with Percona storage engine and without the WordPress. This Percona setup can be used to host very wide variety of data sets, among other tings you can again use it with WordPress, like in our previous article. With that we conclude this article, thank you for reading and have a nice day.