HAProxy for MySQL/MariaDB Load Balance and High Availability Cluster

After you have already setup your MySQL or MariaDB cluster in master-master mode, the next step is to know how to put this in high availability or load balance scheme. One of the many software you can use for this is the HAProxy project. HAProxy is a TCP/HTTP reverse proxy which is particularly suited for high availability environments. Indeed, it can: route HTTP requests depending on statically assigned cookies, spread load among several servers while assuring server persistence through the use of HTTP cookies, switch to backup servers in the event of a main server fails, accept connections to special ports dedicated to service monitoring, stop accepting connections without breaking existing ones, add, modify, and delete HTTP headers in both directions, block requests matching particular patterns, report detailed status to authenticated users from a URI intercepted by the applications.

So, I will explain how I did this configuration.

 HAProxy possesses a monitoring mechanism. This is the one that allows the proxy to know when a node is offline. We could use the TCP monitor, which it only verifies the TCP socket answers correctly, but in my personal opinion, this is not enough. We are going to use simple MySQL verification. This will allow HAProxy to do a simple bind to the database.

HA Configuration

This is my working /etc/haproxy/haproxy.cfg:

global
  log 127.0.0.1 local2Clus
  chroot /var/lib/haproxy
  pidfile /var/run/haproxy.pid
  maxconn 4000
  user haproxy
  group haproxy
  daemon
  stats socket /var/lib/haproxy/stats mode 666

defaults
  mode http
  log global
  option tcp-smart-accept
  option tcp_msart-connect
  option httplog
  option dontlognull
  option http-server-close
  option forwardfor except 127.0.0.0/8
  option redispatch
  retries 3
  timeout http-request 10s
  timeout queue 1m
  timeout connect 10s
  timeout client 1m
  timeout server 1m
  timeout http-keep-alive 10s
  timeout check 10s
  maxconn 3000

userlist STATUSERS
  group admin users admin
  user admin insecure-password YOUR_PASSWORD
  user stats insecure-password YOUR_PASSWORD

listen admin_page
  bind *:9600
  mode http
  stats enable
  stats refresh 60s
  stats uri /
  acl AuthOkay_ReadOnly http_auth(STATUSERS)
  acl AuthOkay_Admin http_auth_group(STATUSERS) admin
  stats http-request auth realm admin_page unless AuthOkay_ReadOnly

listen haproxy_mysql
  bind localhost:3306
  mode tcp
  timeout client 10800s
  timeout server 10800s
  option tcpka
  option allbackups
  balance leastconn
  option mysql-check user haproxy
  default-server inter 2s downinter 5s rise 3 fall 2 slowstart 60s maxconn 384 maxqueue 128 weight 100
  server galera1 172.26.52.1:3306 check
  server galera2 172.26.52.2:3306 check

This configuration will try to have two database server load balanced by having a fair use of the current connexions. This is okay if all servers are in the same data centre, but let's say one is in Paris and the other in Los Angeles. The PBX'es will need to point to the closer one, and if that is not available the following. To do this, change these lines:

balance first
server galera1 172.26.52.1:3306 check id 1
server galera2 172.26.52.2:3306 check id 10

This will tell the HAProxy to try first the server with the lowest ID.

This should be enough. Change this configuration to fit your needs.

MySQL/Maria Configuration

The last step is to add the proper user to the database. If you are following this example, you should do this commands in your database:

insert user (host,user) values('8.8.8.8','haproxy');
create user haproxy@'8.8.8.8';

This will allow simple binds. You are done.

Good Luck!