Getting rid of huge ibdata file, no dump required

May 22, 2012

You have been told (guilty as charged), that the only way to get rid of the huge InnoDB tablespace file (commonly named ibdata1), when moving to innodb_file_per_table, is to do a logical dump of your data, completely erase everything, then import the dump.

To quickly reiterate, you can only delete the ibdata1 file when no InnoDB tables exist. Delete this file with an existing InnoDB table, even a table in its own tablespace, and nothing ever works anymore.

The problem with the dump-based solution

The impact of doing a logical dump is often overwhelming. Well, the dump may be tolerable, but the restore is much longer. The real pain is that you can't do this one table at a time: you have to destroy everything before dropping the ibdata1 file; you then have to import everything.

Perhaps the most common scenario is that we do the changes on a slave, so as not to completely shut down our database. This is nice; no one is aware of the shutdown process. However, Huston, we have a problem: we need to make sure we can keep up the binary logs on the master for the duration of the entire process.

A semi-solution for binary logs

You may get by by keeping the SQL_IO_THREAD running on the slave while dump is taken (SQL thread is better turned off). If you're careful, you could do the same after restarting the database: you should still be able to acquire relay logs. With row based replication becoming more common, the problem of binary logs disk space returns: the logs (rather, log entries) are just so much larger!

Either way, the process can takes long days, at the end of which your slave is up, but lags for long days behind.

Wishful thought: do it one table at a time

If we could do it one table at a time, and assuming our dataset is fairly split among several tables (i.e. not all of our 500GB of data is in one huge table), life would be easier: we could work on a single table, resume replication, let the slave catch up, then do the same for the next table.

How? Didn't we just say one can only drop the ibdata1 file when no InnoDB tables exist?

Solution: do it one table at a time

I'm going to illustrate what seems like a longer procedure. I will later show why it is not, in fact, longer.

The idea is to first convert all your tables to MyISAM (Yay! A use for MyISAM!). That is, convert your tables one table at a time, using normal ALTER TABLE t ENGINE=MyISAM.

Please let go of the foreign keys issue right now. I will address it later, there's a lot to be addressed.

So, on a slave:

  3. START SLAVE again
  4. Wait for slave catch up
  5. GOTO 1

What do we end up with? A MyISAM only database. What do we do with it? Why, convert it back to InnoDB, of course!

But, before that, we:

  1. Shut MySQL down
  2. Delete ibdata1 file, ib_logfile[01] (i.e. delete all InnoDB files)
  3. Start MySQL

A new ibdata1 file, and new transaction log files will be created. Note: the new ibdata1 file is small. Mission almost accomplished.

We then:

  3. START SLAVE again
  4. Wait for slave catch up
  5. GOTO 1

What do we end up with? An InnoDB only database, with true file per table, and a small ibdata1 file. Space recovered!

The advantage of this method

The thing is, we resume replication after each table alteration. This means breaking the lag period into many smaller periods. While the total runtime does not reduce, we do reduce the maximum lag time. And this makes for easier recovery: no need to store multitudes of binary logs!

So what about the foreign keys?

Phew. Continued next post.

  • Holger Thiel


    This is a tricky and dangerous idea. The replication is in danger to break as the statement based replication is in need.

    Normally another slave on the same machine can be installed and used to be built up.


  • Holger,
    I've seen some cases where it would simply take too much time (two weeks or more) to dump and restore, and disk space would not be available to accommodate all binary logs.
    Setting up a chain of slaves is another option, with longer pipes to accommodate all binary logs.

  • The bigger issue is that you lose all the transactional features of InnoDB while this process is taking place and your application is none the wiser. Not to mention the differing performance characteristics of the two storage engines.

    While this can certainly be useful in some scenarios, you need to think very carefully as to whether your application can tolerate this kind of change.

  • @Michael,
    Incorrect. The entire process runs on a slave; you loose none of InnoDB's transactional features (except durability in case of crash in the middle of the process - but then you can start again).

    The slave is single threaded (up to 5.5) and couldn't care less about transactions on master. A failed transaction never reaches it. A committed transaction is just another command to execute.

    I think you may be confusing with running this process on the master, which is entirely not something discussed here.

Powered by Wordpress and MySQL. Theme by