Loading balancing is a hot topic when it comes to high availability(HA). You can distribute the workload among server using a load balancer such as HAProxy. Today we are going to take a look at MySQL loading balancing with HAProxy. To load balance at least you should have 2 nodes of MySQL running. If you haven’t got 2 MySQL nodes running refer the previous post on MySQL Master-Master replication.

What’s HAProxy

HAProxy is a free, very fast and reliable solution offering high availability, load balancing, and proxying for TCP and HTTP-based applications. It is particularly suited for very high traffic web sites and
powers quite a number of the world’s most visited ones.

Server Setup

Server 1 – Node 1
Hostname: mysql-1
IP: 192.ABC.X.28

Server 2 – Node 2
Hostname: mysql-2
IP: 192.ABC.Y.29

Server 3 – Load Balancer
Hostname: haproxy
IP: 192.ABC.Z.30

 

haproxy-mysql

Load balancing MySQL with HAProxy

Prepare MySQL Servers

mysql_haproxy_check user is used to check whether the instances are up or not. Basically a health check.

This user will be used to access the MySQL cluster from the PHP/JAVA/etc application server.

Replace root_haproxy and password with your own secure values. It is enough to execute these queries on one MySQL master as changes will replicate to others. In your application you will provide the root_haproxy user’s details to establish database connections.

Install MySQL Client

Let’s install MySQL client on the server where HAProxy will be installed.

Make sure the MySQL client works fine

Installing & Configuring HAProxy

Let’s install haproxy

Configuring HAProxy

edit the /etc/default/haproxy file and set the

ENABLED=0 to ENABLED=1

Create a duplicate of the main configuration file using below command.

Add the below configuration

If you need stats add the below block as well

You can comment out the unnecessary lines in the haproxy config file leaving above configuration.

Testing Load Balancing and Fail-over

1st request and the result.

2nd request and the result.

Viewing the Stats

Open up a browser and type below URL to view the stats.

http://192.ABC.Z.30:8080/

Use A_Username as username and YourPassword as your password to view the stats which were provided by you in the haproxy configuration file. That’s it.

Hope you got an idea how to do MySQL load balancing with HAProxy. If you have any questions let me know in the comments below. Your feedback is highly appreciated(happy-face).

Be Sociable, Share!