Shell script to optimize all tables in all databases (MySQL)
If you have many databases with many write operations, it may be that the table overhead takes a good amount of space. (This is due to the fact that MySQL doesn't free the space of deleted entries.) With "optimizing" the tables, you can free that space.
Save the following script as /usr/local/sbin/optimizealltables.sh, make it chmod 700 (very important!), then replace YOURPASSWORD with your root MySQL password.
CODE:
-
#!/bin/bash
-
-
MYSQL_LOGIN='-u root --password=YOURPASSWORD'
-
-
for db in $(echo "SHOW DATABASES;" | mysql $MYSQL_LOGIN | grep -v -e "Database" -e "information_schema")
-
do
-
TABLES=$(echo "USE $db; SHOW TABLES;" | mysql $MYSQL_LOGIN | 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 $MYSQL_LOGIN >/dev/null
-
echo "done."
-
done
-
done
