Tell me more ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.
up vote 1 down vote favorite

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

share|improve this question

2 Answers

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
2  
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
show 1 more comment
up vote 0 down vote

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

 
discard

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.