MySQL Monitor

Problem this snippet solves:

Uses the MySQL client to monitor a MySQL server by pulling the list of tables and verifying the existence of the mysql.users table.

Note: The mysql binary is included in the LTM build starting in version BIG-IP 9.2.0, and only then only on hard-disk partitions of the 6400 platform and higher. Installation of the mysql binary on other systems is not officially supported and may not survive an upgrade.

How to use this snippet:

1. Create a new file containing the code below in /usr/bin/monitors on the LTM filesystem. Permissions on the file must be 700 or better, giving root rwx access to the file.
2. Create a monitor profile of type "External" with the following values:
   • External Program: . . the name of the script file created in step 1
   • Arguments:
     • Optional: username. . . .UID to use for test (default is "healthcheck")
     • Optional: password. . . .password for UID (default is "healthcheck")
     • Optional: timeout . . . .timeout in seconds (default is 3)

Code :

#!/bin/bash

# You need to add a test user to your MySQL database as follows:
# mysql -u root -h W.X.Y.Z -p
# > GRANT SELECT ON mysql.* TO healthcheck IDENTIFIED BY 'healthcheck';
# > flush privileges;

# This EAV takes two optional values in the parameters field -- the username
# and the password to use when connecting to MySQL.  You can leave these
# blank and the default username 'healthcheck' and password 'healthcheck'
# will be used.  A third optional parameter is the timeout which defaults to
# 3 seconds. All this script does is a "show tables" and looks for 
# the mysql.user table. 

# This requires the mysql command on the BIG-IP which is only installed with 
# version 9.2.0 and higher, only on the BIG-IP 6400 and higher, and only
# when installing on the hard drive (not compact flash).  The reason is that
# MySQL is only part of the ASM/WebAccelerator modules and although you don't
# need them licensed or activated you need them installed.  There may be
# a way to manually get MySQL installed on the BIG-IP but that would not
# be supported.

member_ip=$(echo "$1" | sed 's/::ffff://')
member_port="${2:-3306}"
mysql_user="${3:-healthcheck}"
mysql_password="${4:-healthcheck}"
timeout="${5:-3}"

pidfile="/var/run/$MON_TMPL_NAME.$member_ip.$member_port.pid"
[ -f "$pidfile" ] && kill -9 $(cat $pidfile) >/dev/null 2>&1
rm -f "$pidfile" ; echo "$$" > "$pidfile"
tmpfile="/var/run/$MON_TMPL_NAME.$member_ip.$member_port.tmp"
rm -f "$tmpfile"

if echo 'show tables;' | mysql -P $member_port -u "$mysql_user" -h $member_ip \
--password="$mysql_password" --database=mysql --connect_timeout=$timeout 2>"$tmpfile" | grep -q user ; then
   rm -f "$pidfile"
   rm -f "$tmpfile"
   echo "up"
else
   # Log the reason for the failure
   logger -p local0.notice "$MON_TMPL_NAME($member_ip:$member_port) MySQL Healthcheck Failed: $(cat "$tmpfile")"
   # Echo to stderr for command-line testing
   rm -f "$pidfile"
   rm -f "$tmpfile"
   echo "down" >&2
   cat "$tmpfile" >&2
   exit 1
fi