HAProxy (High Availability Proxy) is a TCP/HTTP Load Balancer, which is a very popular option specially in the context of MySQL DB load balancing. There are plenty of materials available on the web related to this, but most of them are missing some key points. Hopefully this blog will capture most of them,

The Testing Environment

Ubuntu 10 (64-bit) VM instances, HAProxy V 1.4.22, MySQL 5.1 Server

(Totally there are 3 Ubuntu VM instances one loaded with HAProxy and the other two work as the DB nodes with MySQL 5.1 server installed with MySQL Master-Master replication)

Setting Up MySQL Server instances

Here we do setup two MySQL nodes to support MySQL Master-Master replication.

[P.Note: MySQL Master-Master Replication is also known as Master-Slave Replication]

1. Install MySQL Server 5.1 on both the MySQL Server instances (node1 and node2). You may use apt-get to install command if you are using Ubuntu server instances.

2. Edit /etc/mysql/my.cnf file on both instances.

Prior adding the following parameters, you are required to change the bind address by changing the bind-address parameter. Here in this scenario, both nodes are having host names “mysql1″ and “mysql2″ respectively and each of these host names need to be reflected in the /etc/hosts file as well.

Node 1

binlog_format=row

server-id = 1

sync_binlog = 1
log-bin = /var/log/mysql/bin.log
log-slave-updates
log-bin-index = /var/log/mysql/log-bin.index
log-error = /var/log/mysql/error.log
relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index
auto_increment_increment = 2
auto_increment_offset = 1
expire_logs_days = 5
max_binlog_size = 100M

master-host = <host of the node 2>
master-user = <user>
master-password = <password>
master-connect-retry = 60

replicate-do-db = <The DB to replicate>

Node 2

binlog_format=row

server-id = 2

sync_binlog = 1
log-bin = /var/log/mysql/bin.log
log-slave-updates
log-bin-index = /var/log/mysql/log-bin.index
log-error = /var/log/mysql/error.log
relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index
auto_increment_increment = 2
auto_increment_offset = 1
expire_logs_days = 5
max_binlog_size = 100M

master-host = <host of the node 1>
master-user = <user>
master-password = <password>
master-connect-retry = 60

replicate-do-db = <The DB to replicate>

3. In the Master-Master MySQL DB replication, it is required both MySQL server instances to talk to each other. The master-host, master-user and master-password parameters are used to specify those credentials. The specified user needs relevant privileges of the slave MySQL database. That should be given in each DB instance as below.

GRANT ALL ON *.* to 'root'@'%' IDENTIFIED BY ‘root’;

4. Now it is the time to test the replication. As a test, you can restore a database and see how it behaves. You may specify this database under replicate-do-db parameter in each my.cnf configurations.

Once all these are done, you may restart each MySQL Server instance by executing /etc/init.d/mysql restart command. If you wish, it is possible to check the the above replication status by executing the following command after logged into mysql.

mysql>> show slave status \G

If there are no replication errors you can happily assume that everything is under control. So now we can slowly move to the next step, which is configuring the HAProxy.

Setting up the HAProxy instance

1. Create a user and a user group called “haproxy” (You can give any name here)

useradd -g haproxy haproxy

2. Download and install the HAProxy source code.

Download the source code from: http://haproxy.1wt.eu/download/1.4/src/haproxy-1.4.22.tar.gz or from any preferred location.

>> tar –zxvf haproxy-1.4.22.tar.gz

>> cd haproxy-1.4.22

Sometimes, you may need to install gcc depending on your GNome distribution.

apt-get install gcc

2. Compile the source code.

make install

3. Create the haproxy.cfg (HAProxy Configuration file) under /etc/haproxy directory. (This directory you need to create)

global
       log 127.0.0.1 local0
       maxconn 4096
       user haproxy
       group haproxy
       daemon

defaults
       log global
       mode tcp
       option tcplog
       option dontlognull
       retries 3
       option redispatch
       maxconn 2000
        contimeout  3600000
        clitimeout  3600000
        srvtimeout  3600000
       stats enable
       stats scope .

frontend mysql_cluster
       bind mysql_lb:3307
       default_backend mysql_cluster

backend mysql_cluster
       mode tcp
       balance roundrobin
       option  mysql-check user haproxyUser
       server  mysql1 mysql1:3306 check
       server  mysql2 mysql2:3306 check

listen stats *:80
       mode http
       option httpclose
       balance roundrobin
       stats uri /
       stats realm Haproxy\ Statistics
       stats auth admin:admin

4. Create a MySQL user to check the HAProxy –> MySQL Server node connectivity. This needs to be created in each MySQL node.

GRANT ALL ON *.* to 'haproxyUser'@'%' IDENTIFIED BY ‘’;

You are required to keep the password empty in this occasion.

5. Start the HAProxy

/usr/local/sbin/haproxy -f /etc/haproxy/haproxy.cfg -V

If the above command runs without any errors, you are good to go.

Check the HAProxy Statistics

The users are given an GUI to check the statistics of load balancing instances, which are connected to the HAProxy node. Just type the HAProxy node URL, you will see something similar to the following. If everything is order you will see “green” highlighted rows of server nodes. If there are any issues they are highlighted by “red” and the issue also mostly given on the tooltip of “LastChk” column. Mostly the connectivty issues are a reason for the failures. For those please check the access privileges given for the mysql-check user for node instance.  As specified above, you are required to grant those privileges in order for remote access from the HAProxy.

You may use admin/admin to login to this page. These credentials should be specified under haproxy.cfg.

VN:F [1.9.22_1171]
Rating: 7.0/10 (4 votes cast)
VN:F [1.9.22_1171]
Rating: +4 (from 6 votes)
MySQL DB Load Balancing with HAProxy, 7.0 out of 10 based on 4 ratings