June 11th, 2008 at 11:03 am

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.

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

Feed for this post Categories: Code Tags: , ,

5 Comments

  1. Bradley Coudriet · November 25th, 2008 at 9:47 pm #

    Great little snippet. This helped me out a lot.

  2. Logan · May 14th, 2009 at 10:03 pm #

    This is great, thanks!

  3. Geoff · May 19th, 2009 at 4:35 pm #

    Thanks, just came across this today and exactly what I was looking for :)

  4. Linux _User · June 2nd, 2010 at 4:30 pm #

    thank’s a lot !!!!!

  5. Vasu · September 15th, 2011 at 12:53 pm #

    +1. Copy pasted the code, changed credentials and it ran without a hitch. Thanks for the super awesome script

Leave a Comment




«
»