How to Setup MariaDB Galera Cluster with HAproxy on CentOS 7

In todays article we will install MariaDB Galera cluster with HAproxy for load balanced MariaDB and wordpress. Galera is active-active clustering technology, meaning that it can support writes on all nodes which are then replicated across cluster. There is also active-passive clustering, where only node is writable. We will also install HAproxy for load balancing on our cluster. Install is going to be done on 5 CentOS 7 servers, three for MariaDB 10.1 Galera, one for Haproxy and one for wordpress. HAproxy works in such a way that it routes requests to each node in round robin mode, while presenting itself as a front end. Here we are using tradional way of clustering by having database servers sitting in private network and only webserver is facing to public ip address.

Setting up the Galera cluster

On all servers we need to install mysql. First we will add repository

echo '[mariadb] name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1' >> /etc/yum.repos.d/MariaDB.repo

Then we install mariadb, rsync and xinetd

sudo yum install mariadb-server rsync xinetd

We need to edit /etc/hosts, any editor will do but I will use nano

nano /etc/hosts

Out there ip and names of your hosts, for example

192.168.207.241 mariadb01
192.168.209.91 mariadb02
192.168.129.168 mariadb03

Make sure you use private ip of your hosts, if you use public ones you will need to encrypt traffic between them to secure your cluster.

After install MariaDB, edit

nano /etc/my.cnf.d/server.cnf

On all servers, you need to find [galera] section in file and make it look like this, save for ip addresses which are bold and should be changed

[galera] # Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so

#add your node ips here
wsrep_cluster_address="gcomm://192.168.207.241,192.168.209.91,192.168.129.168"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
#Cluster name
wsrep_cluster_name="linoxide_cluster"
# Allow server to accept connections on all interfaces.

bind-address=0.0.0.0

# this server ip, change for each server
wsrep_node_address="192.168.207.241"
# this server name, change for each server
wsrep_node_name="mariadb01"

wsrep_sst_method=rsync

Staring the Galera Cluster

After this have been configured you need to start the cluster

First on the mariadb01 node which is the master with this command

galera_new_cluster

Then on other two nodes with normal systemctl command:

systemctl start mariadb

Next we can verify that cluster is running:

mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"

Next thing would be to run the mysql_secure_installation script

mysql_secure_installation

Setting up the firewalld

Now when all three nodes are connected we can bring up firewalld and configure it.

systemctl start firewalld

Open the mariadb client and galera replication ports:

firewall-cmd --permanent --add-port=3306/tcp
firewall-cmd --permanent --add-port=4567/tcp

Open rsync port that we use for replication

firewall-cmd --permanent --add-port=873/tcp

Other important ports

firewall-cmd --permanent --add-port=4444/tcp
firewall-cmd --permanent --add-port=9200/tcp

Lastly reload the firewall

firewall-cmd --reload

Using xinetd and clustercheck

Clustercheck is useful script for monitoring the cluster. Lets install it by following commands

wget https://raw.githubusercontent.com/olafz/percona-clustercheck/master/clustercheck
chmod +x clustercheck
mv clustercheck /usr/bin/

Next we need to add mysqlchk to the services list. For that we edit services file:

nano /etc/services

Since it is long file, in nano type Ctrl-W and search for 9200. All services using that port needs to be commented, and new service needs to be added, so that part 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

When this is savaed and done we can start xinetd

systemctl start xinetd

All this need to be done on all nodes, and now only on master we need to add user for clustercheck.

mysql -u root -p

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

exit;

Next we can try clustercheck script

clustercheck

Setting up HAproxy

Login to your designated haproxy server. We are now installing the load balancer

Edit your /etc/hosts file to be something like this (with your ips, of course)

192.168.207.241 mariadb01
192.168.209.91 mariadb02
192.168.129.168 mariadb03
192.168.210.252 haproxy01

Next we can install haproxy

yum install haproxy

Next we need to edit rsyslog.conf

nano /etc/rsyslog.conf

Uncomment those two lines

# Provides UDP syslog reception
$ModLoad imudp
$UDPServerRun 514

Save and exit

echo 'local2.=info /var/log/haproxy-access.log
local2.notice /var/log/haproxy-info.log
' >> /etc/rsyslog.d/haproxy.conf

lets backup default haproxy configuration

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

Next we make new file with with nano

nano /etc/haproxy/haproxy.cfg

Use this pastebin as configuration, but change the ip addresses.

It is time to setup firewall on haproxy node

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

Then reload the firewall

firewall-cmd --reload

Setting up access the MariaDB from HAproxy

On mariadb cluster type:

mysql -u root -p

GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY "verystrongpassword";

Then we go to haproxy01 server  and try to access the database.

You must have the MariaDB-client installed, so lets first do that:

echo '[mariadb] name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1' >> /etc/yum.repos.d/MariaDB.repo

That was for adding repository, this command installs it

yum install MariaDB-client

And then lets test if

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

Make sure you enter verystrongpassword as password and not your regular root password.

Installing Wordpress

Lastly we will setup wordpress to use on our cluster. There is work to be done on mariadb01 node, on haproxy01 and on wp01 node.

On mariadb01

mysql -u root -p

CREATE DATABASE wordpress;

CREATE USER wordpressuser@localhost IDENTIFIED BY 'password';

GRANT ALL ON wordpress.* TO wordpressuser@'192.168.210.252' IDENTIFIED BY 'password';

On wp01 node set in /etc/hosts/

192.168.220.17 wp01
192.168.210.252 haproxy01

Also on haproxy01 add

192.168.220.17 wp01

Next we need to install required packages on wp01 node

yum install httpd php php-gd php-mysqlnd rsync

From here we need to use non-root account with sudo privileges. I don't have one, so I will create it:

useradd miki
usermod miki -aG wheel

passwd miki

Then log in as my user

su miki

Change dir to home

cd

And then download the latest version of wordpress and unpack it

wget http://wordpress.org/latest.tar.gz
tar xzvf latest.tar.gz

We will use rsync to copy the wordpress to apache dir

sudo rsync -avP ~/wordpress/ /var/www/html/
mkdir /var/www/html/wp-content/uploads

We also need to change ownership to apache user

sudo chown -R apache:apache /var/www/html/*

Configuring wordpress

WordPress is configured in wp-config.php file, there we need to set parameters like host address of database server, login credentials, database name. Lets backup the config file:

cd /var/www/html

cp wp-config-sample.php wp-config.php

And then we do editing:

nano wp-config.php

Only change those lines

define('DB_NAME', 'wordpress');

/** MySQL database username */
define('DB_USER', 'wordpressuser');

/** MySQL database password */
define('DB_PASSWORD', 'password');

/** MySQL hostname */
define('DB_HOST', '192.168.210.252:3030');

Lastly, we restart the apache webserver:

sudo systemctl restart httpd

Complete the install of the WordPress from the browser

We can go to public address of wordpress node like bellow to continue install process of word-press

Now we have the cluster set up with wordpress running

Conclusion

Here we have set up wordpress on top of Galera cluster loadbalanced by HAproxy. This is resilient solution for high load sites and although it takes a while to setup, it gives your site near complete insurance from downtime. All traffic between nodes is done by private ip addresses, and only one public address for the Worpress front end is used to access the site