How to setup HAProxy as Load Balancer for MariaDB on CentOS 7

https://www.howtoforge.com/tutorial/how-to-setup-haproxy-as-load-balancer-for-mariadb-on-centos-7

In this tutorial, I will guide you trough the installation of a MariaDB Galera cluster on CentOS 7 which has an HAProxy load balancer in front. The total setup consists of 4 servers. I will use 3 CentOS 7 servers for the database nodes, 2 nodes will be active and  1 acts as the backup node. The fourth server will be used for the HAProxy load balancer. For the balance algorithm, we use leastconn (but you can use other algorithms).
MariaDB is one of the most popular relational database servers made by the original developer of the MySQL Database server. MariaDB is a fork of MySQL that supports the XtraDB storage engine, InnoDB storage engine and a new storage engine called aria.
MariaDB Galera Cluster is a synchronous multi-master cluster for MariaDB that is available only on GNU/Linux-based operating systems. Galera Cluster supports only the XtraDB and InnoDB storage engines. It uses the Galera library for replication with automatic member control, automatic node joining and for reading and writing to the cluster nodes.
Prerequisite

• 4 CentOS 7 servers - 64bit:

           mariadb1
           192.168.1.132

           mariadb2
           192.168.1.133

           mariadb3
           192.168.1.134

           HAProxy
           192.168.1.135

• Root privileges on all four servers.

Step 1 - Install and Configure MariaDB Galera Cluster

In this step, we will install and configure the MariaDB Galera Cluster on 3 CentOS servers. We will install MariaDB-Galera server, then enable the firewall and open some ports for the database and other services. Then we will install the clustercheck script on each MariaDB server, so we can check the server status from the HAProxy.

A. Configure the hostnames and prepare the base system

Login to all database servers with your ssh account:

ssh mariadb1@192.168.1.132
ssh mariadb2@192.168.1.133
ssh mariadb3@192.168.1.134

Switch to the root user with the sudo command:

sudo su

Edit the hosts file on each MariaDB server and add all server IP addresses and hostnames of the other nodes:

vi /etc/hosts

See my /etc/hosts configuration below:

192.168.1.132   mariadb1
192.168.1.133   mariadb2
192.168.1.134   mariadb3

Save the file and exit.
Next, disable SELinux on each server because it causes issues with MariaDB Galera. Disable SELinux with this sed command:

sed -i -e 's/SELINUX=permissive/SELINUX=disabled/g' /etc/sysconfig/selinux

sed (stream editor): replace "SELINUX=permissive" with "SELINUX=disabled" on /etc/sysconfig/selinux file.

B. Install MariaDB Galera Server

In this step, we will install MariaDB Galera 10.0 which is available in the MariaDB repository.
Create a new .repo file in the yum.repos.d directory with the vi command:

vi /etc/yum.repos.d/mariadb.repo

Paste the MariaDB Galera repository details below and save it:

# MariaDB 10.0 CentOS repository list - created 2015-07-09 14:56 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Now install MariaDB Galera Server and Client, and some other packages that are required for this tutorial:

yum -y install MariaDB-Galera-server MariaDB-client galera rsync xinetd percona-toolkit precona-xtrabackup xinetd wget

Wait until the installation process finishes.

C. Configure Firewalld

We will turn on the default firewall of CentOS 7 called Firewalld. We will turn it on and open the TCP ports that are used by the services that we will install.
3306 = Used for MySQL/MariaDB client connections.
4567 = Galera Cluster replication traffic.
873     = Rsync ports.
4444 = For all other State Snapshot Transfer (SST).
9200 = xinetd - clustercheck.
Turn on the firewalld with this systemctl command:

systemctl start firewalld

Now open the ports with the firewall-cmd:

firewall-cmd --permanent --add-port=3306/tcp
firewall-cmd --permanent --add-port=4567/tcp
firewall-cmd --permanent --add-port=873/tcp
firewall-cmd --permanent --add-port=4444/tcp
firewall-cmd --permanent --add-port=9200/tcp

and reload firewalld:

firewall-cmd --reload

D. Configure MariaDB Galera Cluster

Go to the /etc/my.cnf.d/ directory and edit the server.conf file with vi:

cd /etc/my.cnf.d/
vi server.conf

Uncomment and add some configuration lines, you can see the details below:

wsrep_provider=/usr/lib64/galera/libgalera_smm.so   # Path to wsrep provider library
wsrep_cluster_address="gcomm://192.168.1.132,192.168.1.133,192.168.1.134"   # Group Node, add all MardiaDB Server IP
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
 
# Add some configuration below
# Cluster Name
wsrep_cluster_name="mariadb_cluster"
 
# Add Server IP - on mariadb2 = 192.168.1.133 - on mariadb3 = 192.168.1.134
wsrep_node_address="192.168.1.132"
 
# Replication Provider with rsync
wsrep_sst_method=rsync

Save the file and exit.
On the server mariadb1, let's bootstrap the cluster with the command below:

/etc/rc.d/init.d/mysql bootstrap

result:

Bootstrapping the cluster.. Starting MySQL.. SUCCESS!

On mariadb2 and mariadb3 server, start MariaDB normally:

/etc/init.d/mysql start

mariadb1 bootstraping.

mariadb2 and mariadb3 start.


MariaDB has been started on all nodes, now configure the root password for MariaDB on all servers with command below:

/usr/bin/mysql_secure_installation

E. Configure Clustercheck

Clustercheck is a useful bash script to make a proxy (ex: HAProxy) capable of monitoring the MariaDB server.
Go to the /tmp directory and download the script with wget:

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

Now make the script executable by changing the permission and move it to the /usr/bin/ directory:

chmod +x clustercheck
mv clustercheck /usr/bin/

Next, create a xinetd script for the clusterchek with the vi command in the "/etc/xinet.d/" directory:

vi /etc/xinetd.d/mysqlchk

Paste the configuration below:

# default: on
# description: mysqlchk
service mysqlchk
{
        disable = no
        flags = REUSE
        socket_type = stream
        port = 9200             # This port used by xinetd for clustercheck
        wait = no
        user = nobody
        server = /usr/bin/clustercheck
        log_on_failure += USERID
        only_from = 0.0.0.0/0
        per_source = UNLIMITED
}

Save and exit.
Add clustercheck to the services list:

vi /etc/services

Go to the line 10101, comment the services that use port 9200 and add a new line for mysqlchk/clustercheck.

mysqlchk        9200/tcp                # mysqlchk

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

Save the file and exit.
Then start the xinetd service:

systemctl start xinetd

To perform the clustercheck, you must create a new user in MySQL. Login to the MariaDB shell and create a new user with the name "clustercheckuser" and password "clustercheckpassword!".

mysql -u root -p
GRANT PROCESS ON *.* TO 'clustercheckuser'@'localhost' IDENTIFIED BY 'clustercheckpassword!' ;
exit;

At this stage, all MariaDB serves are synchronized.
Testing clustercheck:

/usr/bin/clustercheck

Make sure the results code is 200.

Step 2 - Install and Configure HAProxy

Login to the HAProxy server:

ssh haproxy1@192.168.1.135

Become root user and add the MariaDB server IP and hostname to the "/etc/hosts" file:

sudo su
vi /etc/hosts

HAProxy is available in the CentOS base repository, run this yum command to install it:

yum -y install haproxy

Then configure the log for HAProxy by editing the rsyslog configuration file:

vi /etc/rsyslog.conf

Uncomment the udp port:

# Provides UDP syslog reception
$ModLoad imudp
$UDPServerRun 51

Save the file and exit the editor.
Add the HAProxy configuration in rsyslog.d directory:

vi /etc/rsyslog.d/haproxy.conf

Paste configuration below:

local2.=info     /var/log/haproxy-access.log
local2.notice    /var/log/haproxy-info.log

Save the file and restart rsyslog:

systemctl restart rsyslog

Next, create a new HAProxy configuration file.
Go to the /etc/haproxy directory and backup the dafult haproxy configuration file.

cd /etc/haproxy/
mv haproxy.cfg haproxy.cfg.orig

Create new haproxy configuration file:

vi /etc/haproxy/haproxy.cfg

And paste the HAProxy configuration below:

global
        log 127.0.0.1   local2
        maxconn 1024
        user haproxy
        group haproxy
        daemon
        stats socket /var/run/haproxy.sock mode 600 level admin         # Make sock file for haproxy
 
defaults
        log     global
        mode    http
        option  tcplog
        option  dontlognull
        retries 3
        option  redispatch
        maxconn 1024
        timeout connect 5000ms
        timeout client 50000ms
        timeout server 50000ms
 
listen mariadb_cluster 0.0.0.0:3030
## MariaDB balance leastconn - the cluster listening on port 3030.
        mode tcp
        balance leastconn
        option httpchk
        server mariadb1 192.168.1.132:3306 check port 9200
        server mariadb2 192.168.1.133:3306 check port 9200
        server mariadb3 192.168.1.134:3306 check port 9200 backup       # Make mariadb3 as backup - automatic replication data
 
listen stats 0.0.0.0:9000
## HAProxy stats web gui running on port 9000 - username and password: howtoforge.
        mode http
        stats enable
        stats uri /stats
        stats realm HAProxy\ Statistics
        stats auth howtoforge:howtoforge
        stats admin if TRUE

Port 3030 is the load balanced MariaDB port, we can access the databases on all MariaDB servers with load balancer IP and that port.
Port 9000 is the port for the web-gui, it allows monitoring of HAProxy from the browser.
Now start firewalld, open ports 9000 and 3030, and then reload it:

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

Start HAProxy with the systemctl command:

systemctl start haproxy

Step 3 - Testing HAProxy and MariaDB

A. Replication testing

Login to the MariaDB server, access the MySQL shell and create a new database from mariadb1, then from mariadb2, and finally from mariadb3.

mysql -u root -p
create database this_mariadb1;  # Run this on mariadb1 server
create database this_mariadb2;  # Run this on mariadb2 server
create database this_mariadb3;  # Run this on mariadb3 server

Check the databases:

B. Access the MariaDB Server trough HAProxy

Now we access the MariaDB server cluster and databases trough the HAProxy IP on port 3030.
Before doing this test, enable remote access to the MariaDB server by creating a new user root with host "%".
Log into the MariaDB shell and create a new user "root" with password "aqwe123":

mysql -u root -p
GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY "aqwe123";

Next, access the 'mysql.user' database table trough the HAProxy server:

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

You can see the users of the database server by accessing the load balancer IP on port 3030. You can access the databases on the MySQL nodes by accessing the HAProxy IP on port 3030.
Another test of the server: the leastconn algorithm:

C. Login to the HAProxy Web-Gui

You can access the HAProxy monitoring server on port 9000, with username and password "howtoforge" to show the HAProxy web UI:
http://192.168.1.135:9000/stats

HAProxy has been installed successfully and you can see the MariaDB servers are monitored.

Conclusion

MariaDB Galera Cluster is a synchronous multi-master cluster which provides load balancing for MySQL databases. This is a good solution to build a powerful database system for your site. MariaDB Galera Cluster supports the xtradb and innodb storage engines, it provides automatic replication and allows automatic joining of new nodes. We can use HAProxy in front of the database cluster as a load balancer. The load balancer helps to distribute incoming requests to all databases. We use the bash script clustercheck to perform database monitoring from HAProxy. MariaDB Galera Cluster and HAProxy is one of the best solutions to build a high availability database system.