reclaim unused space for mysql

The problem is that mysql will not release the disk space after delete operations to an InnoDB table. That means the disk usage of mysql will not change even you delete half of the data in the database, which will be a large waste for disk usage.

There are two ways to reorganize the physical storage of table data and associated index data to reduce storage space.

1. Use OPTIMIZE TABLE or MYSQLCHECK.

 OPTIMIZE TABLE tb1_name

but optimize table can only be performed for one table, if you want to optimize all the databases, you could either write a script to do it or use mysqlcheck command instead.

MYSQLCHECK -o --all-databases

2.  Resize the InnoDB Tablespace (if not use innodb_file_per_table?)

1. use mysqldump to dump all the InnoDB tables, including InnoDB tables located in MySQL database.
2. stop the server.
3. remove all the existing tablespace files (*.ibd), including the ibdata and ib_log files. Do not forget to remove *.ibd files for tables located in the MySQL database. 
4. remove any .frm files for InnoDB tables.
5. configure a new tablespace.
6. restart the server
7. import the dump files. 

If your databases only use the InnoDB engine, it may be simpler to dump all databases, stop the server, remove all databases and InnoDB log files, restart the server, and import the dump files.

References

1. http://stackoverflow.com/questions/1270944/mysql-innodb-not-releasing-disk-space-after-deleting-data-rows-from-table 
2. http://dev.mysql.com/doc/refman/5.6/en/innodb-data-log-reconfiguration.html