2

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

Ian
  • 335
  • 1
  • 3
  • 19

2 Answers2

3

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.

EEAA
  • 109,363
  • 18
  • 175
  • 245
  • So, if I turned this on, then restarted mysql, I would not see any effect until I optimize? – Ian May 02 '11 at 02:28
  • That is correct. – EEAA May 02 '11 at 02: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 02 '11 at 02:30
  • 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 02 '11 at 04:29
  • I just optimized all my tables with the new setting, however if/when i try to delete the old ibdata1 file, all my tables end up with 0 rows... How can I get rid of the old file? (short of doing a dump/import) – Ian May 02 '11 at 22:22
1

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.

Community
  • 1
RolandoMySQLDBA
  • 16,544
  • 3
  • 48
  • 84