Here is a script i use to optimize MySQL Database. This is a small bash script that you can use if you want to optimize or repair the whole database. Feel free to give comments if the script works or not.

#!/bin/sh

# @usage “mysql_tables.sh –optimize MyDatabaseABC”

DBNAME=$2

printUsage() {

echo “Usage: $0″

echo ” –optimize ”

echo ” –repair ”

return

}

doAllTables() {

# get the table names, you need to provide the root user’s password

TABLENAMES=`mysql -uroot -ptest123 -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 -uroot -ptest123 -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

But if you want to do the hard way, here is a one line mysql script that you can use to optimize your database.

mysql -uroot -ptest123 -e"show tables" database |grep -v "Tables_in_database" |xargs -i mysql -ptest123 -e"optimize table {}" database