Consul Service Discovery: Automate HAProxy Configuration with Consul for MySQL

This article is to show another use case of Consul. In general, we can register a lot of different services (MySQL, Redis, Memcache, our own applications, etc.) to Consul. In this way, we use Consul to track the availability of those services.

In the HAProxy context, remember that when configuring HAProxy, we have to manually update the backend config pointing to the servers that we use to process the requests. Backends here could be MySQL, our own applications, or any other services. For example, we use HAProxy for load balancing traffic to MySQL databases. I used to write Set up High Availability for MySQL Load Balancing via HAProxy blog to discuss this, so please feel free to take it as a reference.

To leverage the potential of Consul, we can also use it to check whether the desired services are available and use consul-template to update the HAProxy config file for backend config automatically. consul-template is just a binary file (a CLI tool) that makes API calls to the Consul server to get server details as well as service details running on those servers.

Prerequisites

• Require Consul Setup knowledge.
• Had a cluster of at least 3 Consul server nodes. If you want to test, you can build a standalone Consul server (running in dev mode). You may want to look at sections 2 and 3 in Consul Service Discovery: Automate Nginx Upstream updates for dynamic load balancing blog to know more about how to set up a single Consul server. I’ve also written Puppet Series – Automate Consul Cluster Setup to guide setting up a Consul Cluster by Puppet if you’re interested.
• Have 2 or 3 nodes that played the role of MySQL databases.
• Have an app server (I named it app-01), which we will install HAProxy on to test automatic HAProxy config update.
• The app and database servers should have Consul installed and register themselves as Consul Agents to the Consul Cluster.

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.

Verifying Consul Server and Agent on all nodes

Basically, Consul has two parts:

• Consul Servers: consist of 3 servers/containers/etc. with Consul installed, and they’re running in server mode to form the cluster.
• Consul Agents: are servers/containers/etc. also with Consul installed, and they’re running in agent mode to join the Consul cluster.

I’ve used Puppet to deploy the installation and configurations of Consul Servers and Agents on all nodes. So, in this blog, I won’t show how to build a Consul cluster or how to join a Consul Agent to the cluster because it’s out of scope and not the main purpose. But I recommend you check out my previous blogs related to Consul in the Prerequisites section. Or have a look at the Consul docs for more information.

The main purpose is to show you how to use Consul to update the HAProxy config file automatically when a backend server is removed or replaced. Therefore, I assume you have set up the Consul Cluster of Servers and Agents on all nodes. Overall, after we’re done with this basic setup, we can access one of the three Consul servers to check the other nodes. My URL is http://consul-01.srv.local:8500, you should change this to your corresponding URL or IP address. Here is the result:

Or we can also check it by running a CLI on an arbitrary server in the cluster. Simply run:

consul members

Output:

Ok. We’re good to start setting up HAProxy and testing now.

Install HAProxy and necessary packages

On app-01 server, we need to install HAProxy and consul-template binary file by running:

# Update APT
sudo apt-get update -y
 
# Download GPG key
wget -O - https://apt.releases.hashicorp.com/gpg | sudo gpg --dearmor -o /usr/share/keyrings/hashicorp-archive-keyring.gpg
 
# Register APT source in order to install "consul" through APT
echo "deb [arch=$(dpkg --print-architecture) signed-by=/usr/share/keyrings/hashicorp-archive-keyring.gpg] https://apt.releases.hashicorp.com $(grep -oP '(?<=UBUNTU_CODENAME=).*' /etc/os-release || lsb_release -cs) main" | sudo tee /etc/apt/sources.list.d/hashicorp.list
 
# Update APT again and install consul-template
sudo apt update && sudo apt install consul-template -y

# Install HAProxy and mysql-client lib
sudo apt install haproxy mysql-client -y

# Start and enable HAProxy service
sudo systemctl start haproxy
sudo systemctl enable haproxy

The above commands are for Ubuntu/Debian. If you want to install consul-template for other OS platforms, can see this installation docs.

On db-01 server, we should create a MySQL user so that we can use to test later. Login db-01 server and access MySQL with your own account. Create haproxy_user user by these commands:

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;

Register MySQL service to the Consul servers

Before we start configuring HAProxy, we need to register the MySQL service on db-01, db-02, and db-03 servers with the Consul Servers so that we can use consul-template on app-01 to fetch the data of the available database servers.

Depending on where your consul loads the configuration files, my consul binary loads config files from /etc/consul. Sometimes, it might be /etc/consul.d in your case, so it’s best to check that. You can run:

sudo ps -ef | grep consul 

The output should be something like:

consul 5444 1 0 11:30 ? 00:01:44 /usr/local/bin/consul agent -config-dir /etc/consul

Now, on three database servers, we will create /etc/consul/service-mysql.hcl file to register MySQL service to the Consul servers.

There’s a slight difference on db-01 because we’re going to use this database for read/write operations, so it should have tag names like e.g. db-backend-read, db-backend-write. The tag names are set here is just for my demonstration, you could take it as reference. Otherwise, you may want to set different tag’s names according to your own purpose or your system architecture. Now, on db-01 server, we run:

sudo nano /etc/consul/service-mysql.hcl

And add the following content:

## -----------------------------
## service-mysql.hcl
## -----------------------------
service {
  name = "mysql"
  id = "mysql-db-backend"
  tags = [ "db-backend-read", "db-backend-write" ]
  port = 3306
  
  check {
    tcp = "localhost:3306"
    interval = "5s",
    timeout = "1s"
  }
}

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

And on db-02 and db-03, we treat these databases for read operation only, so we run:

sudo nano /etc/consul/service-mysql.hcl

And add the following content:

## -----------------------------
## service-mysql.hcl
## -----------------------------
service {
  name = "mysql"
  id = "mysql-db-backend"
  tags = [ "db-backend-read" ]
  port = 3306
  
  check {
    tcp = "localhost:3306"
    interval = "5s",
    timeout = "1s"
  }
}

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

To validate configuration file, on 3 database servers, we run:

consul validate /etc/consul/

If you see a sentence saying Configuration is valid!, then it means we’re fine now.

Reload consul service to start registering the MySQl service:

sudo systemctl reload consul

Go back to the Web UI page, and select Services section, we have:

For now, the Consul Agent will run a TCP check at an interval of 5 seconds against the MySQL port 3306 on localhost to check whether MySQL is running or not. We can implement a script check as well, which allows us to run a deeper check for MySQL. However, to keep it simple, I’ll just use the TCP check in this case.

Automate HAProxy configuration update

Create an HAProxy config template file

We will create an HAProxy config template file. This is for consul-template to fetch server data from Consul servers and create a real HAProxy config file based on the defined template. Run:

sudo nano /etc/haproxy/haproxy.cfg.ctmpl

And add this 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 admin:haproxy

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 fall 1 inter 10000 rise 1 weight 100
        {{ range service "db-backend-read.mysql" }}
          {{- if eq .Node "db-01" -}}
        server {{ .Node -}}.srv.local {{ .Node -}}.srv.local:{{ .Port }} check weight 30
          {{- else }}
        server {{ .Node -}}.srv.local {{ .Node -}}.srv.local:{{ .Port }} check
          {{- end -}}
        {{ end }} 


backend db-write
        balance roundrobin
        option allbackups
        default-server fall 1 inter 10000 rise 1 weight 100
        {{- range service "db-backend-write.mysql" }}
        server {{ .Node -}}.srv.local {{ .Node -}}.srv.local:{{ .Port }} check
        {{ end }}

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

Because I’m treating db-01 for both read and write operations, we should reduce the load that db-01 receives from read operations. That’s why I set db-01 with weight 30 while other databases should go with weight 100 by default. Also, you may want to tailor the HAProxy config file based on your own preferences.

To test whether consul-template produces an expected configuration file. We run:

sudo consul-template -once -template "/etc/haproxy/haproxy.cfg.ctmpl:/tmp/haproxy-testing.cfg" -dry

And then check the output to see if it has the correct format. Or we can remove the -dry option so it produces a real /tmp/haproxy-testing.cfg file. That’s when we can run:

haproxy -f /tmp/haproxy-testing.cfg -c -V

If we see output saying Configuration file is valid, then we’re good to go.

Set up consul-template configuration file

Next, we create a /etc/consul-template directory where we store consul-template‘s configuration files. These files are to tell consul-template to watch servers’ details through the Consul servers and update the /etc/haproxy/haproxy.cfg file if needed, run:

sudo mkdir /etc/consul-template
sudo touch /etc/consul-template/config.hcl
sudo chown -R consul:consul /etc/consul-template
sudo chmod 644 /etc/consul-template

And run:

sudo nano /etc/consul-template/config.hcl

And add the following content for consul-template config file:

consul {
  address = "localhost:8500"

  retry {
    enabled  = true
    attempts = 15
    backoff  = "250ms"
  }
}
template {
  source      = "/etc/haproxy/haproxy.cfg.ctmpl"
  destination = "/etc/haproxy/haproxy.cfg"
  perms       = 0644
  command     = ["systemctl", "reload", "haproxy"]
}

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

We can declare many template blocks to track other services. To know more about these parmeters defined in this /etc/consul-template/config.hcl file, please have a look this docs.

Now, we continue creating /etc/systemd/system/consul-template.service, a systemd service config file for controlling consul-template operations, run:

sudo nano /etc/systemd/system/consul-template.service

And add this content

[Unit]
Description=Consul-Template Daemon Service
Wants=basic.target
After=basic.target network.target

[Service]
User=consul
Group=consul
ExecStart=sudo /usr/bin/consul-template \
  -config /etc/consul-template
SuccessExitStatus=12
ExecReload=/bin/kill -SIGHUP $MAINPID
ExecStop=/bin/kill -SIGINT $MAINPID
KillMode=process
Restart=always
RestartSec=42s
LimitNOFILE=4096

[Install]
WantedBy=multi-user.target

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

To reload systemd to load a new service configuration file, run:

sudo systemctl daemon-reload

Remember to check the real path of the consul-template command by simply running which consul-template. I installed consul-template through apt, so it’s /usr/bin/consul-template in my case. If you manually download the consul-template binary file, it should correspond to the path you put the binary file in, e.g., /usr/local/bin/consul-template.

Then add sudo permission to allow the consul user to run consul-template as a sudo user. Run:

sudo nano /etc/sudoers.d/sudo-consul

And add this content:

consul ALL = (root) NOPASSWD: /usr/bin/consul-template *

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

Make sure you can execute consul-template as sudo user under the consul user. Run this command to test:

sudo -u consul sudo consul-template --version

If it outputs version without asking for password, then you’re good.

Start and enable consul-template.service to start generating haproxy.cfg file.

sudo systemctl start consul-template
sudo systemctl enable consul-template

Once we started consul-template service, to check the content of /etc/haproxy/haproxy.cfg generated by consul-template. We run sudo cat /etc/haproxy/haproxy.cfg, in my example, it should become:

Ok. We’re done the configuration. Now, we move on testing part.

Testing

Here, we will make requests to 127.0.0.1:3307 (We defined in the haproxy config) to test the Load Balancer. Your script or application should remain on the same server (is app-01 in this case) where you installed HAProxy in order 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

On app-01 server, we run:

for i in `seq 1 10`; 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 to: server_id 1 is db-01, server_id 2 is db-02, and server_id 3 is db-03. With db-02 and db-03 having a weight of 100 (receiving the most traffic load) while db-01 has a weight of 30 (receiving small amount of traffic load).

Now, I will stop the MySQL service on db-02 to demonstrate how Consul updates the HAProxy config. On db-02, run:

sudo systemctl stop mysql

If I go back Consul Web UI to check MySQL service status, I will see MySQL on db-02 is down now this:

Now, on app-01 server, I run sudo cat /etc/haproxy/haproxy.cfg, I will see db-02 is removed from the config file has been already. See below:

Test querying by running this command again:

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

Output now looks like:

If we start MySQL service back on db-02 server, HAProxy config file should be automatically updated again. Feel free to play more to understand the logic of this implementation.

My Thoughts

That’s all we need for this implementation. There are still a few manual steps for creating Consul service config files (like the MySQL service in the example above) to register these services. We may think about adding the process to any tool like Puppet, Ansible, Chef, etc. That’s when we provision a server; these automation tools will set up Consul service config files on the targeted server. That will save us a huge amount of effort when you’re in a dynamic environment, e.g., when you have to scale out these slave DB servers more often to meet the demand. The new slave DB server is automatically updated in HAProxy config file then.

I hope this will give you some ideas in designing HAProxy for not only MySQL but also other services.