#!/bin/bash## #automysqlcheck.sh## This is a small bash script that checks all mysql databases for errors# and mails a log file to a specified email address. All variables are# hardcoded for ease of use with cron. Any databases you wish not to check# should be added to the DBEXCLUDE list, with a space in between each name.## Note that DBEXCLUDE will only work with GNU sed, as BSD regular expressions# on Darwin seem to have some trouble with word boundary anchors.## original version by sbray@csc.uvic.ca, UVic Fine Arts 2004## modified by eyechart AT gmail.com and Mickael Sundberg at mickael@pischows.se and Jake Carr jake-+AT+-websitesource-+DOT+-com# (see Change Log for details)## Some of this code was inspired from automysqlbackup.sh.2.0# http://sourceforge.net/projects/automysqlbackup/##=====================================================================# Change Log#=====================================================================## VER 1.3 - (2006-12-02)# Added --host=$DBHOST in mysql commands, so it's useful for non-localhost situations# Jake Carr# VER 1.2 - (2006-10-29)# Added "\`" arround the tables in $DBTABLES, otherwise it'll create# errors if tablenames containt characters like -.# Modified by Mickael Sundberg# VER 1.1 - (2005-02-22)# Named script automysqlcheck.sh# Added PATH variable to make this script more CRON friendly# Removed the $DBTABLES loop and replaced it with single command# that executes the CHECK TABLE command on all tables in a given DB# Changed code to only check MyISAM and InnoDB tables# Cleaned up output to make the email prettier# Modified script to skip databases that have no tables# Modified by eyechart# VER 1 - (2004-09-24)# Initial release by sbray@csc.uvic.ca# system variables (change these according to your system)PATH=/usr/local/bin:/usr/bin:/bin:$PATHUSER=rootPASSWORD=DBHOST=localhostLOGFILE=/var/log/automysqlcheck.logMAILTO=root@localhostTYPE1= # extra params to CHECK_TABLE e.g. FASTTYPE2=CORRUPT=no # start by assuming no corruptionDBNAMES="all" # or a list delimited by spaceDBEXCLUDE="" # or a list delimited by space# I/O redirection...touch $LOGFILEexec 6>&1exec > $LOGFILE # stdout redirected to $LOGFILEecho -n "AutoMySQLCheck: "dateecho "---------------------------------------------------------"; echo; echo# Get our list of databases to check...# NOTE: the DBEXCLUDE feature seemed to only work with Linux regex, GNU sedif test $DBNAMES = "all" ; thenDBNAMES="`mysql --host=$DBHOST --user=$USER --password=$PASSWORD --batch -N -e "show databases"`"for i in $DBEXCLUDEdoDBNAMES=`echo $DBNAMES | sed "s/\b$i\b//g"`donefi# Run through each database and execute our CHECK TABLE command for all tables# in a single pass - eyechartfor i in $DBNAMESdo# echo the database we are working onecho "Database being checked:"echo -n "SHOW DATABASES LIKE '$i'" | mysql -t --host=$DBHOST -u$USER -p$PASSWORD $i; echo# Check all tables in one pass, instead of a loop# Use GAWK to put in comma separators, use SED to remove trailing comma# Modified to only check MyISAM or InnoDB tables - eyechartDBTABLES="`mysql --host=$DBHOST --user=$USER --password=$PASSWORD $i --batch -N -e "show table status;" | gawk 'BEGIN {ORS=", " } $2 == "MyISAM" || $2 == "InnoDB"{print "\`" $1 "\`"}' | sed 's/, $//'`"# Output in table form using -t optionif [ ! "$DBTABLES" ]thenecho "NOTE: There are no tables to check in the $i database - skipping..."; echo; echoelseecho "CHECK TABLE $DBTABLES $TYPE1 $TYPE2" | mysql --host=$DBHOST -t -u$USER -p$PASSWORD $i; echo; echofidoneexec 1>&6 6>&- # Restore stdout and close file descriptor #6# test our logfile for corruption in the database...for i in `cat $LOGFILE`doif test $i = "warning" ; thenCORRUPT=yeselif test $i = "error" ; thenCORRUPT=yesfidone# Send off our results...if test $CORRUPT = "yes" ; thencat $LOGFILE | mail -s "MySQL CHECK Log [ERROR FOUND] for $DBHOST-`date`" $MAILTOelsecat $LOGFILE | mail -s "MySQL CHECK Log [PASSED OK] for $DBHOST-`date`" $MAILTOfi