MySQL Monitor Insert Random Number
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/: 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
Published Mar 12, 2015
Version 1.0JackofallTrades
Historic F5 Account
Joined January 28, 2008
JackofallTrades
Historic F5 Account
Joined January 28, 2008
No CommentsBe the first to comment