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
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
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.
cp /etc/haproxy/haproxy.cfg{,.original}
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.
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).
awesome. it helped me alot thanks :)
Good to hear :-)
Doesn’t this work for master slave replication?
Yes, You could use the same configurations with few changes. You should state your MySQL slave server with `backup` keyword in the configuration. Refer below.
http://serverfault.com/questions/352253/haproxy-mysql-failover-load-balancing
thanks a ton :)
Thank sir, But can you make a tutorial video on youtube, I don’t totally understand.
Noted.
Good to read this blog, thumbs up sir. However, may I please request a blog regarding apache tomcat web application to HAProxy with mysql cluster?? thanks you in advance.
Noted.
It’s work for master-slave replication? and i am still can’t view the stats ip-haproxy:8080
It could be your firewall blocking the port. Make sure the 8080 is accessible
Thanks Sir.. it helped me a lot :)
Good to hear :-)
In this case, isn’t HA Proxy is Single Point of failure ?
You have to implement HA as shown in the below article. Here in the article I have used a single HAProxy for explanation purpose.
https://dasunhegoda.com/how-to-setup-haproxy-with-keepalived/833/