Set up High Availablity for MySQL Load Balancing via HAProxy

This is an old solution, but I believe it remains useful today for load balancing in MySQL. I know there are many options in the market. However, HAProxy still has its position because of its performance. In this blog, I just want to share how we set up HAProxy for load balancing MySQL. This is also known as High Availability when we need to have multiple MySQL slave servers for read operations. In this case, we have an app server that is writing to a database (master role) and reading data from two other databases (slave role).

Prerequisite

• An Ubuntu 24.04 server plays the role of an App server where we install HAProxy version 2.8 (default).
• 3 x Ubuntu 24.04 installed MySQL, and I assume that you have configured one DB as a master and the other two as slaves.

Note: I’m running a local DNS that allows me to use domain instead of IP address. If you want to have local DNS server so that you won’t have to enter your IP address, please check out How to Set Up a Local DNS Server with Dnsmasq in Ubuntu 24.04 (Fast & Easy) to facilitate your testing.

Create MySQL user for haproxy check

Before we configure HAProxy, we need to create a haproxy user on the master DB server. This is for web-01 server to access the DB server and check MySQL availability. On db-01, run:

CREATE USER IF NOT EXISTS 'haproxy_user'@'%' IDENTIFIED BY 'HaproxyPassword';
CREATE USER IF NOT EXISTS 'haproxy_user'@'localhost' IDENTIFIED BY 'HaproxyPassword';
GRANT ALL PRIVILEGES ON *.* TO 'haproxy_user'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'haproxy_user'@'localhost';
FLUSH PRIVILEGES;

Install HAProxy

On the web-01 server, we install HAProxy by running:

sudo apt install haproxy mysql-client -y

Configure HAProxy for MySQL (master/slave)

Create a copy of the original haproxy.cfg file:

sudo mv /etc/haproxy/haproxy.cfg /etc/haproxy/haproxy.cfg.original

(Optional) Now we need to create a new haproxy.cfg file with custom content. Run sudo nano /etc/haproxy/haproxy.cfg and add the following content:

global
        log /dev/log    local0
        log /dev/log    local1 notice
        chroot /var/lib/haproxy
        stats socket /run/haproxy/admin.sock mode 660 level admin
        stats timeout 30s
        maxconn 2000
        user haproxy
        group haproxy
        daemon

defaults
        log     global
        option  tcplog
        option  tcpka
        retries 3
        timeout connect 10000
        timeout client  50000
        timeout server  50000

# This frontend "monitor-stats" is optional, you can exclude it if you don't need web UI 
frontend monitor-stats
        bind 0.0.0.0:8080
        mode http
        stats enable
        stats uri /stats
        stats realm Strictly\ Private
        stats auth binh:haproxy

If you already have a haproxy.cfg file with your own global and defaults settings, you can ignore this change. I added simple default configurations to eliminate unnecessary settings.

If you want to understand the meaning of each option under global and defaults settings, can check haproxy configuration docs

Next, we move to the main configuration part for MySQL. We will create one cluster group (frontend/backend) for read operations, and one cluster group for write operations. For each cluster group, we basically need two sections: frontend and backend.

Create a frontend configuration for the read operations, I named it as db-read in both sections. So, still in the haproxy.cfg file, we add:

frontend db-read
        bind 127.0.0.1:3307 
        mode tcp
        acl MAIN_down nbsrv(db-read) le 0
        default_backend db-read
        use_backend db-write if MAIN_down

Where:

• bind: make this db-read proxy listening on the specified IP address and port. Our web-01 will connect to this <IP-address>:<port>
• mode: sets this proxy to use tcp mode. As HAProxy by default doesn’t have a specific mode for MySQL, we use tcp here.
• acl: creates Access Control List named “MAIN_down”. This ACL checks a boolean condition where it runs nbsrv method against the backend section named db-read (we define later below), and returns an integer value corresponding to the number of usable servers of the backend. If the usable servers are less than or equal to 0 (means non of the servers in the backend section is working), it returns true, which means “MAIN_down” is down. This condition is used by the use_backend option below.
• default_backend: sets to use the db-read backend by default
• use_backend: tests the result of the acl option, if “MAIN_down”, switch connections to use backend named db-write instead. It becomes helpful when you lose both slave servers, but are still able to read by using the master DB server.

Similar to the frontend db-read, we create a frontend section named db-write for the write operations:

frontend db-write
        bind 127.0.0.1:3308 
        mode tcp
        acl MAIN_down nbsrv(db-write) le 0
        default_backend db-write

Noted that we don’t use the use_backend option for the write operation, as we have only one master DB. I know it creates a single point of failure, but setting up a second master comes with a trade-off. I’ll explain later.

Now, we need to create a backend section for the read operations. We add:

backend db-read
        balance roundrobin
        option allbackups
        server db-02.srv.local db-02.srv.local:3306 check
        server db-03.srv.local db-03.srv.local:3306 check

Where:

• balance: we choose roundrobin to split connections to each server in turns. You can check here to see other options
• option: allbackups treats all servers with backup option enabled, and these servers are configured in the server option below. For example: server db-02.srv.local db-02.srv.local:3306 check backup. In case you have a few spare servers, it seems a good practice to add them to this backend section as a backup. I don’t have it, but still good to set this allbackups by default.
• server: specifies which server should belong to this db-read backend. The check option is enabled to do a health check. It marks the server as available when a connection can be established at the highest configured transport layer (by using TCP).

Similarly, we have backend db-write for the write operation. We add:

backend db-write
        balance roundrobin
        option allbackups  
        server db-01.srv.local db-01.srv.local:3306 check

Remember to save the content and close from the nano command. Press Ctrl + O and press Enter to save the content. Then press Ctrl + X to exit.

Finally, we need to restart the haproxy service to make the new config take effect

sudo systemctl restart haproxy

Can go to the HAProxy stats dashboard to view, access your server IP that has HAProxy through port 8080 if you enabled it. My one is http://192.168.68.99:8080/stats

NOTED: If you’re interested in automating HAProxy configuration without manually changing backend servers when you scale out or remove any server. I wrote a use case of utilising Consul service discovery to achieve this. Please check out Consul Service Discovery: Automate HAProxy Configuration with Consul for MySQL for more information.

Testing Load Balancing

In reality, your application will make requests to 127.0.0.1:3307 (We defined in the haproxy config). This application should remain on the same server where you installed HAProxy to access 127.0.0.1. Otherwise, it won’t be able to connect unless you bind the db-read frontend to 0.0.0.0:3307

Back to our demo, we test making requests to the db-read frontend in this case. On the web-01 server, as we don’t have any application, we make a simple query, we run:

for i in `seq 1 6`; do mysql -h 127.0.0.1 -u haproxy_user -pHaproxyPassword -P 3307 -e "show variables like 'server_id'"; done

Output:

We see requests are splitting evenly between the 2 slave DB servers: server_id 2 is db-02, and server_id 3 is db-03

That’s all we need for a simple load balancing. However, I personally think that this is not enough to establish high availability for MySQL load balancing. Look at this part:

backend db-read
  ...
  server db-02.srv.local db-02.srv.local:3306 check
  ...

The check option in the server option enables a health check on a server by ensuring a TCP connection is established. This method may not be useful because MySQL still responds successfully to HAProxy even when it isn’t really operating properly.

For example, while a MySQL slave is still up and running, we can test by performing stop slave;, HAProxy still considers this slave DB server as healthy and forwards requests to it. If the slave DB server is stopped for a long time (e.g. 1 day), there’s a huge lag of data between the master and slave, causing the application query to this slave DB that hasn’t had data updated (a potential bug happens there).

The best practice is to combine HAProxy with a custom script (play the role of an agent). This script runs on each DB server and performs interval checks continually to make sure the replication status or MySQL itself are working normally. And then the script returns an appropriate status to HAProxy so that HAProxy knows the server is healthy. HAProxy relies on the returned status to decide whether it forwards requests to a DB server or not.

(Advanced) Configure HAProxy with an agent script for MySQL checks

The script can be written in many languages, so it’s up to your choice. This is a high-level method and requires programming knowledge. I won’t go into details of how to write a script. Instead, I’ve created a haproxy-mysql-check script written in Golang so that you can refer to it. This script is built to produce the statuses required by HAProxy’s agent-check option.

This part is to show you an idea of how to use HAProxy with the agent script. If you don’t want to use this method, feel free to skip it.

Set up a custom agent script

Install that haproxy-mysql-check script on each DB server. Please run the following:

# On user's home directory, create a app/ folder to store this script
sudo mkdir app
sudo cd app

# Download haproxy-mysql-check package
wget https://gitlab.com/api/v4/projects/71540041/packages/generic/haproxy-mysql-check/1.0.0/haproxy-mysql-check-linux-amd64-1.0.0.tar.gz

# Extract it to get binary file
tar -xvf haproxy-mysql-check-linux-amd64-1.0.0.tar.gz

A binary file haproxy-mysql-check appears then. Now we need to add a .env file with content looks like:

# Change values of these parameters to your own values
DB_HOST="localhost"
DB_USER="haproxy_user"
DB_PASS="HaproxyPassword"
DB_PROTOCAL="tcp"
DB_PORT="3306"
# Change IS_SLAVE to "false" if the DB is master
IS_SLAVE="true" 

The .env file contains basic settings that enable the script to connect to the MySQL service to perform checks.

Now, start the script in the background:

./haproxy-mysql-check >> haproxy-agent.log 2>&1 &

This is just a quick way to start the script. It’s better that you create a systemd service called “haproxy-mysql-check.service” so that you can use it to start the haproxy-mysql-check script. That’s when you can enable the service to start at launch. Every time your DB server is rebooted, the script is up and starts checking the MySQL service, so it’s able to tell HAProxy immediately once MySQL is in operational mode.

You could use your own script if you have one, but please make sure that it returns the statuses required by the agent-check option

Update your HAProxy config file to use agent-check

Open the haproxy.cfg file and append default-server option to the backend db-read part, run sudo nano /etc/haproxy/haproxy.cfg:

# Other configurations...

backend db-read
        balance roundrobin
        option allbackups
        default-server agent-check agent-port 9200 fall 1 rise 1 inter 10000 weight 100
        server db-02.srv.local db-02.srv.local:3306 check
        server db-03.srv.local db-03.srv.local:3306 check

backend db-write
        balance roundrobin
        option allbackups
        default-server agent-check agent-port 9200 fall 1 rise 1 inter 10000 weight 100
        server db-01.srv.local db-01.srv.local:3306 check

Press Ctrl + O and press Enter to save the content. Then press Ctrl + X to exit.

We append default-server option where:

• default-server: supports options used in the server option. If we define here, these options are all passed as arguments on the server line.
• agent-check: enable an auxiliary agent check, which tells HAProxy that the server has an agent running independently to perform health checks.
• agent-port: a TCP port used for agent checks
• fall: mark a server as dead after <count> consecutive unsuccessful health checks. We set it to 1 which means after 1 unsuccessful health check, the server is considered as dead.
• rise: as a server as operational after <count> consecutive successful health checks. The server is marked as operational after 1 successful health check.
• inter: interval between two consecutive health checks, it delays <milliseconds> after the first check. Here is 10s.
• weight: set a load proportional to each server based on specified weight. The default weight is 1, and the maximal value is 256. The higher the weight is set, the higher the load the server receives. Our haproxy-mysql-check script also calculates the weight based on the Seconds_Behind_Master parameter on each DB server. If the slave DB server has a high lag, it will reduce the weight and send the weight’s value back to HAProxy along with its status. For example, “up 50%” means the slave DB server is still up and running, but should reduce the weight down to 50. Once the slave DB server catches up to the master DB, Seconds_Behind_Master becomes 0, our script returns status “up 100%” to bring the weight back to 100.

The agent-check takes priority over the check option. When both options are used, agent-check performs regular checks. However, if our script isn’t running, agent-check cannot access it and will resort to the check option, which only verifies the TCP connection.

Remember to reload HAProxy service to make changes take effect:

sudo systemctl reload haproxy

Testing Load Balancing

We go to the random slave DB server, in here, I test with db-03. Log in to MySQL and try stopping its replication:

binh@db-03:~/app$ sudo mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3941
Server version: 10.11.13-MariaDB-0ubuntu0.24.04.1-log Ubuntu 24.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected, 1 warning (0.007 sec)

We go back web-01 server, and check the haproxy log:

tail -100f /var/log/haproxy.log

You’ll see a line that looks similar below (with your own server hostname):

Our script returns status “stopped” to HAProxy, so HAProxy marked the server as DOWN.

Go back to db-03 and start the slave back, run:

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.009 sec)

Continue checking the haproxy log on web-01, you will see something like:

Our script returns status “up” to HAProxy, so HAProxy marked the server as UP.

That’s all for this part.

(Bonus) Configure HAProxy for MySQL (master/master)

In the example above, we apply Load Balancing to read operations only. For the write operations, it becomes more complicated as it really depends on how your application works, and that sounds biased to me. That’s because I encounter several situations where I have two master DB servers with their own slave servers following, and the application writes to both Master DB servers at the same time, sometimes creating dangerous bugs that we would want to avoid.

I just want to mention that if you really want to implement Load Balancing for the write operations to the master servers, and you have enough resources to have the second master just stay there as a backup. We have a way to do that safely.

Look at the diagram; that’s when HAProxy sends traffic for the write operation to only one master DB server (db-01) and treats the second one as a backup. The second one serves traffic only when the db-01 is down. If you don’t want to let the db-04 (2nd master DB) just stay there and do nothing, we could add it to the backend db-read so that we still utilise the 2nd master DB, but it’s better to set a low weight for it.

Just update your HAProxy config to:

# Other global and default configurations...

frontend db-read
        bind 127.0.0.1:3307
        mode tcp
        acl MAIN_down nbsrv(db-read) le 0
        default_backend db-read
        use_backend db-write if MAIN_down

frontend db-write
        bind 127.0.0.1:3308
        mode tcp
        acl MAIN_down nbsrv(db-write) le 0
        default_backend db-write

backend db-read
        balance roundrobin
        option allbackups
        default-server agent-check agent-port 9200 fall 1 inter 10000 rise 1 weight 100
        server db-02.srv.local db-02.srv.local:3306 check
        server db-03.srv.local db-03.srv.local:3306 check
        # We append db-04 into LB group for read operations with weight 30
        server db-04.srv.local db-04.srv.local:3306 check weight 30


# We treat db-04 as the backup server for the write operation
backend db-write
        balance roundrobin
        option allbackups
        default-server agent-check agent-port 9200 fall 1 rise 1 inter 10000 weight 100
        server db-01.srv.local db-01.srv.local:3306 check
        server db-04.srv.local db-04.srv.local:3306 check backup

That’s all for this post.