F5 mysql monitor

 

Send String ：sql 语句

Receive String ：sql  回显

User Name  用户

Password   密码  

Database   数据库  :相当于  use database;

Receive Row

 一个测试增删改查的完整脚本

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

 

只测试数据正常的脚本

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