MySQL Load Balancing with HAProxy

Taking it from where we left on the last post where we created master-slave replication. In this post, we going to try and load balance that. We have four servers this time, one HAProxy server, one master server and two slave servers. NB: I will be sharing the full configurations.

Lab Servers:

HAProxy — 192.168.56.101
Master server - 192.168.56.102
Slave1 server - 192.168.56.103
Slave2 server - 192.168.56.104

Create users on mysql servers:

We start by creating a haproxy_user. The user will be used to check if the instances are are up or not. More like a health check. Since we have replication which is ignoring the mysql database where we are adding the user. We will have to create the user in all mysql servers. If we did not ignore the mysql database, we would only run the command on master and the user should be created in all slaves.

mysql -u root -p
USE mysql;
INSERT INTO user (HOST, USER) VALUES('192.168.56.101', 'haproxy_user');
flush privileges;

Repeat the above commands on the two slaves.

creating haproxy_user

Next we create haproxy_rootthat will have the same access level as root user and will be used to access the databases from the HAProxy server. You can use root(Fail-over need root access to the database), but it is good practice to create another user. Run the following commands in all the mysql servers.(I assume you still in the same session from previous step)

GRANT ALL PRIVILEGES ON *.* TO 'haproxy_root'@'192.168.56.101' IDENTIFIED BY 'haproxy_root_pass' WITH GRANT OPTION;
flush privileges;

create haproxy_root user

Install mysql-client on HAProxy server:

In haproxy server we run the following command to install mysql-client. sudo apt-get install mysql-client. Once done, you can confirm it is installed by running mysql command.

Install and Configure HAProxy:

We start off by install haproxy in HAProxy server. sudo apt-get install haproxy. It is not enable by default, therefore we have to enable it so that it can start any time the server is rebooted. Edit the file /etc/default/haproxy and set ENABLED=1. That is all you need to do there.

Next we backup the existing configuration file and edit like below.

sudo cp /etc/haproxy/haproxy.cfg{,.original}

My final configuration file was

/etc/haproxy/haproxy.cfg

The listen stats was added to allow view the setup on the browser. On mysql-cluster, we check the haproxy_user, use roundrobin algorithm. And finally pass the servers.

When you happy with your configuration, you can check if it is valid before restarting the haproxy service. haproxy -c -V -f /etc/haproxy/haproxy.cfg. If it has errors, fix them until the file is valid. Now you can go ahead and restart the haproxy. sudo service haproxy restart.

Viewing the Stats:

If the above setup was successful, you should have something similar to this when you view on the browser.

Testing Load Balancing and Fail-over:

Doing a quick test on HAProxy server by querying the for server-id, you can see the result is from all the mysql servers in roundrobin manner.

mysql -h 127.0.0.1 -u haproxy_root -p -e "show variables like 'server_id'";

Conclusion:

We have successfully set a load-balanced mysql-cluster. See you on the next post.