Optimize tables that need optimization

This script looks for tables that needs optimization. The logic is to check 'Data_free' field of SHOW TABLE STATUS command , if it is not zero, this means table has unused bytes i.e. the table has got holes somewhere in the table. You can customize this script according to your need, that is, optimize found tables etc.

#!/bin/sh

# This script check tables in the database.

# Additionaly you can supply a filter to check only certain tables.

user=root

pass=xxxxx

db=test

#filter=

TMP=~/scripts/query.log

LOG=~/scripts/need_optimization.log

MAXBYTES=100

mysql="/usr/bin/mysql -u$user -p$pass -s $db"

# Ok now do the check on each table

echo "Checking database $db ..."

echo "show tables like \"${filter}\"" | $mysql | while read table

do

echo "SHOW TABLE STATUS LIKE \"${table}\"\G" | $mysql > $TMP

bytes=`grep Data_free $TMP | cut -f2 -d: | tr -d " "`

if [ $bytes -gt $MAXBYTES ]; then

echo "--------------------- Table Status ($table) `date`" >> $LOG

cat $TMP >> $LOG

echo "---------------------------------------------------------------------------" >> $LOG

fi

done

echo "Checking database $db finished..."

##############################

let's run this script

shell# ./db_optimize.sh

check what table(s) need optimization...

shell# more need_optimization.log