How to Reclaim Disk Space After Deleting Lots of MySQL Records

Turns out MySQL’s InnoDB engine isn’t (by default) very enthusiastic to give back disk space if after mass deletions (say, a DELETE * or a TRUNCATE TABLE).

The fix is a little unsettling, but it’s also simple (and with a modestly size db – fast).

  1. Back up the db (you should doing this anyway, but now is a nice time to do it again.)
    mysqldump -uroot -p --opt --skip-lock-tables -f --quote-names --databases yourDB > yourDB.sql
  2. Confirm you made a backup. Then drop the databases you just backed up (I told you there was an un-nerving part).
  3. Shutdown the MySQL server – whatever your preferred method is. I like to click ‘stop’ in Webmin, maybe you like the command line.
  4. Remove all innodb files (it’s pretty obvious which ones they are)
  5. Restart the MySQL server
  6. Reimport your database
    mysql -uroot -p < yourDB.sql

While you’re here – hop over to my.cnf and add innodb_file_per_table so next time OPTIMIZE TABLE will actually do what you expect it to do.