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.
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 1 – Node 1
Server 2 – Node 2
Server 3 – Load Balancer
Prepare MySQL Servers
mysql_haproxy_check user is used to check whether the instances are up or not. Basically a health check.
mysql -u root -p -e "INSERT INTO mysql.user (Host,User) values ('192.ABC.Z.30','mysql_haproxy_check'); FLUSH PRIVILEGES;"
This user will be used to access the MySQL cluster from the PHP/JAVA/etc application server.
mysql -u root -p -e "GRANT ALL PRIVILEGES ON *.* TO 'root_haproxy'@'192.ABC.Z.30' IDENTIFIED BY 'password' WITH GRANT OPTION; FLUSH PRIVILEGES"
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.
apt-get install mysql-client
Make sure the MySQL client works fine
mysql -h 192.ABC.Z.30 -u haproxy_root -p -e "SHOW DATABASES"
Installing & Configuring HAProxy
Let’s install haproxy
apt-get install 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
listen mysql-cluster bind 0.0.0.0:3306 mode tcp option mysql-check user mysql_haproxy_check balance roundrobin server mysql-1 192.ABC.X.28:3306 check server mysql-2 192.ABC.Y.29:3306 check
If you need stats add the below block as well
listen stats 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
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.
mysql -h 127.0.0.1 -u root_haproxy -p -e "show variables like 'server_id'"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 2 | +---------------+-------+
2nd request and the result.
mysql -h 127.0.0.1 -u root_haproxy -p -e "show variables like 'server_id'"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 1 | +---------------+-------+
Viewing the Stats
Open up a browser and type below URL to view the stats.
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).