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 local2Clus
chroot /var/lib/haproxy
pidfile /var/run/
maxconn 4000
user haproxy
group haproxy
stats socket /var/lib/haproxy/stats

mode http
log global
option httplog
option dontlognull
option http-server-close
option forwardfor except
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

listen haproxy_mysql
bind localhost:3306
mode tcp
option tcpka
balance leastconn
option mysql-check user haproxy
server galera1 check
server galera2 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 center, 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 check id 1
server galera2 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 in the database. If you are following this example, you should do this commands in your database:

insert user (host,user) values('','haproxy');

This will allow simple binds. You are done.

Good Luck!

blog comments powered by Disqus


Read about IT, Migration, Business, Money, Marketing and other subjects.

Some subjects: FusionPBX, FreeSWITCH, Linux, Security, Canada, Cryptocurrency, Trading.