How to setup the load balancer HAProxy in front of MariaDb Cluster


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
INSERT INTO user (Host,User) values ('','haproxy'); 
  • NODE 2 – Example IP
INSERT INTO user (Host,User) values ('','haproxy'); 
  • NODE 3 – Example IP
INSERT INTO user (Host,User) values ('','haproxy'); 

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:

    log local0
    log local1 notice
    maxconn 1024
    user haproxy
    group haproxy
    log global
    mode http
    option tcplog
    option dontlognull
    retries 3
    option redispatch
    timeout connect 5000
    timeout client 10000
    timeout server 10000
listen workerhaproxy
    mode tcp
    balance source
    option tcpka
    option mysql-check user haproxy
    server db1 check weight 1
    server db2 check weight 1
    server db3 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


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.


David Burgos

Read more posts by this author.