With increasing data, mysql tables can get defragmented and it is a good idea to optimize tables every once in a while.
Getting the list of all tables that need optimization :
>
mysql -p -e “select concat(TABLE_SCHEMA,’.’,TABLE_NAME),Data_free from information_schema.TABLES where TABLE_SCHEMA NOT IN (‘information_schema’,’mysql’) and Data_free>0;” >> fragdb.txt
To Generate the output to include the command to optimize table, so that you can process with shell script use the following command :
>
mysql -p -e “select concat(‘optimize table  ‘, TABLE_SCHEMA, ‘.’, TABLE_NAME, ‘;’) > from information_schema.TABLES > where TABLE_SCHEMA NOT IN (‘information_schema’,’mysql’) > and Data_free > 0;” >> dbfrag.txt
In replication environment , if you donot want to replicate optimization, which you dont want in most cases, use the following syntax :
>
OPTIMIZE  or TABLE