WORDPRESS - How to Setup MariaDB Galera Cluster with HAproxy on CentOS 7

How to Setup MariaDB Galera Cluster with HAproxy on CentOS 7

source : http://linoxide.com/cluster/mariadb-centos-7-galera-cluster-haproxy/

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.

MariaDB galera cluster HAproxy

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
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 mariadb01 mariadb02 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
# Mandatory settings
#add your node ips here
#Cluster name
# Allow server to accept connections on all interfaces.
# this server ip, change for each server
# this server name, change for each server

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
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

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!';
Next we can try clustercheck script

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) mariadb01 mariadb02 mariadb03 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
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 -P 3030 -e "select Host, User, Password from mysql.user"
ha-proxy access
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 USER wordpressuser@localhost IDENTIFIED BY 'password';
GRANT ALL ON wordpress.* TO wordpressuser@'' IDENTIFIED BY 'password';
On wp01 node set in /etc/hosts/ wp01 haproxy01
Also on haproxy01 add 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
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', '');
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
word press at last
Now we have the cluster set up with wordpress running


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. This would be all for this article, thank you for reading and have a good day.