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.

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

Loading

15 Comments

  1. Usama Ejaz May 26, 2015 at 8:41 pm

    awesome. it helped me alot thanks :)

    Reply
      1. G.V. Karthik July 21, 2015 at 12:45 pm

        Doesn’t this work for master slave replication?

        Reply
          1. G.V. Karthik July 24, 2015 at 5:30 am

            thanks a ton :)

  2. trịnh phú sơn August 11, 2015 at 10:50 am

    Thank sir, But can you make a tutorial video on youtube, I don’t totally understand.

    Reply
  3. Albert Bayoneto Iglesias August 22, 2015 at 9:51 am

    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.

    Reply
  4. Fadli Idris September 10, 2015 at 6:13 pm

    It’s work for master-slave replication? and i am still can’t view the stats ip-haproxy:8080

    Reply
    1. Dasun Hegoda September 12, 2015 at 12:47 am

      It could be your firewall blocking the port. Make sure the 8080 is accessible

      Reply
  5. Ganesh Pathak September 12, 2015 at 11:25 am

    Thanks Sir.. it helped me a lot :)

    Reply
  6. sanjay October 11, 2015 at 4:49 am

    In this case, isn’t HA Proxy is Single Point of failure ?

    Reply

Leave A Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.