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

hostname:haproxy
address: 192.168.2.100

And two identical MySQL servers with those parameters:

hostname:mysql1
address: 192.168.2.1
hostname:mysql2
address: 192.168.2.2

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

create user 'haproxy'@192.168.2.100;
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 192.168.2.1 -e "SHOW DATABASES"
root@haproxy# mysql -u root -h 192.168.2.2 -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

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

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

listen mysql-cluster
bind 192.168.2.100:3306
mode tcp
option mysql-check user haproxy
balance roundrobin
server mysql1 192.168.2.1:3306 check
server mysql2 192.168.2.2:3306 check

listen webhaproxy
bind 192.168.2.100:8080
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 192.168.2.100 -u haproxy -e "show variables like 'server_id'"; done;