By default MySQL stores InnoDB tables and indexes in a single file called "ibdata1" (which is on Debian in the
directory). Unfortunately this file is never "recycled", thus once space is allocated to it, it's never released (see this feature request
). Not even after dropping the database that held the given table. However there's an InnoDB option called
that allows you to store each InnoDB table in a separate file (just as MyISAM does) and after dropping the table the file is removed and thus the occupied space is recycled.
So the solution is to use this option. But it affects only newly created tables, there's no automatic table conversion involved. However executing an
ALTER TABLE ... ENGINE=InnoDB
on the given table acts just as if you did drop the table and loaded it back from a backup. You can use the following query to create the
statements for all of your InnoDB tables:
SELECT CONCAT('ALTER TABLE `', t.`TABLE_SCHEMA`, '`.`', t.`TABLE_NAME`, '` ENGINE=', t.`ENGINE`, ';') AS stmt
FROM `information_schema`.`TABLES` t
WHERE t.`ENGINE` = 'InnoDB'
ORDER BY t.`TABLE_SCHEMA`, t.`TABLE_NAME`
One thing to note is that the above procedure will not decrease the size of your current
file. To achieve that you've only one option. Instead of executing "alter table" commands, you've to dump/backup _all_ your InnoDB tables, drop all these tables, add the extra innodb option to my.cnf and import the innodb tables back into the database. A more complete checklist:
- Backup/dump all InnoDB tables.
- Drop all InnoDB tables.
- Stop MySQL.
- Add the
innodb_file_per_table option to
my.cnf (to the
- Delete the
ibdata1 file and any
ib_logfile* files in the MySQL database directory.
- Start MySQL.
- Import tables from the backup that you created in the first step.