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.
