up vote 0 down vote favorite
share [g+] share [fb]

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

link|improve this question
60% accept rate
feedback

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.

link|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. – ErikA 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. – ErikA 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
feedback
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.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

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