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! #!/bin/sh # this shell script finds all the tables for a database and run a command against it # @date 6/14/2006 # @author Son Nguyen DBNAME=$2 printUsage() { echo "Usage: $0" echo " --optimize <tablename>" return } doAllTables() { # get the table names TABLENAMES=`mysql -D $DBNAME -e "SHOW TABLES\G;"|grep 'Tables_in_'|sed -n 's/.*Tables_in_.*: \([_0-9A-Za-z]*\).*/\1/p'` # loop through the tables and optimize them for TABLENAME in $TABLENAMES do mysql -D $DBNAME -e "OPTIMIZE TABLE $TABLENAME;" done } if [ $# -eq 0 ] ; then printUsage exit 1 fi case $1 in --optimize) doAllTables;; --help) printUsage; exit 1;; *) printUsage; exit 1;; esac Sample Usage: root@s# ./mysql_optimize.sh --optimize abc +------------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------------+----------+----------+----------+ | abc.def | optimize | status | OK | +------------------+----------+----------+----------+