Unbreakable MySQL Cluster with Galera and Linux Virtual Server (LVS)

Recently we had to set-up a 3-node Galera Cluster with a Load Balancer in front of it. Because Galera Cluster nodes (mysqld) still reply to TCP requests on port 3306 when they are expelled from the Cluster it is not sufficient to just leave it to the Load Balancer to check the port if a Galera node is properly running or not.

We used the wsrep_notify_cmd variable to hook our own script into the Galera Cluster which disables each Node on the Load Balancer when its state changed.

# my.cnf
#
[mysqld]
wsrep_notify_cmd = /usr/local/bin/lvs_control.sh

The whole Galera Cluster Architecture looks as follows:

As Load Balancer we used the IPVS Load Balancer from the Linux Virtual Server (LVS) Project. This Load Balancer was made highly available with keepalived.

Our script to take a Galera Node out of the Load Balancer was the following:

#!/bin/bash -eu

#
# /etc/mysql/conf.d/wsrep.cnf
#
# [mysqld]
# wsrep_notify_cmd = /usr/local/bin/lvs_control.sh
#

LOG="/tmp/lvs_control.log"
LBIP="192.168.0.89"
VIP="192.168.0.99"
PORT="3306"
LBUSER="galera"
LBUSER="root"
ETC="/etc/mysql/conf.d/wsrep.cnf"
ETC="/home/mysql/data/mysql-5.5-wsrep-23.7-a/my.cnf"
MYIP=''
WEIGHT="100"
DATE=$(date '+%Y-%m-%d %H:%M:%S')

echo $DATE >>$LOG

regex='^.*=\s*([0-9]+.[0-9]+.[0-9]+.[0-9]+).*'
str=$(grep "^wsrep_node_incoming_address" $ETC 2>>$LOG)

if [[ $str =~ $regex ]] ; then
  MYIP=${BASH_REMATCH[1]}
else
  echo "Cannot find IP address in $str" >>$LOG
  exit 1
fi

while [ $# -gt 0 ] ; do

  case $1 in
  --status)
    STATUS=$2
    shift
    ;;
  --uuid)
    CLUSTER_UUID=$2
    shift
    ;;
  --primary)
    PRIMARY=$2
    shift
    ;;
  --index)
    INDEX=$2
    shift
    ;;
  --members)
    MEMBERS=$2
    shift
    ;;
  esac
  shift
done

# echo $* >> $LOG
echo $STATUS >> $LOG

# Undefined means node is shutting down
# Synced means node is ready again
if [ "$STATUS" != "Synced" ] ; then
  cmd="ssh $LBUSER@$LBIP 'sudo /sbin/ipvsadm -e -t $VIP:$PORT -r $MYIP -w 0'"
else
  cmd="ssh $LBUSER@$LBIP 'sudo /sbin/ipvsadm -e -t $VIP:$PORT -r $MYIP -w $WEIGHT'"
fi

echo $cmd >>$LOG
eval $cmd >>$LOG 2>&1
echo "ret=$?" >>$LOG

exit 0

We assume that the same script can be used with little modifications for the Galera Load Balancer as well.