In Depth MySQL Checks for HAProxy With Systemd Socket-Based Activation

Introduction

HAProxy checks, while super-useful, are limited and you might not find exactly what you need in the list of available probes.

The MySQL health checks available natively in HAProxy are not deep enough for our needs.

At Continuent, we wanted to actually execute a SQL query before considering the server as ready to serve requests. Our challenge was to be able to extend HAProxy's health checks and do an in-depth verification of the MySQL server before routing to it.

So we needed a configurable, somewhat complex (script-based) check that HAProxy would call to determine if a backend was usable or not.

We've had an earlier solution based on xinetd that, in collaboration with one of our customers, evolved into a more modern systemd socket-based call.

Implementation

The full documentation for systemd socket unit configuration can be found here.

What we'll do is define a socket unit and its associated service unit. We configure it so that each time a connection is made to the given socket, the service will be triggered. This service will simply call the bash script of your choice.

Create the socket unit

Our scripts will be named connectorchk.sh, but you can give them any name you prefer.

Create the file /etc/systemd/system/connectorchk.socket with the following contents:

[Unit]
Description=Connector health check (socket-activated)

[Socket]
# Listen on all IPv4 addresses on TCP/9200 (add another ListenStream for IPv6 if desired)
ListenStream=0.0.0.0:9200
# Spawn a service instance per connection, with the socket on stdin/stdout
Accept=yes
NoDelay=true

[Install]
WantedBy=sockets.target

Create the service unit

Create the file /etc/systemd/system/connectorchk@.service containing:

[Unit]
Description=Connector health check (per-connection instance)
After=network.target

[Service]
Type=simple
User=tungsten
Group=tungsten
ExecStart=/opt/continuent/share/connectorchk.sh

# Make the accepted TCP socket the script's stdin/stdout (inetd-style)
StandardInput=socket
StandardOutput=socket
StandardError=journal

# Optional: set a working directory if your script expects it
# WorkingDirectory=/opt/continuent/share

# --- Optional hardening (uncomment as your script allows) ---
# NoNewPrivileges=true
# PrivateTmp=true
# ProtectSystem=full
# ProtectHome=true
# RestrictAddressFamilies=AF_INET AF_INET6
# RestrictRealtime=true
# LockPersonality=true
# MemoryDenyWriteExecute=true

[Install]
WantedBy=sockets.target

Create the shell script

Finally your custom script /opt/continuent/share/connectorchk.sh will contain whatever functionalities you want to give it. Here is an example:

#!/bin/sh
#
# This script checks if a mysql server is healthy running on localhost. It will
# return:
# "HTTP/1.x 200 OK\r" (if connector is running smoothly)
# - OR -
# "HTTP/1.x 503 Service Unavailable\r" (else)
#
# The purpose of this script is make haproxy capable of monitoring mysql properly
#

MYSQL_BIN=`which mysql`
MYSQL_HOST=`hostname`
MYSQL_PORT="3306"
MYSQL_USERNAME="haproxy"
MYSQL_PASSWORD="secret"
MYSQL_OPTS="-N -q -A"

#If you create the following file, the proxy will return mysql down
#routing traffic to another host
FORCE_FAIL="/dev/shm/proxyoff"
OUT=""
return_ok()
{
printf "HTTP/1.1 200 OK\r\n"
printf "Content-Type: text/plain\r\n"
printf "\r\n"
printf "Connector is running.\r\n"
printf "\r\n"
exit 0
}
return_fail()
{
printf "HTTP/1.1 503 Service Unavailable\r\n"
printf "Content-Type: text/plain\r\n"
printf "\r\n"
printf "Connector is *down*.\r\n"
printf "$OUT\r\n\r\n"
exit 1
}
if [ -f "$FORCE_FAIL" ]; then
    OUT="$FORCE_FAIL found"
    return_fail;
fi

OUT=`$MYSQL_BIN $MYSQL_OPTS --host="$MYSQL_HOST" --port="$MYSQL_PORT" --user="$MYSQL_USERNAME" \
--password="$MYSQL_PASSWORD" -e "select @@hostname;" 2>&1`

if [ $? -ne 0 ]; then
    return_fail;
fi

return_ok;

Install the service

You can now set permissions for the check scripts:

shell> chown tungsten.tungsten /opt/continuent/share/connectorchk.sh
shell> chmod 711 /opt/continuent/share/connectorchk.sh

And configure systemd to add the service:

sudo systemctl daemon-reload
sudo systemctl enable --now connectorchk.socket

Verification

To verify that the connector check service is running via systemd, connect to port 9200 using the telnet command:

shell> telnet localhost 9200

You should get a response similar to this:

HTTP/1.1 200 OK

Content-Type: text/plain


Connector is running.

Configure HAProxy

HAProxy can now be tweaked to use the new port 9200 to check for service health. As an example, suitable MySQL check script configuration can be added to a basic HAProxy installation using the following settings:

frontend connector
  bind *:3306
  default_backend cluster
#---------------------------------------------------------------------
# backend
#---------------------------------------------------------------------
# HTTP health backend that talks to the systemd socket on 9200
backend conn_health
  mode http
  option httpchk GET /health
  http-check expect status 200
  default-server inter 2s fall 3 rise 2

  server conn1 db1:9200 check
  server conn2 db2:9200 check
  server conn3 db3:9200 check

# Actual MySQL traffic backend (TCP) that reuses the health state
backend cluster
  mode tcp
  # Inherit UP/DOWN from the HTTP backend above; don't also set "check" here.
  server conn1 db1:3306 track conn_health/conn1
  server conn2 db2:3306 track conn_health/conn2
  server conn3 db3:3306 track conn_health/conn3

  default-server on-marked-down shutdown-sessions
  timeout check 2s

Wrap-Up

With this, we're now able to extend HAProxy's health checks and do an in-depth verification of the MySQL server before routing to it. By basing failover on an actual SQL query instead of a shallow TCP probe, HAProxy makes smarter, more accurate decisions about server availability. Using a systemd‑activated script keeps the design simple and flexible, while ensuring that checks reflect the database’s real state. In production, this may translate into fewer surprises, faster responses to issues, and health checks that reflect the reality of your database rather than just its TCP port.

Smooth Sailing!