Place the following script in a directory of your choice:


# 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, UVic Fine Arts 2004
# modified by eyechart AT (see Change Log for details)
# Some of this code was inspired from
# Change Log
# VER 1.1 – (2005-02-22)
# Named script
# 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
# VER 1 – (2004-09-24)
# Initial release by
# system variables (change these according to your system)
TYPE1= # extra params to CHECK_TABLE e.g. FAST
CORRUPT=no # start by assuming no corruption
DBNAMES=”all” # or a list delimited by space
DBEXCLUDE=”” # or a list delimited by space

# I/O redirection…
touch $LOGFILE
exec 6>&1
exec > $LOGFILE # stdout redirected to $LOGFILE

echo -n “AutoMySQLCheck: ”
echo “———————————————————“; echo; echo

# Get our list of databases to check…
# NOTE: the DBEXCLUDE feature seemed to only work with Linux regex, GNU sed
if test $DBNAMES = “all” ; then
DBNAMES=”`mysql –user=$USER –password=$PASSWORD –batch -N -e “show databases”`”
for i in $DBEXCLUDE
DBNAMES=`echo $DBNAMES | sed “s/\b$i\b//g”`

# Run through each database and execute our CHECK TABLE command for all tables
# in a single pass – eyechart
for i in $DBNAMES
# echo the database we are working on
echo “Database being checked:”
echo -n “SHOW DATABASES LIKE ‘$i'” | mysql -t -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 – eyechart
DBTABLES=”`mysql –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 option
if [ ! “$DBTABLES” ]
echo “NOTE: There are no tables to check in the $i database – skipping…”; echo; echo
echo “CHECK TABLE $DBTABLES $TYPE1 $TYPE2” | mysql -t -u$USER -p$PASSWORD $i; echo; echo

exec 1>&6 6>&- # Restore stdout and close file descriptor #6

# test our logfile for corruption in the database…
for i in `cat $LOGFILE`
if test $i = “warning” ; then
elif test $i = “error” ; then

# Send off our results…
if test $CORRUPT = “yes” ; then
cat $LOGFILE | mail -s “MySQL CHECK Log [ERROR FOUND] for $DBHOST-`date`” $MAILTO
cat $LOGFILE | mail -s “MySQL CHECK Log [PASSED OK] for $HOST-`date`” $MAILTO

*Make sure to update user, password, dbhost, log file location and DBNAMES/DBEXCLUE with your own details.

Now make the script executable (chmod +x and add a cronjob to run when required e.g.

01 20 * * * /home/user/bin/

*cron will run at 1.20am everyday.

Written by Matt Cooper
Hi, I'm Matt Cooper. I started this blog to pretty much act as a brain dump area for things I learn from day to day. You can contact me at: