We use pair of HAProxy servers at work to route and balance all of our traffic. It is a highly reliable tool with little time or performance overhead. Yesterday I need to balance a load of MySql connections across several slave servers and so used HAProxy to do that. Since I did not find the exact recipe I need on the web here is my test configuration contribution to the MySql global distributed cookbook:
defaults mode http stats uri /haproxy-stats stats enable log global timeout connect 5000ms timeout client 50000ms timeout server 50000ms listen admin bind *:8000 mode http listen mysql bind *:3306 balance leastconn mode tcp option tcpka option srvtcpka option mysql-check user haproxy server mysql1 192.168.1.101:3306 weight 1 maxconn 200 check server mysql2 192.168.1.102:3306 weight 1 maxconn 200 check server mysql3 192.168.1.103:3306 weight 1 maxconn 200 check # END
Each MySql server used must have the haproxy user created using
create user 'haproxy'@'%'; flush privileges;
The wildcard can be replaced with the IP address of the HAProxy server.
I want to thank the folks at Homebrew for the haproxy formula.