I have a simple configuration of 2 MySQL being load balanced by HAProxy. For an unfortunate reason I need to use them in Passive\Active mode. So I thought I'd configure one DB as 'backup' and go to sleep. But I was wrong. Whenever I add the 'backup' to the server line HAProxy throws a communication link error (essentially saying 'no DB available" (with the 'backup' it works great). It just doesnt consider that server as a valid option any more…
I have tried this configuration:
listen mysql 10.0.0.109:3307
mode tcp balance roundrobin option httpchk server db01 10.0.0.236:3306 server db02 10.0.0.68:3306 backup
and also this configuration:
frontend mysql_proxy
bind 10.0.0.109:3307 default_backend mysql
backend mysql
mode tcp balance roundrobin option httpchk server db01 10.0.0.236:3306 server db02 10.0.0.68:3306 backup
Nothing worked!
Can anyone point me in the right direction?
Best Answer
Addressing the original question as to why the example does not work there are 2 things which stand out:
check
keyword is not added to theserver
declarations, which tells haproxy to use the health check directive on this server.httpchk
keyword is best used together with aport
on theserver
directive to define where the HTTP health check service is running. This method assumes you've setup a service somewhere that reports the health of the master.For example, I think something like this would work (untested)
In this example, haproxy will query the
/master_status
url on port 3305 on eachserver
. If the server returns a 2xx status code, the backend will be available through haproxy. Otherwise, it will be offline.To solve the problem of the health check service, you can use the rubygem
mysql_health
. You would run this service on each database server that participates in the haproxy cluster.https://github.com/osterman/mysql_health
To start the health check service, you would run something like:
There are many more command line options available. Use the
--help
argument for more details.