As you may have noticed in your MySQL’s data directory (in Debian/Ubuntu – /var/lib/mysql) lies a file called ‘ibdata1′. It holds almost all the InnoDB data (it’s not a transaction log) of the MySQL instance and could get quite big. By default this file has a initial size of 10Mb and it automatically extends. Unfortunately, by design InnoDB data files cannot 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 (in Debian/Ubuntu it’s located in /etc/mysql/my.cnf). Guess what ? It’s not set by default.
This method 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 appropriate changes in my.cnf;
- Re-initialize the database with the following command (replace the ‘mysqld‘ with the login of the user your MySQL server runs as):
sudo -u mysqld mysql_install_db
- 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; - Restart the MySQL server.