Shell Script to Optimize all tables for a database

This shell script finds all the tables for a database and run a command against it.

See File optimize-tables

The file’s contents are below as well:

#!/bin/sh

# this shell script finds all the tables for a database and run a command against it
# @usage "mysql_tables.sh --optimize MyDatabaseABC"
# @date 6/14/2006
# @version 1.1 - 1/28/2007 - add repair
# @version 1.0 - 6/14/2006 - first release
# @author Son Nguyen

DBNAME=$2

printUsage() {
  echo "Usage: $0"
  echo " --optimize <dbname>"
  echo " --repair <dbname>"
  return
}


doAllTables() {
  # get the table names
  TABLENAMES=`mysql -D $DBNAME -e "SHOW TABLES\G;"|grep 'Tables_in_'|sed -n 's/.*Tables_in_.*: \([_0-9A-Za-z]*\).*/\1/p'`

  # loop through the tables and optimize them
  for TABLENAME in $TABLENAMES
  do
    mysql -D $DBNAME -e "$DBCMD TABLE $TABLENAME;"
  done
}

if [ $# -eq 0 ] ; then
  printUsage
  exit 1
fi

case $1 in
  --optimize) DBCMD=OPTIMIZE; doAllTables;;
  --repair) DBCMD=REPAIR; doAllTables;;
  --help) printUsage; exit 1;;
  *) printUsage; exit 1;;
esac