My Blog

Recently I did run into issue where I just needed to optimize my database. Have few of them in mysql and then each of those databases have all bunch of tables. Now, I can issue:
mysql -u root -ppassword -e ‘show databases’
this will display list of all my databases, then I can connect to each individual database and issue the following sql statement:
OPTIMIZE TABLE tbl_name;
But this is quite manual process. Easier way is to just use mysqlcheck utility on the whole database rather than each individual tables.
mysqlcheck -o dspam -u root -ppassword
The mysqlcheck program can also be used to check (-c/-m/-C), repair (-r) and analyze (-a) the tables in a database. These commands can be done by using the CHECK, REPAIR and ANALYZE MySQL commands.

So anyway I did put this into some script and can easily execute it on every database that I have in my mysql
-=-=-=- END OF SCRIPT -=-=-=-
#!/usr/bin/ksh
STAMP=`date +%y%m%d.%H%M%S`
HOSTNAME=`/usr/bin/uname -n`
REMOTEHOSTNAME=”localhost”

mysqlpwd=’password’             ###This is why the file should be chmod 700
                                ###for the mysql user we are backing up.

for database in `/usr/local/mysql/bin/mysql -h $REMOTEHOSTNAME -u root -p$mysqlpwd -e ‘show databases’ | sed s/[\+\|]//g`
do
  if [ $database == Database ] || [ $database == test ]
  then
    echo $database
    continue
  else
    now=`date`
    echo “==========================================”
    echo “Optimizing $database on $REMOTEHOSTNAME:”
    echo “Start: $now”
    /usr/local/bin/mysqlcheck -h $REMOTEHOSTNAME -o $database -u root -p$mysqlpwd
    if [ $? -eq 0 ]
    then
      now=`date`
      echo “End:   $now”
      echo “Optimization of $database Successful.”
    else
      echo “!!! There was an error optimizing up MySQL $database on $REMOTEHOSTNAME”
    fi
    echo “”
    echo “==========================================”
    echo “”
    echo “”
fi     
done
-=-=-=- END OF SCRIPT -=-=-=-