MySQL data file won't shrink

Question

My ibdata1 file for MySQL database grew to about 32GB over time. Recently I deleted about 10GB of data from my databases (and restarted mysql for good measure), but the file won't shrink. Is there any way to reduce the size of this file

Answer 1

The file size of InnoDB tablespaces will never reduce automatically, no matter how much data you delete.

What you could do, although it is a lot of effort, is to create one tablespace for every table by setting

innodb_file_per_table

The long part about this is, that you need to export ALL DATA from the mysql server (setting up a new server would be easier) and then reimport the data. Instead of one single ibdata1 file which holds the data for each and every table, you will find a lot of files called tablename.ibd which hold the data only for one single table.

---

Afterwards:

When you then delete a lot of data from tables, you can let mysql recreate the data-file by issuing

alter table <tablename> engine=myisam;

to switch to MyIsam (and have the InnoDB data file for this table deleted) and then

alter table <tablename> engine=innodb;

to recreate the table.

Answer 2

Unless you set innodb_file_per_table, ibdata1 holds all InnoDB tables plus undo.

This file never shrinks.

To shrink it, you should (at your own risk):

• Backup and drop all InnoDB tables in all databases
• Delete the file manually
• Reinitialize InnoDB storage (by restarting mysqld) and
• Restore the tables from backup.

If you set innodb_file_per_table, you'll still have to do this to reclaim the space, but in this case you'll be able to do this on per-table basis, without affecting the other tables.

Note that the undo is still held in ibdata, even with innodb_file_per_table set.