##!/bin/bash
##last edit 2010-08-05

#script to check MySQL integrity of given database on a specified MySQL Server by D. Spangenberger (C)2010
#Make sure, MySQL Server responds zu external request by disabling 'bind-adresse = 127.0.0.1' in /etc/mysql/my.cnf (or where else your config is stored)

#NOTE:
#This script is intend to be used with nagios monitoring system, 
#but it might be used standalone as well with some modifications, e.g. always deleting TMP_FILE (see NOTE2 for more information) but emailing it to the administrator

#NOTE2:
#After an error occured, you need to manually delete the file specified by TMP_FILE variable, to get rid of the "critical" status code, after repairing the database;
#or call this script (e.g. as nagios event_handler) with the optional [-r] parameter, to make mysqlcheck try to fix the currupted database
#This is to make you able to later refer to the occured problem, even when the database is already repaired (e.g. by MySQL itself)

#NOTE3
#to use the [-r] option you must give login credentials for a user who has write-permission on the specified database!

#variables
DATESTAMP=`date +%Y-%m-%d_%H%M%S`
TMP_FILE=check_mysql_$1_$2.log
EMAIL="<enter your e-mail here>"

#error handling - usage errors
#check parameters
if [ $# -lt 4 ]; then
	echo "Not enough Parameters given! Usage: ./check_mysql \$host \$database \$user \$password [-r]"
	
	#exit with status "unknown"
	exit 3
fi

if [ $# -gt 5 ]; then
	echo "Too much Parameters given! Usage: ./check_mysql \$host \$database \$user \$password [-r]"
	
	#exit with status "unknown"
	exit 3
fi

if [ ! $5 = "-r" ]; then
	echo "Wrong optional parameter! Usage: ./check_mysql \$host \$database \$user \$password [-r]"
	exit 3
fi

#backup previous TMP_FILE when running in repair-mode (s. NOTE2)
if [ ! $5 = "" ]; then
mv $TMP_FILE $TMP_FILE.$DATESTAMP
fi

#run mysqlcheck and append output to TMP_FILE
#IMPORTANT: choose ONE of the following command lines NOT BOTH

#silent - output to TMP_FILE only
#/usr/bin/mysqlcheck -h $1 --user="$3" --password="$4" -B $2 $5 >> $TMP_FILE

#verbose - clone output to screen
/usr/bin/mysqlcheck -h $1 --user="$3" --password="$4" -B $2 $5 | tee $TMP_FILE

#check wether TMP_FILE is existent
if [ -e $TMP_FILE ]; then
	
	#get length of TMP_FILE
	TMP_FILE_SIZE=`cat $TMP_FILE | wc -l`
	#echo $TMP_FILE_SIZE
	#if length greater then zero then analyse, else exit with error
	if [ ! $TMP_FILE_SIZE -eq 0 ]; then
		
		#analyze TMP_FILE for errors (anything else than "OK" is an error!) - option '-v' inverts output of grep - 'wc -l' counts lines
		STATUS=`cat $TMP_FILE | grep -v "OK" | wc -l`
	else
		
		#TMP_FILE is empty -> exit with error
		echo " TMP_FILE is empty! ($TMP_FILE)"
		exit 2
	
	fi

else
	#TMP_FILE is not existent -> exit with error
	echo "Unable to locate TMP_FILE! ($TMP_FILE)"
	exit 2

fi


if [ ! $STATUS -eq 0 ]; then
        ERROR=`cat $TMP_FILE | grep -v "OK"`
	
	#uncomment the following line if you want the TMP_FILE to be send to the contact specified in $EMAIL (s. NOTES above)
	cat $TMP_FILE | mutt -s "mysqlcheck result for DB $2 on $1" -a $TMP_FILE $EMAIL

	#exit with status "critical" (without deleting TMP_FILE; default)
	
	#uncomment the following line if you want TMP_FILE to be deleted even if errors where found (s. NOTES above)
	#rm $TMP_FILE
	
	echo "Errors found in Database $2 on $1! - $ERROR"	
	exit 2
else
	echo "Database Integrity check for $2 on $1 passed!"
	
	#uncomment following line if you want to receive e-mail even if no errors where found (debugging purpose)
	#cat $TMP_FILE | mutt -s "mysqlcheck result for DB $2 on $1" -a $TMP_FILE $EMAIL
	
	#delete temp file after analyzing
	rm $TMP_FILE
	
	#exit with status "OK"
	exit 0
fi

#if everything fails exit with status "unknown"
echo "unknown error!"
exit 3