After writing the article yesterday about removing a slave from a MySQL replication setup. I was digging around the mysql data directory and noticed that the innodb data file hadn’t reduced in size after I dropped the database. There is a reason for this. MySQL will not garbage collect the old data as it’s all one container. You need to change how MySQL handles the storage of database data files and to do that you have to hit the big reset button on your MySQL server.

This is the official way to reclaim space from deleted databases using innodb tables. You can read the full explanation here but I’ll give you a quick run down.

  1. Backup all your existing databases with mysqldump.
  2. Drop all databases except the mysql database
  3. Stop the MySQL server
  4. Change the config file to include the following in the [mysql] section of the config file : innodb_file_per_table
  5. Restart MySQL server
  6. Import all databases again

So yeah, I would be encouraging EVERYONE to add this line to all mysql servers you setup before you load it up with databases. I read that from MySQL 5.6 and onwards this option on by default. With this option enabled each database lives in its own set of files and when the database is dropped the files are garbage collected.