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.
This is my working /etc/haproxy/haproxy.cfg:
log 127.0.0.1 local2Clus
stats socket /var/lib/haproxy/stats mode 666
option forwardfor except 127.0.0.0/8
timeout http-request 10s
timeout queue 1m
timeout connect 10s
timeout client 1m
timeout server 1m
timeout http-keep-alive 10s
timeout check 10s
group admin users admin
user admin insecure-password YOUR_PASSWORD
user stats insecure-password YOUR_PASSWORD
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
timeout client 10800s
timeout server 10800s
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:
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.
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('126.96.36.199','haproxy');
create user haproxy@'188.8.131.52';
This will allow simple binds. You are done.
Good Luck!blog comments powered by Disqus