MySQL: Optimize all tables; Run using cron
I was looking for a optimize table script and finally stumbled upon some code which could be salvaged into a shell script. The full code is reproduced here for future reference.
#-- I usually add the root username and password to the ~/.my.cnf
$MYSQL_CMD -e "SELECT concat('OPTIMIZE TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,';') as cmd FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND Data_free > 10 AND NOT ENGINE='MEMORY' INTO OUTFILE '/tmp/cmd.sql'"
$MYSQL_CMD < /tmp/cmd.sql
rm -f /tmp/cmd.sql