If you use the InnoDB storage engine for (some of) your MySQL tables, you’ve probably already came across a problem with its default configuration. As you may have noticed in your MySQL’s data directory lies a file called ‘innodb1?. It holds almost all the InnoDB data of the MySQL instance and could get quite big. Unfortunately, by design InnoDB data files can’t be shrinked. That’s why DELETEs, TRUNCATEs, DROPs, etc. can’t will not reclaim the space used by the file. Instead any freed regions are marked as unused and can be used later. Theoretically speaking the file could reach the maximum size allowed by the filesystem if no limit is set in the my.cnf file. Guess what ? It’s not set by default. However you can configure your InnoDB engine as described MySQL’s Reference Manual. Additionally you can force the server to create an *.ibd for each newly created InnoDB table by using the ‘innodb_file_per_table‘ option.
As mentioned above, you cannot shrink InnoDB data files. Additionally, you cannot make much changes in the settings of a InnoDB data file. So if you haven’t configured InnoDB properly right after the installation, you’ll probably have a pretty large ibdata1 file. There are three ways to reclaim your free space, but before doing so backup your whole MySQL data directory… just in case. And don’t forget to stop any services using MySQL databases.
Dump the Whole Database
In fact, this is the method I used to solve the problem. It requires much more space and time but it’s maybe the easiest one. So here it is:
- Dump all databases by calling:
/usr/bin/mysqldump –-extended-insert –-all-databases –-add-drop-database –-disable-keys –-flush-privileges –-quick –-routines -triggers > all-databases.sql
- Stop the MySQL server;
- Rename or remove (in case you’ve already backed it up) the MySQL data directory and create an empty one with the same name and permissions;
- Make the appropriated changed in my.cnf;
- From the command prompt issue ‘mysql_install_db‘ in order to re-initialize the database;
- Start the MySQL server;
- Get into the ‘mysql‘ console and type:
SET FOREIGN_KEY_CHECKS=0;
SOURCE all-databases.sql;
SET FOREIGN_KEY_CHECKS=1;
:) Have fun !
