Shell-script: MySQL optimize all tables

Using myisamchk sometime requires shutting down MySQL server and that’s not very easy in certain situation. This shell script accepts a database name from input and gets all table names and runs a SQL query against these tables. Currently, it only optimizes all the tables and could be expanded to do a lot more. OPTIMIZE does a lot more, an all-in-one solution (for MyISAM tables: repair, sort index, update stats). Enjoy!

<br /> #!/bin/sh</p> <p># this shell script finds all the tables for a database and run a command against it<br /> # @date 6/14/2006<br /> # @author Son Nguyen</p> <p>DBNAME=$2</p> <p>printUsage() {<br /> echo “Usage: $0″<br /> echo ” –optimize <tablename>”<br /> return<br /> }</p> <p>doAllTables() {<br /> # get the table names<br /> TABLENAMES=`mysql -D $DBNAME -e “SHOW TABLES\G;”|grep ‘Tables_in_’|sed -n ‘s/.*Tables_in_.*: \([_0-9A-Za-z]*\).*/\1/p’`</p> <p> # loop through the tables and optimize them<br /> for TABLENAME in $TABLENAMES<br /> do<br /> mysql -D $DBNAME -e “OPTIMIZE TABLE $TABLENAME;”<br /> done<br /> }</p> <p>if [ $# -eq 0 ] ; then<br /> printUsage<br /> exit 1<br /> fi</p> <p>case $1 in<br /> –optimize) doAllTables;;<br /> –help) printUsage; exit 1;;<br /> *) printUsage; exit 1;;<br /> esac</p> <p>

Sample Usage:
<br /> root@s# ./mysql_optimize.sh –optimize abc<br /> +——————+———-+———-+———-+<br /> | Table | Op | Msg_type | Msg_text |<br /> +——————+———-+———-+———-+<br /> | abc.def | optimize | status | OK |<br /> +——————+———-+———-+———-+<br />