mysqlchk_iptables

#!/bin/bash
#
# A background script use to check Galera node availability and add a port redirection using
# iptables if Galera node is healthy. Derived from percona-clustercheck, but utilizing iptables
# port redirection instead of returning HTTP response. This allows TCP load balancer to perform
# health checks without custom monitoring port (percona-clustercheck runs on 9200 through xinetd).
#
# Author: Ashraf Sharif <ashraf@severalnines.com>
#
# Documentation and download: https://github.com/ashraf-s9s/clustercheck-iptables/
#
# Based on the original script from percona-clustercheck
#

## Defaults
MIRROR_PORT=3308
REAL_PORT=3306
SOURCE_ADDRESS="0.0.0.0/0"
SOURCE_ADDRESS_IPV6="0::0/0"
CHECK_INTERVAL=1
MYSQL_USERNAME="mysqlchk_user"
MYSQL_PASSWORD="mysqlchk_password"
AVAILABLE_WHEN_DONOR=0
LOG_FILE="/var/log/mysqlchk_iptables"
AVAILABLE_WHEN_READONLY=0
DEFAULTS_EXTRA_FILE="/etc/my.cnf"
VERBOSE=0
ERR_FILE="/dev/null"
PROG_NAME="mysqlchk_iptables"

TEMP=`getopt -o h,v,d,x,s,u:,p:,l:,e:,a:,a6:,m:,r:,i:,t,R,D --long help,verbose,daemon,stop,status,user:,password:,log-file:,defaults-extra-file:,source-address:,source-address-ipv6:,mirror-port:,real-port:,check-interval:,test,run,debug -n 'mysqlchk_iptables' -- "$@"`
eval set -- "$TEMP"

while true ; do
  case "$1" in
    -h|--help) HELP="1" ; shift ;;
    -v|--verbose) VERBOSE="1" ; shift ;;
    -d|--daemon) DAEMON="1" ; shift ;;
    -x|--stop) STOP="1" ; shift ;;
    -s|--status) STATUS="1"; shift ;;
    -u|--user) MYSQL_USERNAME="$2" ; shift 2 ;;
    -p|--password) MYSQL_PASSWORD="$2" ; shift 2 ;;
    -l|--log-file) LOG_FILE="$2" ; shift 2 ;;
    -e|--defaults-extra-file) DEFAULTS_EXTRA_FILE="$2"; shift 2 ;;
    -a|--source-address) SOURCE_ADDRESS="$2"; shift 2 ;;
    -a6|--source-address-ipv6) SOURCE_ADDRESS_IPV6="$2"; shift 2 ;;
    -m|--mirror-port) MIRROR_PORT="$2"; shift 2 ;;
    -r|--real-port) REAL_PORT="$2" ; shift 2 ;;
    -i|--check-interval) CHECK_INTERVAL="$2" ; shift 2 ;;
    -t|--test) TEST="1"; shift ;;
    -R|--run) RUN="1"; shift ;;
    -D|--debug) DEBUG="1"; shift ;;
    --) shift ; break ;;
    *) echo "$0: error - unrecognized option -$OPTARG" 1>&2; exit 1;;
  esac
done

help ()
{
  echo ""
  echo "Options:"
  echo "-v, --verbose              : Run this script in foreground. CTRL+C to stop."
  echo "-d, --daemon               : Run this script as daemon."
  echo "-x, --stop                 : Stop this script if it is running as daemon."
  echo "-s, --status               : Report the status of this script."
  echo "-t, --test                 : Test the script."
  echo "-u, --user                 : MySQL user to perform health check. (Default: mysqlchk_user)"
  echo '-p, --password             : Password for the MySQL user (Default: mysqlchk_password)'
  echo "-l, --log-file             : Log file (Default: /var/log/mysqlchk_iptables)"
  echo "-e, --defaults-extra-file  : This file (if exists) will be passed to the mysql-command with the command line option --defaults-extra-file (Default: /etc/my.cnf)"
  echo "-a, --source-address       : The source IP address to allow accessing the mirror port by iptables. (Default: 0.0.0.0/0)"
  echo "-a6, --source-address-ipv6 : The source IPv6 address to allow accessing the mirror port by ip6tables. (Default: 0::0)"
  echo "-m, --mirror-port          : This port will be created by iptables which redirects to the real port. (Default: 3308)"
  echo "-r, --real-port            : MySQL port listening on this node. (Default: 3306)"
  echo "-i, --check-interval       : Health check interval in seconds. (Default: 1)"
  echo "-h, --help                 : Print help"
  echo ""
  echo "Details at https://github.com/ashraf-s9s/clustercheck-iptables"
  echo ""
  exit 0
}

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

[ "$HELP" == "1" ] && help

# if the disabled file is present, delete iptables rule. This allows
# admins to manually remove a node from a cluster easily.
if [ -e "/var/tmp/clustercheck.disabled" ]; then
    iptables_rules delete
    logging "Galera Cluster Node is manually disabled."
fi

logging()
{
    if [[ $DEBUG == "1" ]]; then
        echo "[$(date +"%d-%m-%y %T.%N")] [INFO] $1"
    else
        echo "[$(date +"%d-%m-%y %T.%N")] [INFO] $1" >> $LOG_FILE
    fi
}

iptables_rules()
{
    OPT="-A"
    [ $1 == "delete" ] && OPT="-D"

    # check if the rule exists
    iptables -t nat -C PREROUTING -s $SOURCE_ADDRESS -p tcp --dport $MIRROR_PORT -j REDIRECT --to-ports $REAL_PORT &> /dev/null
    proc=$?

    if [[ $proc == 0 &&  "$OPT" == "-D" ]]; then
        iptables -t nat $OPT PREROUTING -s $SOURCE_ADDRESS -p tcp --dport $MIRROR_PORT -j REDIRECT --to-ports $REAL_PORT
        [ $? -eq 0 ] && logging "iptables rule deleted. $MIRROR_PORT removed." || logging 'Failed to delete iptables rule.'
    fi

    if [[ $proc == 1  &&  "$OPT" == "-A" ]]; then
        if [[ "$DRYRUN" != "1" ]]; then
            iptables -t nat $OPT PREROUTING -s $SOURCE_ADDRESS -p tcp --dport $MIRROR_PORT -j REDIRECT --to-ports $REAL_PORT
            [ $? -eq 0 ] && logging "iptables rule added. $MIRROR_PORT is redirected to $REAL_PORT". || logging 'Failed to add iptables rule.'
        else
            logging "(TEST) iptables rule added. $MIRROR_PORT is redirected to $REAL_PORT".
        fi
    fi

    ip6tables -t nat -C PREROUTING -s $SOURCE_ADDRESS_IPV6 -p tcp --dport $MIRROR_PORT -j REDIRECT --to-ports $REAL_PORT &> /dev/null
    proc=$?

    if [[ $proc == 0 &&  "$OPT" == "-D" ]]; then
        ip6tables -t nat $OPT PREROUTING -s $SOURCE_ADDRESS_IPV6 -p tcp --dport $MIRROR_PORT -j REDIRECT --to-ports $REAL_PORT
        [ $? -eq 0 ] && logging "ip6tables rule deleted. $MIRROR_PORT removed." || logging 'Failed to delete ip6tables rule.'
    fi

    if [[ $proc == 1  &&  "$OPT" == "-A" ]]; then
        if [[ "$DRYRUN" != "1" ]]; then
            ip6tables -t nat $OPT PREROUTING -s $SOURCE_ADDRESS_IPV6 -p tcp --dport $MIRROR_PORT -j REDIRECT --to-ports $REAL_PORT
            [ $? -eq 0 ] && logging "ip6tables rule added. $MIRROR_PORT is redirected to $REAL_PORT". || logging 'Failed to add ip6tables rule.'
        else
            logging "(TEST) ip6tables rule added. $MIRROR_PORT is redirected to $REAL_PORT".
        fi
   fi
}

main()
{
    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
    #
    WSREP_STATUS=$($MYSQL_CMDLINE -e "SHOW STATUS LIKE 'wsrep_local_state';" \
        2>${ERR_FILE} | tail -1 2>>${ERR_FILE})
    WSREP_SST=$($MYSQL_CMDLINE -e "SHOW VARIABLES LIKE 'wsrep_sst_method';" \
        2>${ERR_FILE} | tail -1 2>>${ERR_FILE})

    if [[ "$DRYRUN" == "1" ]]; then
        if [[ -z "$WSREP_STATUS" && -z "$WSREP_SST" ]]; then
            echo "Unable to detect wsrep_local_state and wsrep_sst_method. Something wasn't right."
            echo "Please check user/password. Ensure those are correct. Aborting."
            exit 1
        else
            echo "Detected variables/status:"
            echo "wsrep_local_state: $WSREP_STATUS"
            echo "wsrep_sst_method: $WSREP_SST"
        fi
    fi

    if [[ "${WSREP_STATUS}" == "4" ]] || [[ "${WSREP_STATUS}" == "2" && "${WSREP_SST}" =~ "xtrabackup" ]]
    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})
            [[ "$DRYRUN" == "1" ]] && echo "read_only: $READ_ONLY" && echo ""
            if [[ "${READ_ONLY}" == "ON" ]];then
                # Galera Cluster node local state is 'Synced', but it is in
                # read-only mode.
                # => delete iptables rule
                iptables_rules delete
                logging "Galera Cluster Node is read-only."
        else
            # Galera Cluster node local state is 'Synced' without read_only = ON
            # => add iptables rule
            iptables_rules add
            logging "Galera Cluster Node is synced."
            fi
        fi
    else
        # Galera Cluster node local state is not 'Synced'
        # => delete iptables rule
        iptables_rules delete
        logging "Galera Cluster Node is not synced."
    fi
}

REQ_ARGUMENTS="--user=${MYSQL_USERNAME} --password=${MYSQL_PASSWORD} --mirror-port=$MIRROR_PORT --real-port=$REAL_PORT --log-file=$LOG_FILE --source-address=$SOURCE_ADDRESS --source-address-ipv6=$SOURCE_ADDRESS_IPV6 --check-interval=$CHECK_INTERVAL --defaults-extra-file=$DEFAULTS_EXTRA_FILE"

ABSOLUTE_PATH=$(cd `dirname "${BASH_SOURCE[0]}"` && pwd)/$PROG_NAME
PID_PATH=/var/run
PID_FILE=$PID_PATH/$PROG_NAME

ctrl_c() {
    iptables_rules delete
    echo ""
    echo "$ABSOLUTE_PATH stopped. Bye."
    exit 1
}

if [[ $DAEMON == "1" ]]; then
    [ -e $PID_FILE ] && echo "PID file exists at $PID_FILE. Refusing to fork another process." && exit 1
    $ABSOLUTE_PATH $REQ_ARGUMENTS -R &
    PID=$!
    if [ $? -ne 0 ]; then
        logging "Unable to run as daemon. Exiting."
        exit 1
    else
        echo $PID > $PID_FILE
        logging "Script started with PID $PID."
        logging "Command used to start: $ABSOLUTE_PATH $REQ_ARGUMENTS"
	echo "$ABSOLUTE_PATH started with PID $PID."
        exit 0
    fi
elif [[ $STOP == "1" ]]; then
    echo "Stopping $PROG_NAME.."
    if [ ! -e $PID_FILE ]; then
        echo "PID file does not exist. Nothing to kill. Exiting."
        exit 1
    else
        EPID=$(cat $PID_FILE)
        kill $EPID
        [ $? -ne 0 ] && logging "Unable to kill $EPID. Exiting." && exit 1
        iptables_rules delete
        rm -f $PID_FILE
        logging "PID $EPID killed. $PID_FILE removed. Bye."
	echo "$ABSOLUTE_PATH stopped. Bye."
        exit 0
    fi
elif [[ $STATUS == "1" ]]; then
    if [ ! -e $PID_FILE ]; then
        echo "PID file does not exist. Script is not running."
        exit 1
    else
        EPID=$(cat $PID_FILE)
        echo "PID file exists. Script is running with PID $EPID."
        exit 0
    fi
elif [[ $VERBOSE == "1" ]]; then
    [ -e $PID_FILE ] && echo "PID file exists at $PID_FILE. Refusing to fork another process." && exit 1
    echo "Running in foreground.. CTRL+C to stop"
    trap ctrl_c INT
    $ABSOLUTE_PATH $REQ_ARGUMENTS -R -D
fi

if [[ $RUN == "1" ]]; then
    while true; do
        main
        sleep $CHECK_INTERVAL
    done
elif [[ $TEST == "1" ]]; then
    DEBUG="1"
    DRYRUN=1
    main
    sleep $CHECK_INTERVAL
else
    help
fi