MySQL‎ > ‎

Corruption

MySQL, MyISAM, Table Corruption

if theres a corrupted table in a mysql database you just goto
cd /usr/local/mysql/data

and find the database ands its *.MYD and *.MYI files check to see if the db is corrupted

myisamchk -c db.MYI

fix if corrupted

myisamchk -r db.MYI

Script to check tables for corruption in MySQL

USER=kr
PASSWORD=mypass
DBHOST=localhost
LOGFILE=./mysql_check.log
MAILTO=spam@compute.info
TYPE1= # extra params to CHECK_TABLE e.g. FAST
TYPE2=
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 "Logfile: "
date
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
do
DBNAMES=`echo $DBNAMES | sed "s/\b$i\b//g"`
done
fi

# Run through each database and execute our CHECK TABLE command for each table...
for i in $DBNAMES
do
# to fancy up our log file
echo ""
echo "Database: $i"
echo "---------------------------------------------------------"

DBTABLES="`mysql --user=$USER --password=$PASSWORD $i --batch -N -e "show tables"`"

for j in $DBTABLES
do
echo "CHECK TABLE $j $TYPE1 $TYPE2" | mysql -u$USER -p$PASSWORD $i
done
echo ""
done

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

# test our logfile for corruption in the database...
for i in `cat $LOGFILE`
do
if test $i = "warning" ; then
CORRUPT=yes
elif test $i = "error" ; then
CORRUPT=yes
fi
done

# send off our results...
if test $CORRUPT = "yes" ; then
cat $LOGFILE | mail -s "MySQL CHECK Log [ERROR FOUND] for $DBHOST-`date`" $MAILTO
else
cat $LOGFILE | mail -s "MySQL CHECK Log [PASSED OK] for $HOST-`date`" $MAILTO
fi

Comments