MYSQL Monitor

I was messing around with a mysql monitor and after loading the mysql client on a 1500 I modified a script by kirkbauer.

 

 

This is what I came up with...not pretty but it works... Any comments or sugestion to stream line this would be greatfull..

 

 

!/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

 

 

echo "up"

 

 

rm -f "$pidfile"

 

rm -f "$tmpfile"

 

exit 0

 

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