7.12.5.2. 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\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;

You may want to configure the actual check in the /opt/continuent/share/connectorchk.sh script, which is 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 xinetd, 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.