MySQL DB Load Balancing with HAProxy
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.
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>
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.
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.
Comments are closed.