mysql optimize database from shell script
maggio 3rd, 2010
If you have many databases with many operations: insert, delete, update ecc… it may be takes a good amount of space. Infact MySQL doesn’t free the space of deleted entries.
With “optimizing” the tables, you can free that space.
Whit this script yuo can optimizing ALL database on yuor server.
#!/bin/sh
# this shell script finds all the tables for a database and run a OPTIMIZE command against it
# @usage "mysql_perform.sh --optimize
# @date 03/05/2010
# @version 1.0 - 03/05/2010 - first release
# @author Mattia Moscheni
# @site http://www.ferzip.com
AUTH=$3
printUsage() {
echo "Usage: $0"
echo " --optimize --credentials '-uUSERNAME -pPASSWORD' "
return
}
doAllTables(){
for db in $(echo "SHOW DATABASES;" | mysql $AUTH | grep -v -e "Database" -e "information_schema")
do
TABLES=$(echo "USE $db; SHOW TABLES;" | mysql $AUTH | grep -v Tables_in_)
echo "Switching to database $db"
for table in $TABLES
do
echo -n " * Optimizing table $table ... "
echo "USE $db; OPTIMIZE TABLE $table" | mysql $AUTH >/dev/null
echo "done."
done
done
}
if [ $# -lt 3 ] ; then
printUsage
exit 1
fi
case $1 in
--optimize) doAllTables;;
--help) printUsage; exit 1;;
*) printUsage; exit 1;;
esacremember to assing execute attribute chmod +x or chmod 700 Is a good idea to use whit NICE command An example of usage is:
#./mysql_perform.sh --optimize --credentials '-uUSERNAME -pPASSWORD'
or
#nice -n 20 mysql_perform.sh --optimize --credentials '-uUSERNAME -pPASSWORD'
.
Channel on Metacafe