How to configure HAproxy loadbalancer for Mysql

HAProxy, which stands for High Availability Proxy, is a popular open source software TCP/HTTP Load Balancer and proxying solution which can be run on Linux, Solaris, and FreeBSD. Its most common use is to improve the performance and reliability of a server environment by distributing the workload across multiple servers.

This tutorial explains the detailed steps to configure HAproxy loadbalancer for Mysql in Linux servers.

Load Balancer - 10.0.0.100

Node1 - 10.0.0.1

Node2 - 10.0.0.2

Before proceeding, make sure all MySQL servers (Node1, Node2) are up, running and are properly replicating (master-master) 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.

#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.

#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.

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.

#yum install haproxy

Enable HAProxy to be started by the init script.

#chkconfig haproxy on

Configuring HAProxy

Rename the original configuration file

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

Create and edit a new one

# vi /etc/haproxy/haproxy.cfg

The first block is the global and defaults configuration block.

global
log 127.0.0.1 local2
user haproxy
group haproxy

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

Since we've told HAProxy to send log messages to 127.0.0.1 we have to configure rsyslog to listen on it.
To configure rsyslog, Create new file and update with below entries,

# vi /etc/rsyslog.d/haproxy.conf

$ModLoad imudp
$UDPServerRun 514
$AllowedSender UDP, 127.0.0.1
local2.* /var/log/haproxy.log

Logs will be saved to the file /var/log/haproxy.log

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.

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 stats 0.0.0.0:8080
mode http
log global
maxconn 10
timeout queue 100s
stats enable
stats hide-version
stats refresh 30s
stats show-node
stats auth Another_User:passwd
stats uri /

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://: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 |
+---------------+-------+

Fail a MySQL server either by stopping the service

#service mysql stop

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.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.

It will back to normal working once the node mysql started