2009-07-26

Repair and optimize mysql automatically

There is a command to repair and optimize all databases at once. The optimizing is very important, an optimized db could be 100x faster than an uploaded and not optimized one. I had an experience that a program did not run at all until we optimized the database. Daily optimizing keeps the mysql very fast.
Repair & Optimize all tables by hand: mysqlcheck -Aor -P

Another daily repair/optimize script (found on the internet and used previously):

Enter this to the crontab:
00 6 * * * root /path_to_script/optimize.sh

The content of optimize.sh:

#!/bin/bash
MYSQL_LOGIN='-u mysql_user --password=your_password'
LOCATION=/opt/lampp/bin

for db in $(echo "SHOW DATABASES;" | $LOCATION/mysql $MYSQL_LOGIN | grep -v -e "Database" -e "information_schema")
do
TABLES=$(echo "USE $db; SHOW TABLES;" | $LOCATION/mysql $MYSQL_LOGIN | grep -v Tables_in_)
echo "Switching to database $db"
for table in $TABLES
do
echo -n " * Repairing/Optimizing table $table ... "
echo "USE $db; REPAIR TABLE $table" | $LOCATION/mysql $MYSQL_LOGIN >/dev/null
echo "USE $db; OPTIMIZE TABLE $table" | $LOCATION/mysql $MYSQL_LOGIN >/dev/null
echo "done."
done
done

No comments:

Post a Comment