HAProxy Galera Monitor Script

clustercheck.sh

#!/bin/bash
#
# Script to make a proxy (ie HAProxy) capable of monitoring Galera Cluster nodes properly
# Updated by Geoffrey McRae to check for MySQL command failure, and renamed to 'Galera Cluster'
#
# Author: Olaf van Zandwijk <olaf.vanzandwijk@nedap.com>
# Author: Raghavendra Prabhu <raghavendra.prabhu@percona.com>
# Author: Geoffrey McRae <geoff@hostfission.com>
#
# Documentation and download: https://github.com/olafz/percona-clustercheck
#
# Based on the original script from Unai Rodriguez
#

if [[ $1 == '-h' || $1 == '--help' ]];then
    echo "Usage: $0 <user> <pass> <available_when_donor=0|1> <log_file> <available_when_readonly=0|1> <defaults_extra_file>"
    exit
fi

function ok()
{
    local LEN=${#1}
    let LEN=$LEN+2

    echo -en "HTTP/1.1 200 OK\r\n"
    echo -en "Content-Type: text/plain\r\n"
    echo -en "Connection: close\r\n"
    echo -en "Content-Length: $LEN\r\n"
    echo -en "\r\n"
    echo -en "$1\r\n"
    sleep 0.1
    exit 0
}

function bad()
{
    local LEN=${#1}
    let LEN=$LEN+2

    echo -en "HTTP/1.1 503 Service Unavailable\r\n"
    echo -en "Content-Type: text/plain\r\n"
    echo -en "Connection: close\r\n"
    echo -en "Content-Length: $LEN\r\n"
    echo -en "\r\n"
    echo -en "$1\r\n"
    sleep 0.1
    exit 1
}

# if the disabled file is present, return 503. This allows
# admins to manually remove a node from a cluster easily.
if [ -e "/var/tmp/clustercheck.disabled" ]; then
    bad 'Galera Cluster Node is manually disabled'
fi

MYSQL_USERNAME="${1-clustercheckuser}"
MYSQL_PASSWORD="${2-clustercheckpassword!}"
AVAILABLE_WHEN_DONOR=${3:-0}
ERR_FILE="${4:-/dev/null}"
AVAILABLE_WHEN_READONLY=${5:-1}
DEFAULTS_EXTRA_FILE=${6:-/etc/my.cnf}

#Timeout exists for instances where mysqld may be hung
TIMEOUT=10

EXTRA_ARGS=""
if [[ -n "$MYSQL_USERNAME" ]]; then
    EXTRA_ARGS="$EXTRA_ARGS --user=${MYSQL_USERNAME}"
fi
if [[ -n "$MYSQL_PASSWORD" ]]; then
    EXTRA_ARGS="$EXTRA_ARGS --password=${MYSQL_PASSWORD}"
fi
if [[ -r $DEFAULTS_EXTRA_FILE ]];then
    MYSQL_CMDLINE="mysql --defaults-extra-file=$DEFAULTS_EXTRA_FILE -nNE --connect-timeout=$TIMEOUT \
                    ${EXTRA_ARGS}"
else
    MYSQL_CMDLINE="mysql -nNE --connect-timeout=$TIMEOUT ${EXTRA_ARGS}"
fi
#
# Perform the query to check the wsrep_local_state
#
CMD_OUTPUT=$($MYSQL_CMDLINE -e "SHOW STATUS LIKE 'wsrep_local_state';" 2>${ERR_FILE})
if [ $? -ne 0 ]; then
    bad 'MySQL command failed'
fi

WSREP_STATUS=$(echo "$CMD_OUTPUT" | tail -1 2>>${ERR_FILE})

if [[ "${WSREP_STATUS}" == "4" ]] || [[ "${WSREP_STATUS}" == "2" && ${AVAILABLE_WHEN_DONOR} == 1 ]]
then
    # Check only when set to 0 to avoid latency in response.
    if [[ $AVAILABLE_WHEN_READONLY -eq 0 ]];then
        READ_ONLY=$($MYSQL_CMDLINE -e "SHOW GLOBAL VARIABLES LIKE 'read_only';" \
                    2>${ERR_FILE} | tail -1 2>>${ERR_FILE})

        if [[ "${READ_ONLY}" == "ON" ]];then
            # Galera Cluster node local state is 'Synced', but it is in
            # read-only mode. The variable AVAILABLE_WHEN_READONLY is set to 0.
            # => return HTTP 503
            # Shell return-code is 1
            bad 'Galera Cluster Node is read-only'
        fi
    fi
    # Galera Cluster node local state is 'Synced' => return HTTP 200
    # Shell return-code is 0
    ok 'Galera Cluster Node is synced'
else
    # Galera Cluster node local state is not 'Synced' => return HTTP 503
    # Shell return-code is 1
    bad 'Galera Cluster Node is not synced'
fi

/etc/xinetd.d/mysqlchk

# default: on
# description: mysqlchk
service mysqlchk
{
  type            = UNLISTED
  disable         = no
  flags           = REUSE
  socket_type     = stream
  port            = 9200
  wait            = no
  user            = nobody
  server          = /usr/local/bin/clustercheck
  server_args     = clustercheck SECRET 1
  log_on_failure += USERID
  only_from       = 127.0.0.1/32 192.168.0.0/24
  bind            = 127.0.0.1 192.168.0.11
  per_source      = UNLIMITED
}

/etc/haproxy/haproxy.cfg

global
  chroot  /var/lib/haproxy
  daemon  
  group  haproxy
  log  localhost local0
  maxconn  4000
  pidfile  /var/run/haproxy.pid
  user  haproxy

defaults
  log  global
  maxconn  8000
  option  redispatch
  retries  1
  timeout  http-request 10s
  timeout  queue 1m
  timeout  connect 10s
  timeout  client 1m
  timeout  server 1m
  timeout  check 10s

listen mysql
  bind 192.168.0.10:3306 
  mode tcp
  balance leastconn
  option httpchk
  server db1.xxx.com 192.168.0.11:3306 check port 9200
  server db2.xxx.com 192.168.0.12:3306 check port 9200 backup