MySql load balancing with HAProxy

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.

No comments: