Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

If I turn on innodb_file_per_table (not currently active), will it destroy my existing tables?

share|improve this question
up vote 2 down vote accepted

No, but you will need to do a sql dump and re-load or alternatively an optimize table before existing tables will be moved into their own files.

Of course as with any large architectural changes, make sure to nab a good backup before implementing the changes.

share|improve this answer
So, if I turned this on, then restarted mysql, I would not see any effect until I optimize? – Ian May 2 '11 at 2:28
That is correct. – EEAA May 2 '11 at 2:29
Well, I should clarify, after a restart, any new tables will be implemented as file_per_table, but existing data will remain where it was previously. – EEAA May 2 '11 at 2:30
Perfect, thanks. – Ian May 2 '11 at 3:42
Bonus note: once you reload into f-p-t mode, you'll have to manually clear ibdata1, as there's no way to shrink it. Joy. Needless to say, f-p-t is on by default in all my installations. – BMDan May 2 '11 at 4:29

Even when you switch to innodb_file_per_table, the InnoDB tables that are inside ibdata1 must be extracted from ibdata1. Unfortunately, you cannot reclaim that space back.

You must overhaul the InnoDB infrastructure in order for innodb_file_per_table to work for you. @BMDan already mentioned this in his comment.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.