HAProxy us powerfull open source software which can load balance HTTP and TCP servers. In this example, we will configure HAProxy to load balance connections for MySQL. Let it first explain our small environment. We have one HAProxy with these parameters


And two identical MySQL servers with those parameters:


At this point we should create a user which can read mysql status. Do this for both MySQL servers.

create user 'haproxy'@;
flush privileges;

Now install mysql-client on haproxy server and check is it possible to connect to MySQL servers.

root@haproxy# yum -y install mysql-client
root@haproxy# mysql -u root -h -e "SHOW DATABASES"
root@haproxy# mysql -u root -h -e "SHOW DATABASES"

Install haproxy package on HAProxy server.

root@haproxy# yum -y install haproxy

Configure HAProxy server. This is example of /etc/haproxy/haproxy.cfg file

log local0
chroot /var/lib/haproxy
pidfile /var/run/haproxy.pid
maxconn 4000
user haproxy
group haproxy
stats socket /var/lib/haproxy/stats

log global
retries 2
timeout connect 3000
timeout server 5000
timeout client 5000
maxconn 20000

listen mysql-cluster
mode tcp
option mysql-check user haproxy
balance roundrobin
server mysql1 check
server mysql2 check

listen webhaproxy
mode http
stats enable
stats uri /
stats realm Strictly\ Private
stats auth webstats:pillow
Log in with username webstats and passwd pillow to check connection stats

Restart HAProxy service and check that is working properly

root@haproxy# /etc/init.d/haproxy restart
root@haproxy# chkconfig haproxy on
root@haproxy# for i in `seq 1 4`; do mysql -h -u haproxy -e "show variables like 'server_id'"; done;