// Tutorial //

How To Use HAProxy to Set Up MySQL Load Balancing

Published on December 2, 2013
Default avatar
By Jesin A
Developer and author at DigitalOcean.
How To Use HAProxy to Set Up MySQL Load Balancing

Prelude


HAProxy is an open source software which can load balance HTTP and TCP servers. In the previous article on HAProxy we configured load balancing for HTTP and in this one we’ll do the same for MySQL. All your MySQL servers have to be configured to perform Master-Master replication as load balancing involves both reading and writing to all the backends.

The following three droplets will be used in this article:

Droplet 1 - Load Balancer Hostname: haproxy OS: Ubuntu Private IP: 10.0.0.100

Droplet 2 - Node 1 Hostname: mysql-1 OS: Debian 7 Private IP: 10.0.0.1

Droplet 2 - Node 2 Hostname: mysql-2 OS: Debian 7 Private IP: 10.0.0.2

Before proceeding, make sure all MySQL servers are up, running and are properly replicating database writes.

Prepare MySQL Servers


We need to prepare the MySQL servers by creating two additional users for HAProxy. The first user will be used by HAProxy to check the status of a server.

root@mysql-1# mysql -u root -p -e "INSERT INTO mysql.user (Host,User) values ('10.0.0.100','haproxy_check'); FLUSH PRIVILEGES;"

A MySQL user is needed with root privileges when accessing the MySQL cluster from HAProxy. The default root user on all the servers are allowed to login only locally. While this can be fixed by granting additional privileges to the root user, it is better to have a separate user with root privileges.

root@mysql-1# mysql -u root -p -e "GRANT ALL PRIVILEGES ON *.* TO 'haproxy_root'@'10.0.0.100' IDENTIFIED BY 'password' WITH GRANT OPTION; FLUSH PRIVILEGES"

Replace haproxy_root and password with your own secure values. It is enough to execute these queries on one MySQL master as changes will replicate to others.

Install MySQL Client


MySQL client has to be installed on the HAProxy droplet to test connectivity.

root@haproxy# apt-get install mysql-client

Now try executing a query on one of the masters as the haproxy_root user.

root@haproxy# mysql -h 10.0.0.1 -u haproxy_root -p -e "SHOW DATABASES"

This should display a list of MySQL databases.

Installing HAProxy


On the HAProxy server install the package.

root@haproxy# apt-get install haproxy

Enable HAProxy to be started by the init script.

root@haproxy# sed -i "s/ENABLED=0/ENABLED=1/" /etc/default/haproxy

To check if this change is done properly execute the init script of HAProxy without any parameters.

root@haproxy:~# service haproxy
Usage: /etc/init.d/haproxy {start|stop|reload|restart|status}

Configuring HAProxy


Rename the original configuration file

mv /etc/haproxy/haproxy.cfg{,.original}

Create and edit a new one

nano /etc/haproxy/haproxy.cfg

The first block is the global and defaults configuration block.

global
    log 127.0.0.1 local0 notice
    user haproxy
    group haproxy

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

More information about each of these options are covered in this article. Since we’ve told HAProxy to send log messages to 127.0.0.1 we have to configure rsyslog to listen on it. This has too been covered in the same article under Configure Logging for HAProxy.

Moving to the main configuration part.

listen mysql-cluster
    bind 127.0.0.1:3306
    mode tcp
    option mysql-check user haproxy_check
    balance roundrobin
    server mysql-1 10.0.0.1:3306 check
    server mysql-2 10.0.0.2:3306 check

Unlike HTTP load balancing HAProxy doesn’t have a specific “mode” for MySQL so we use tcp. We’ve set HAProxy to listen only on the loopback address (assuming that application is on the same server) however if your application resides on a different droplet make it listen on 0.0.0.0 or the private IP address.

We need one more configuration block to see the statistics of load balancing. This is completely optional and can be omitted if you don’t want stats.

listen 0.0.0.0:8080
    mode http
    stats enable
    stats uri /
    stats realm Strictly\ Private
    stats auth A_Username:YourPassword
    stats auth Another_User:passwd

Replace the usernames and passwords in “stats auth”. This will make HAProxy listen on port 8080 for HTTP requests and the statistics will be protected with HTTP Basic Authentication. So you can access stats at

http://<Public IP of Load Balancer>:8080/

Once you’re done configuring start the HAProxy service.

service haproxy start

Use the mysql client to query HAProxy.

root@haproxy# mysql -h 127.0.0.1 -u haproxy_root -p -e "SHOW DATABASES"

The “-h” option has to be present with the loopback IP address. Omitting it or using localhost will make the MySQL client connect to the mysql.sock file which will fail.

Testing Load Balancing and Failover


To check if load balancing is working query the server_id variable twice or more.

root@haproxy# mysql -h 127.0.0.1 -u haproxy_root -p -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
root@haproxy# mysql -h 127.0.0.1 -u haproxy_root -p -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+

This demonstrates roundrobin load balancing with equal weights, we’ll now change the weight for mysql-2 and see the results.

nano /etc/haproxy/haproxy.cfg

server mysql-2 10.0.0.2:3306 check weight 2

Reload to apply this change.

service haproxy reload

Query for the server_id multiple times.

root@haproxy:~# for i in `seq 1 6`
do
mysql -h 127.0.0.1 -u haproxy_root -ppassword -e "show variables like 'server_id'"
done

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+

Now load balancing works in the ratio of 1:2 with one-thirds of the requests going to mysql-1 and two-thirds going to mysql-2.

Fail a MySQL server either by stopping the service

root@mysql-1# service mysql stop

or bringing the interface down.

root@mysql-1# ifconfig eth1 down

Try the “show variables” query now to see the result. The following log entries will indicate when and how HAProxy detected the failure.

tail /var/log/haproxy/haproxy.log

Nov 15 00:08:51 localhost haproxy[1671]: Server mysql-cluster/mysql-1 is DOWN, reason: Layer4 timeout, check duration: 2002ms. 1 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.

Reducing Failover Interval


When a MySQL server goes down HAProxy takes some time to detect this failure and remove it from the cluster. In this section we’ll see how to control this time. First we’ll see how to measure this value. One way is to block the MySQL port using iptables for a certain amount of time, then remove the rule and check the log.

root@mysql-1:~# ifconfig eth1 down &&
date &&
sleep 20 &&
ifconfig eth1 up &&
date

Fri Nov 15 00:37:09 IST 2013
Fri Nov 15 00:37:29 IST 2013

The port 3306 was blocked for 20 seconds, we’ll look at the log file now.

root@haproxy:~# tail /var/log/haproxy.log
Nov 15 16:49:38 localhost haproxy[1275]: Server mysql-cluster/mysql-1 is DOWN, reason: Layer4 connection problem, info: "Connection refused", check duration: 0ms. 1 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.
Nov 15 16:49:56 localhost haproxy[1275]: Server mysql-cluster/mysql-1 is UP, reason: Layer7 check passed, code: 0, info: "5.5.31-0+wheezy1-log", check duration: 1ms. 2 active and 0 backup servers online. 0 sessions requeued, 0 total in queue.

It took 6 seconds to detect a failure (difference between 16:49:38 and 16:49:32) and 4 seconds_to detect that the server can be reached (difference between 16:49:56 and 16:49:52). This is determined by the server parameters rise, fall and inter.

The rise parameter sets the number of checks a server must pass to be declared operational. Default is 2.

The fall parameter sets the number of checks a server must pass to be declared dead. Default is 3.

The inter parameter sets the interval between these checks. Default is 2000 milliseconds.

Putting this info together a server must fail 3 continuous checks which are performed at an interval of 2 seconds to be considered dead. So in our example above the following would’ve happened.

16:49:32 - Port 3306 on mysql-1 was blocked
16:49:34 - Check - Failed - Failure No. 1
16:49:36 - Check - Failed - Failure No. 2
16:49:38 - Check - Failed - Failure No. 3 (server removed and event logged)

And when the firewall rule was removed.

16:49:52 - Firewall rule removed port 3306 accessible
16:49:54 - Check - Passed - Success No. 1
16:49:56 - Check - Passed - Success No. 2 (server added to cluster and event logged)

The following settings will reduce the test interval to 1 second and also reduce the number of fall tests.

nano /etc/haproxy/haproxy.cfg

server mysql-1 10.0.0.1:3306 check fall 2 inter 1000
server mysql-2 10.0.0.2:3306 check fall 2 inter 1000

Sometimes you may not want to flood the private network with too many “test” packets especialy if you have a large amount of MySQL servers. In such cases the fastinter and downinter parameters will come handy.

The fastinter parameter sets the interval between checks while a server is transitioning UP or DOWN.

The downinter parameter sets the test interval when a server is DOWN.

That explanation might be confusing so we’ll see it with an example.

nano /etc/haproxy/haproxy.cfg

server mysql-1 10.0.0.1:3306 check fastinter 1000
server mysql-2 10.0.0.2:3306 check fastinter 1000

Since we haven’t specified the “inter” parameter it defaults to 2000ms. With this configuration we’ll restart HAProxy and do the test again.

root@mysql-1:~# iptables -A INPUT -p tcp --dport 3306 -j REJECT &&
date &&
sleep 20 &&
iptables -D INPUT -p tcp --dport 3306 -j REJECT &&
date
Fri Nov 15 17:18:48 IST 2013
Fri Nov 15 17:19:08 IST 2013

Check the HAProxy log file.

root@haproxy:~# tail /var/log/haproxy.log
Nov 15 17:18:52 localhost haproxy[1353]: Server mysql-cluster/mysql-1 is DOWN, reason: Layer4 connection problem, info: "Connection refused", check duration: 0ms. 1 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.
Nov 15 17:19:11 localhost haproxy[1353]: Server mysql-cluster/mysql-1 is UP, reason: Layer7 check passed, code: 0, info: "5.5.31-0+wheezy1-log", check duration: 1ms. 2 active and 0 backup servers online. 0 sessions requeued, 0 total in queue.

Now it took only 4 seconds (compared to 6 earlier) to detect a failure and 3 seconds (compared to 4) to detect that the server was up. Behind the scenes this is what happened.

17:18:48 - Port 3306 blocked
17:18:50 - Check - Failed - Failure No. 1
17:18:51 - Check - Failed - Failure No. 2
17:18:52 - Check - Failed - Failure No. 3 (server removed and event logged)

And when the port was unblocked.

17:19:08 - Firewall rule removed
17:19:10 - Check - Passed - Success No. 1
17:19:11 - Check - Passed - Success No. 2 (server added to cluster and event logged)

First notice the interval between the port block event (17:18:48) and the first check (17:18:50), it is 2 seconds (the “inter” interval). Then notice the interval between Test 1 <-> Test 2 and Test 2 <-> Test 3, it is only 1 second (the “fastinter” interval). The same intervals can be noticed when the server moved from DOWN to UP. So “fastinter” controls the interval between these checks.

So what is downinter? When a server has been declared DOWN HAProxy continues checking it every 2 seconds (or the interval mentioned in inter). If you feel you’re using up unnecessary network resources setting the downinter to say 5000 will make HAProxy check a DOWN server only once in 5 seconds.

Important


The tests we did previously REJECTed the packets which means when HAProxy initiated a connection by sending a SYN packet to mysql-1 it received a RST packet (instead of SYN + ACK). This is why the log entry mentioned “Connection refused”. In this case only the fall, inter and fastinter values come into the scene.

Instead if HAProxy didn’t receive anything after sending SYN the connection times out. In this case in addition to the above mentioned parameters the “timeout” duration comes into the scene. This situation can happen if

  • iptables is set to DROP
  • the private interface is down
  • there is a problem with the private networking infrastructure

Further Reading


Official documentation http://cbonte.github.io/haproxy-dconv/configuration-1.4.html

<div class=“author”>Submitted by: <a rel=“author” href=“http://jesin.tk/”>Jesin A</a></div>

If you’ve enjoyed this tutorial and our broader community, consider checking out our DigitalOcean products which can also help you achieve your development goals.

Learn more here


About the authors
Default avatar
Jesin A

author

Developer and author at DigitalOcean.

Still looking for an answer?

Was this helpful?
10 Comments

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

HAproxy stats for database 3306 is stated offline, can advise me on this? i have remove the pass and all the privileges try login to mysql and it works but in HAProxy stats showing offline

When i try to connect from haproxy i get this error

root@haproxy ~]# mysql -h 192.168.122.54 -u haproxy_root -p -e “SHOW DATABASES” Enter password: ±-------------------+ | Database | ±-------------------+ | cluster1 | | cluster2 | | cluster3 | | information_schema | | mysql | | performance_schema | | testcluster | ±-------------------+ [root@haproxy ~]# mysql -h 127.0.0.1 -u haproxy_root -p -e “SHOW DATABASES” Enter password: ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (111) [root@haproxy ~]# mysql -h 127.0.0.1 -u haproxy_root -p -e “show variables like ‘server_id’” Enter password: ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (111) [root@haproxy ~]#

Kindly Support

For statistics configurations, please use below settings:

listen webfarm bind 0.0.0.0:8080 mode http stats enable stats uri / stats realm Strictly\ Private stats auth A_Username:YourPassword stats auth Another_User:passwd

Hi,

I have successfully installed a galera cluster following a tutorial here on DO. I also followed this one but when running

for i in seq 1 6 do mysql -h 127.0.0.1 -u haproxy_root -ppassword -e “show variables like ‘server_id’” done

with the correct password I get: mysql: [Warning] Using a password on the command line interface can be insecure. ±--------------±------+ | Variable_name | Value | ±--------------±------+ | server_id | 0 | ±--------------±------+ mysql: [Warning] Using a password on the command line interface can be insecure. ±--------------±------+ | Variable_name | Value | ±--------------±------+ | server_id | 0 | ±--------------±------+ mysql: [Warning] Using a password on the command line interface can be insecure. ±--------------±------+ | Variable_name | Value | ±--------------±------+ | server_id | 0 | ±--------------±------+ mysql: [Warning] Using a password on the command line interface can be insecure. ±--------------±------+ | Variable_name | Value | ±--------------±------+ | server_id | 0 | ±--------------±------+ mysql: [Warning] Using a password on the command line interface can be insecure. ±--------------±------+ | Variable_name | Value | ±--------------±------+ | server_id | 0 | ±--------------±------+ mysql: [Warning] Using a password on the command line interface can be insecure. ±--------------±------+ | Variable_name | Value | ±--------------±------+ | server_id | 0 | ±--------------±------+

so it doesn’t seem to work. Does anybody has any ideas on this?

thanks

HI,

We have configured HAproxy as MySQL load-balancer in following way.

[ Tomcat X 1 ]===>[ HAproxy X 1 ]===>[ MySQL master-master replicated nodes X 2 ]

When we route db connection trough HAproxy all works fine except our map-refresh event.

We get following error :

com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception: BEGIN NESTED EXCEPTION
java.io.EOFException STACKTRACE: java.io.EOFException at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1963) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2375) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2874) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715) at com.mysql.jdbc.Connection.execSQL(Connection.java:3249) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1268) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1403) at com.inuxu.batch.device.Device.loadDeviceAndCampaignInDataGrid(Device.java:117) at com.inuxu.batch.main.ECG.main(ECG.java:61) END NESTED EXCEPTION Last packet sent to the server was 7 ms ago. at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2586) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2874) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715) at com.mysql.jdbc.Connection.execSQL(Connection.java:3249) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1268) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1403) at com.inuxu.batch.device.Device.loadDeviceAndCampaignInDataGrid(Device.java:117) at com.inuxu.batch.main.ECG.main(ECG.java:61) END NESTED EXCEPTION at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:888) ~[EcgBatchV6.3.jar:na] at com.mysql.jdbc.Connection.checkClosed(Connection.java:1931) ~[EcgBatchV6.3.jar:na] at com.mysql.jdbc.Connection.prepareStatement(Connection.java:4705) ~[EcgBatchV6.3.jar:na] at com.mysql.jdbc.Connection.prepareStatement(Connection.java:4671) ~[EcgBatchV6.3.jar:na] at com.inuxu.batch.segmentscore.SegmentScore.loadSegmentAndCampaignInDataGrid(SegmentScore.java:127) ~[EcgBatchV6.3.jar:na] at com.inuxu.batch.main.ECG.main(ECG.java:62) [EcgBatchV6.3.jar:na] 06 Jan 2016;05:36:13.634 12931 [main]INFO ECG_siteDetailsMap: - Loading SiteDetailsMap ! 06 Jan 2016;05:36:13.635 12932 [main]INFO ECG_siteDetailsMap: - Map is locked for this process !! 06 Jan 2016;05:36:13.638 12935 [main]INFO ECG_siteDetailsMap: - loading siteDetailsMap_1 Map 06 Jan 2016;05:36:13.641 12938 [main]ERROR ECG_siteDetailsMap: - SQL Exception in: 06 Jan 2016;05:36:13.645 12942 [main]INFO ECG_siteDetailsMap: - Loading SiteDeliveryChannels ! /data/apps/tomcat7/tomcat-instance2/adServing

Our HAproxy conf :

global log 127.0.0.1 local0 debug user haproxy group haproxy tune.bufsize 20480 tune.maxrewrite 2048

defaults mode tcp mode tcp log global retries 3 timeout connect 5000 timeout server 50000 timeout client 50000 maxconn 2000

listen mysql-cluster bind 192.168.172.171:3306 mode tcp option mysql-check user haproxy_check stick-table type ip size 1m expire 1h stick on src balance roundrobin server mysql-1 <our node1 ip>:3306 maxconn 2000 check server mysql-2 <our node2 ip>:3306 maxconn 2000 check backup

listen 0.0.0.0:8080 bind 0.0.0.0:8080 mode http stats enable stats uri / stats realm Strictly\ Private stats auth admin:admin

Please suggest if anything is wrong in this.

I followed this tutorial to the letter and set everything up local on my setup in my home lab and kept running into a error

ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 78 Current database: *** NONE ***

It would recover from it and run the query to show the server id, but that would appear before it. If I extend the timeout any longer, it would only be that much longer before that error would appear. I went one step further and connected my Spring/Java app to the HaProxy and those errors would cause the app to drop the connection. Either you did something that was not covered in the tutorial or you decided not to show that error.

I spent 2 days researching this and trying all kind of configurations both on the database and haproxy and it was unavoidable. The only thing that I was able to do which still needs tweaking was to change the mysql timeout settings to a lesser value than haproxy, and to increase the haproxy settings. I ran this for hours and randomly tested and had a couple of failures still even after tweaking it.

Anyhow, if you can tell us if you ran into that issue or not that would be great. If you did not run into that issue, than my next step is to spin up a couple of DO instances and run your code there to see if it works different on DO’s instances.

Hi How can I integrate Mysql And Http HAProxy for 3 nodes Is that Possible ?

Trying to connect application with load-balancer. Application supports connection via JDBC driver of Java. Connection url is like: jdbc:mysql:// [loadbalancer_ip] /[database_name]?rewriteBatchedStatements=true

Loadbalancer timeouts this requests. How to fix this issue?

Isn’t this vulnerable against a split-brain situation?

Wonderful article.

However I have a problem. I’m stuck in the “Testing Load Balancing and Failover”. The thing is when I execute the command mysql -h 127.0.0.1 -u haproxy_root -p -e "show variables like 'server_id'", roundrobin act properly since it jumps from 1 to 2 and viceversa… BUT in my case I have 3 DB Servers (db1, db2, db3) and not 2 as this tutorial. I’m reading everywhere and roundrobin should jump between all the servers, but in my case it just work between db1 and db2, why not in db3?

Check my HAProxy configuration:

global
    log 127.0.0.1 local0 notice
    user haproxy
    group haproxy

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

listen mysql-cluster
    bind 0.0.0.0:3306
    mode tcp
    option mysql-check user haproxy_check
    balance roundrobin
    server db1 172.28.109.10:3306 check
    server db2 172.28.109.11:3306 check
    server db3 172.28.109.12:3306 check

listen 127.0.0.1:8080
    mode http
    stats enable
    stats uri /
    stats realm Strictly\ Private
    stats auth monitor_user:password

Those 3 servers are properly declared in my MariaDB Galera Cluster configuration file, and they share information normally. Check part of it here:

wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://171.28.109.10, 171.28.109.11, 171.28.109.12"
wsrep_cluster_name='galera_cluster'
wsrep_node_address='171.28.109.10'
wsrep_node_name='db1'
wsrep_sst_method=rsync
wsrep_sst_auth="user:password"

What am I missing? Please help me.

Thanks.