Mysql database optimize and repair bash script

By augusto ~ Posted Dom, 26/08/2007 - 13:46

Taken from the

here a script for your shell very usefull to optimize and repair your mysql databases. It work for every table of the given database.

Since the original was missing the authentication options, I've added a few lines to pass to the script username and password

#!/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

#AUTH='-uweb -pwebphp'
AUTH='-uadmin -pqLtwUCRKL8'

AUTH=$3
DBNAME=$4

printUsage() {
echo "Usage: $0"
echo " --optimize --credentials '-uUSERNAME -pPASSWORD' "
echo " --repair "
return
}

doAllTables() {
# get the table names
TABLENAMES=`mysql $AUTH -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 $AUTH -D $DBNAME -e "$DBCMD TABLE $TABLENAME;"
done
}

if [ $# -lt 3 ] ; then
printUsage
exit 1
fi

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