How can I write a routine to check, optimize and analyze all tables in databases in MySQL
I prefer writing it myself. Place these lines in /usr/local/sbin/OptimizeAllTables.sh

WHICH=/usr/bin/which
MYSQL=`${WHICH} mysql`
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT CONCAT('OPTIMIZE LOCAL TABLE ',db,'.',tb,';')"
SQL="${SQL} FROM (SELECT table_schema db,table_name tb,engine eng"
SQL="${SQL} FROM information_schema.tables WHERE"
SQL="${SQL} engine in ('InnoDB','MyISAM') AND table_schema NOT IN"
SQL="${SQL} ('information_schema','performance_schema','mysql')"
SQL="${SQL} ORDER BY data_length DESC) A"
${MYSQL} ${MYSQL_CONN} -ANe"${SQL}" &gt; /tmp/OptimizeAllTables.sql
${MYSQL} ${MYSQL_CONN} &lt; /tmp/OptimizeAllTables.sql


and make it executeable

chmod +x /usr/local/sbin/OptimizeAllTables.sh


CAVEAT #1

Running OPTIMIZE TABLE is the same as running ALTER TABLE ... ENGINE=... followed by ANALYZE TABLE. Therefore, there is no need to run a separate ANALYZE TABLE.

CAVEAT #2

Please note I use OPTIMIZE LOCAL TABLE instead of OPTIMIZE TABLE. This ensures that the OPTIMIZE TABLE is not recorded in the binary logs because it can replicate to Slaves. If you want the OPTIMIZE TABLE to be replicated all Slaves, OPTIMIZE TABLE is fine.

CAVEAT #3

Note that I ordered all the tables by the biggest table first. If you want to optimize all tables starting with the smallest table, change this line

SQL="${SQL} ORDER BY data_length DESC) A"


to

SQL="${SQL} ORDER BY data_length) A"


EPILOGUE

Simply place this line in the crontab to run every midnight

0 0 * * * /usr/local/sbin/OptimizeAllTables.sh


If the tables are enormous, you may want to schedule this for one a week (Sunday at Midnight)

0 0 * * 0 /usr/local/sbin/OptimizeAllTables.sh


Give it a Try !!!