Thursday 4 February 2010

mysqlcheck automatic script

This script check tables in the database. Also It captures error messages into log file. you can customize this script to send an alert via email etc.



#!/bin/sh

# This script check tables in the database.
# Additionaly you can supply a filter to check only certain tables.

user=root
pass=xxxxxx
db=mydb
#filter=

mysql="/usr/bin/mysql -u$user -p$pass -s $db"
stats_method="SET myisam_stats_method=nulls_equal"

# Ok now do the check on each table
echo "Checking database $db ..."
echo "show tables like \"${filter}\"" | $mysql | while read table
do
echo "$stats_method;check table $table\G" | $mysql > ~/scripts/query_output.log
error=`cat ~/scripts/query_output.log | grep Error`
count=`expr length "$error"`

if [ "$count" != "0" ]; then

echo "--------------------- Check Table Report ($table) `date`" >> ~/scripts/bad_tables.log
tail --lines=4 ~/scripts/query_output.log >> ~/scripts/bad_tables.log
echo "------------------------------------------------------------------------------------" >> ~/scripts/bad_tables.log

fi
done

echo "Checking database $db finished..."

Now run the script

shell# ./check_table.sh

let's see if there is any bad table..

shell# more bad_tables.log

2 comments:

  1. nice script! thanks!

    but maybe

    echo "$stats_method;check table $table\G" | $mysql > ~/scripts/query_output.log

    should be :

    echo "$stats_method;check table $table\G" | $mysql >> ~/scripts/query_output.log

    ReplyDelete
  2. No this is not right, actually, query_output.log file is used to capture output of one table status at a time i.e. via 'check table' but, you can modify this script according to your requirements.

    ReplyDelete