0

Currently I have a MySQL Server with innodb_file_per_table = 0

Based on a number of factors, it seems to make sense for me to change that to innodb_file_per_table = 1

I understand that to make this all work I need to

  1. Use mysqldump to export all databases into individual files
  2. Shutdown mysql
  3. Change the innodb_file_per_table setting
  4. Restart mysql
  5. Import each database

Question is, can I do the following

Day #1

  1. Shutdown mysql
  2. Change the innodb_file_per_table setting
  3. Restart mysql
  4. Export some databases
  5. Import those databases back into mysql

Day #2

  1. Export some databases
  2. Import those databases back into mysql

Until I've exported an re-imported all of the databases?

I'm assuming that during the transition some databases will continue to use the ibdata1 file while the exported and re-imported database will use their own datastores. Will having a mix of datastores cause issues or am I OK doing a phased transition?

3
  • Are the factors you mentioned actually noticeable?
    – danblack
    Dec 17, 2020 at 1:14
  • Neither approach will shrink ibdata1. What is your goal behind doing the change? There are other possible goals. And there are other techniques.
    – Rick James
    Dec 17, 2020 at 1:21
  • @RickJames Maybe I should first be asking if I shoul dbe doing this. Do you know of a good resource online that would help me understand when innodb_file_per_table is the right way to go and when it's not? I'm concerned about the size of my ibdata1 file (35GB), the risk of corruption and backup/restore options for individual databases.
    – Jason
    Dec 17, 2020 at 13:41

2 Answers 2

1

Sorry, I don't have a reference for when to set innodb_file_per_table in different ways. Here are some tips:

  • Note: The value of innodb_file_per_table applies only to subsequent CREATE TABLEs or table rebuilding.

  • [Perhaps the main reason for turning it ON:] When you have a "big" table that might be ALTERed, or you might do a lot of deleting, etc, it is somewhat advisable to build the table with file_per_table ON. This allows the operation to replace the .ibd file containing just that one table.

  • Hence, if the table is being shrunk significantly by the operation, the extra space that is freed up is returned to the OS. That is, the only easy way to return free space to the OS is to have file_per_table on and do some operation that rebuilds the table.

  • file_per_table = OFF is slightly better for "small" tables that will stay small. This because of sharing a single file descriptor (for ibdata1) for many tables.

  • To change file_per_table for a table requires (1) setting the flag and (2) rebuilding the table (eg, simply ALTER TABLE t ENGINE=InnoDB;; it may need FORCE). After that, consider changing the setting back.

  • When running tight on disk space, it might help to play games by moving selected tables between ibdata1 (which may have a lot of free space) and separate .ibd files.

  • Moving a file out of ibdata1 leaves a bunch of free space behind. This space may eventually get used by INSERTs and other operations on the tables in ibdata1.

  • Dumping and integrity are not affected by ibdata1 vs .ibd.

  • Most servers should not worry about the setting.

  • [A related topic:] OPTIMIZE TABLE is essentially never necessary for InnoDB tables.

2

I honestly wouldn't bother unless you have a specific problem you are solving.

Its possible to do simpler with:

  1. Shutdown
  2. change setting innodb_file_per_table = 1
  3. start
  4. ALTER TABLE xxx FORCE for each table
1
  • 1
    Caveat: ibdata1 will not shrink when using ALTER.
    – Rick James
    Dec 17, 2020 at 1:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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