Shrinking ibdata files after innodb_file_per_table

Patrick Galbraith wrote in his blog about switching to innodb_file_per_table.  For those that don’t know about this setting, it places the data for the tables into .ibd files within the database dir instead of storing it in the ibdata files in the main datadir.  This is useful if you don’t want to babysit your innodb tablespace.  At least, that was my main reason for wanting to use it.  There is still dictionary data stored in the ibdata file(s) so you can’t just remove them.

Anyhow, the delima Patrick wrote about is recovering the space used by the ibdata files in the datadir after you have converted the tables to one file per table.  I commented on his blog, but thought it worth a full post to be sure others could find my solution.

  1. Backup your data (cuz, you never know)
  2. Convert all tables to MyISAM
  3. Stop MySQL.
  4. Delete ib* in the datadir
  5. Restart MySQL.  MySQL will recreate the files.
  6. Convert all tables to InnoDB

It worked for me.  Your mileage may vary.  No warranty that you won’t lose all your data.  Try it on a dev server first.

The benefits of this were that the data was online while we converted the tables.  The only downtime was while we shut down MySQL, removed the files and waited on MySQL to recreate a small 10MB ibdata file and the ib_logfiles.  I am not 100% sure you have to remove the ib_logfiles, but I did for good measure.  I just run with a single 10MB autoextend ibdata file.  I think it is at 34MB or so on our main database server now.

About these ads

3 Responses to Shrinking ibdata files after innodb_file_per_table

  1. tpol says:

    Couldn’t you also backup the database, remove the files, then restore the database. Any thoughts on which would be the “better” solution?

  2. Brian Moon says:

    Yes, that is the solution that Patrick was considering. However, that means down time while the database imports.

  3. Per Cederberg says:

    This method fails if any of the InnoDB tables uses foreign keys. From the MySQL 5.0 manual (13.2.6.4):

    Also, if an InnoDB table has foreign key constraints, ALTER TABLE cannot be used to change the table to use another storage engine. To alter the storage engine, you must drop any foreign key constraints first.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: