In my previous post I showed you How to deploy a MariaDb Cluster and now let’s see how to setup HAProxy in front of the cluster. But before, we should prepare the nodes.

Preparing the cluster

On each node, login to the database:

mysql -u root -p

Enter your password and press enter.

Now create the HAProxy user. The load balancer will use this user in order to check the availability of the nodes.

NODE 1 – Example IP 11.11.11.11

INSERT INTO user (Host,User) values ('11.11.11.11','haproxy'); 
FLUSH PRIVILEGES;

NODE 2 – Example IP 22.22.22.22

INSERT INTO user (Host,User) values ('22.22.22.22','haproxy'); 
FLUSH PRIVILEGES;

NODE 3 – Example IP 33.33.33.33

INSERT INTO user (Host,User) values ('33.33.33.33','haproxy'); 
FLUSH PRIVILEGES;

Setting up HAProxy

Installing HAProxy

Now install the packages on the load balancer server

sudo apt-get install mysql-client haproxy

Configuring HAProxy

Create and edit the configuration file:

sudo nano /etc/haproxy/haproxy.cfg

Paste a full example:

global
    log 127.0.0.1 local0
    log 127.0.0.1 local1 notice
    maxconn 1024
    user haproxy
    group haproxy
    daemon
defaults
    log global
    mode http
    option tcplog
    option dontlognull
    retries 3
    option redispatch
    timeout connect 5000
    timeout client 10000
    timeout server 10000
listen workerhaproxy 0.0.0.0:6000
    mode tcp
    balance source
    option tcpka
    option mysql-check user haproxy
    server db1 11.11.11.11:3306 check weight 1
    server db2 22.22.22.22:3306 check weight 1
    server db3 33.33.33.33:3306 check weight 1

Some notes on this example:

Remember to change your databases IP.
HAProxy will listeting on 6000 port.
Uses source balance option, what means each user will try to connect to the same database server. There are more options, like round-robin.
Please check the documentation in order to understand the configuration.

Enable and start HAProxy

Edit the file /etc/default/haproxy

sudo nano /etc/default/haproxy

Change ENABLED to 1, save and exit.

Now start HAProxy

service haproxy start

Testing

Now in your app or database client, test it connecting to the HAPRoxy server, on port 6000.

You can use root user or whatever user you had created on database.

Sources

http://cbonte.github.io/haproxy-dconv/configuration-1.4.html

http://www.sebastien-han.fr/blog/2012/04/08/mysql-galera-cluster-with-haproxy/

https://www.digitalocean.com/community/articles/how-to-use-haproxy-to-set-up-mysql-load-balancing–3