MySQL Monitor

Problem this snippet solves:

This EAV takes seven option values in the argument field - database name, username, password, table and colum. The default for these areguments is 'healthcheck' The seventh optional parameter is the timeout which defaults to 3 seconds. The purpose of this script is to interact with the database by dropping the last entry really the only entry, insert a new entry based off a random number and then selects and validates that random number generated and inserted into the database. This seems a little repetitive but what the heck....

Code :

#!/bin/bash
# based on original contribution by kirkbauer
# 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 seven option values in the argument field -- database name, username,
# password, table and colum. The default for these areguments is 'healthcheck' 
# The seventh optional parameter is the timeout which defaults to
# 3 seconds. The purpose of this script is to interact with the database by 
# dropping the last entry really the only entry, insert a new entry based off a random number
# and then selects and validates that random number generated and inserted 
# into the database. This seems a little repetitive but what the heck....

# 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. By utilizing only 
# the mysql-client I was able to get this to work on a BIG-IP 1500 but this is not
# supported.
#####ARGs
member_ip=$(echo "$1" | sed 's/::ffff://')
member_port="${2:-3306}"
mysql_db="${3:-healthcheck}"
mysql_user="${4:-healthcheck}"
mysql_password="${5:-healthcheck}"
table="${6:-healthcheck}"
col1="${7:-healthcheck}"
timeout="${8:-3}"
number=$RANDOM


############pid file stuff
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"

#################Delete the last insertion

if echo 'DELETE FROM '$table' WHERE status is NOT NULL;' | mysql -P $member_port -u "$mysql_user" -h $member_ip \
--password="$mysql_password" --database=$mysql_db --connect_timeout=$timeout 2>"$tmpfile" ; then

##################Insert a new randomly generated number

if echo 'INSERT INTO '$table' ('$col1' , 'time') VALUES ('$number',NOW());' | mysql -P $member_port -u "$mysql_user" -h $member_ip \
--password="$mysql_password" --database=$mysql_db --connect_timeout=$timeout; then

###############Check to see if that number exist in the database... 
if echo 'SELECT * FROM '$table' WHERE status is NOT NULL;' | mysql -P $member_port -u "$mysql_user" -h $member_ip \
--password="$mysql_password" --database=$mysql_db --connect_timeout=$timeout 2>"$tmpfile" | grep -q $number ; then

rm -f "$pidfile"
rm -f "$tmpfile"
echo "up"
fi
fi
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
echo "down" >&2
cat "$tmpfile" >&2
rm -f "$pidfile"
rm -f "$tmpfile"
exit 1
fi