“Lazy” suits more to a Linux System Engineer. 🙂 …..

Introducing scripting with a touch of HTML formatting.

The below script will grep all DB’s and will,

  • Check for the corrupted tables
  • Repair it
  • Optimize it
  • If unable to do so then will email with a DB name, Table name and an Engine information.

Script

for db in $(mysql -e “SHOW DATABASES;” | grep -ve Database -ve information)
do

ISAM=$(mysql -e “SELECT TABLE_NAME FROM information_schema.TABLES where TABLE_SCHEMA = ‘”$db”‘;”| grep -v TABLE_)
for isam in $ISAM
do
mysqlcheck $db $isam > /root/check.txt
tbls=$(cat /root/check.txt | grep -ie Corrupt -ie dump\/reload)
if [ ! -z “$tbls” ];
then
echo “<h3>Database: $db</h3>” >> /root/result.log
echo “<i>Table Name: $isam<i>” >> /root/result.log
echo “<br>” >> /root/result.log
echo “<br>” >> /root/result.log
echo “<b><font color =”GREEN”>Repairing the table</font></b>” >> /root/result.log
echo “USE $db; REPAIR TABLE $isam” | mysql -H > /root/repair.txt
echo “<br>” >> /root/result.log
echo “<br>” >> /root/result.log
echo “<PRE>`cat /root/repair.txt`</PRE>” >> /root/result.log
echo “<br>” >> /root/result.log
echo “<b><font color =”GREEN”>Optimizing the table</font></b>” >> /root/result.log
echo “<br>” >> /root/result.log
echo “<br>” >> /root/result.log
echo “USE $db; OPTIMIZE TABLE $isam” | mysql -H > /root/optimize.txt
echo “<PRE>`cat /root/optimize.txt`</PRE>” >> /root/result.log
echo “<br>” >> /root/result.log
echo “<b>done</b>” >> /root/result.log
echo “<br>” >> /root/result.log
echo “<br>” >> /root/result.log
echo “<b>Checks after REPAIR AND OPTIMIZE</b>” >> /root/result.log
echo “<br>” >> /root/result.log
mysqlcheck $db $isam > /root/check.txt

tbls=$(grep -i Corrupt /root/check.txt)

if [ ! -z “$tbls” ];
then
echo “<br>” >> /root/result.log
echo “<b><font color =”RED”>Can’t be Repaired or Optimized</font></b>” >> /root/result.log
echo “<br>” >> /root/result.log
echo “<br>” >> /root/result.log
echo “<b>Table Engine Information</b>” >> /root/result.log
echo “<br>” >> /root/result.log
echo “<br>” >> /root/result.log
mysql -H -e “SELECT TABLE_NAME,ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = ‘”$db”‘ AND TABLE_NAME = ‘”$isam”‘;” > /root/engine.txt
echo “<PRE>`cat /root/engine.txt`</PRE>” >> /root/result.log
echo “<HR ALIGN=”LEFT” SIZE=”3″ WIDTH=”70%” NOSHADE>” >> /root/result.log
else
echo “<br>” >> /root/result.log
echo “<b><font color =”GREEN”>Status OK for $isam</font></b>” >> /root/result.log
echo “<HR ALIGN=”LEFT” SIZE=”3″ WIDTH=”70%” NOSHADE>” >> /root/result.log
fi

fi

done
done
cat result.log | mail -s “$(echo -e “118 Status check for MYISAM Tables\nContent-Type: text/html”)” your-email-address@ — -f MYISAM@google.com
rm -f /root/result.log

Output:

MYISAM check output