Mysql - Optimize all tables in all databases

To reduce used space by Mysql InnoDb tables (we use file per table setting), you need to optimize table.

Otherwise the InnoDB data file just grows. Even if you delete some rows from table, free space for deleted rows is correctly reused later by new rows, but data file on disk will never reduce the size alone.

Replication:

In our case we optimize tables just in case the server is working as slave in maintenance mode (no traffic, just replication). Important is to disable replication of optimize command to other servers (e.g. in Master-Master replication mode)

To optimize all tables on your server in all databases you can use e.g. commandline command:

mysqlcheck --all-databases --optimize --skip-write-binlog 

To optimize in all databases on server just tables, where there is minimum 10MB of overhead and it is at least 10% of table size, we created simple bash script:

#!/bin/bash

mysql -e "SHOW DATABASES" | while read database therest; do
        mysql -D$database -e "SHOW TABLE STATUS WHERE Data_free>10000000 AND Data_free/Data_length > 0.1" | while read tblname therest; do
                mysqlcheck --optimize --skip-write-binlog $database $tblname
        done
done