Tungsten Clustering

Configuring HAProxy with a Check Script Via systemd

✅ RECOMMENDED

A suitable MySQL check script configuration can be added to a basic HAProxy installation using the following settings:

#---------------------------------------------------------------------
# frontend
#---------------------------------------------------------------------
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

For correct operation with HAProxy, the external check script needs to be installed on all hosts running the Tungsten Connector.

This external script will respond to check calls by HAProxy to ensure the Connector is available and able to reach the database on that node.

This is typically done via the systemd listener configuration which executes the local check script each time the HAProxy daemon asks for status.

The check script will return the status of that Connector to HAProxy in the form of HTTP return codes.

Based on the default-server port 9200 entry in the configuration above, HAProxy will check via port 9200 on every defined backend host.

note

The hostname and port numbers in the above example should be modified to match your cluster configuration.

This is the procedure where systemd socket mode replaces xinetd.

Create and configure the three needed scripts on each host running Tungsten Connector:

/etc/systemd/system/connectorchk.socket

[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

/etc/systemd/system/connectorchk@.service

[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

/opt/continuent/share/connectorchk.sh

#!/bin/sh
#
# This script checks if a mysql server is healthy running on localhost. It will
# return:
# "HTTP/1.x 200 OK
" (if connector is running smoothly)
# - OR -
# "HTTP/1.x 503 Service Unavailable
" (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;

You may want to configure the actual check in the /opt/continuent/share/connectorchk.sh script, which is currently doing a connector status to see if the Tungsten Connector is running.

Set the permissions for the check scripts:

shell> chown tungsten.tungsten /opt/continuent/share/connectorchk.sh
shell> chmod 700 /opt/continuent/share/connectorchk.sh
shell> chmod +x /opt/continuent/share/connectorchk.sh

Configure systemd to add the service:

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

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

MySQL is running.
```