Home / server / Questions / 509117
Accepted
nulltek
Asked: 2013-05-20 14:34:49 +0800 CST

Innodb one file per tablespace

  • 772

I'm walking into an environment that uses MySQL and Innodb as the storage engine. I'd like to enable innodb_file_per_table to increase performance. Considering all of the databases/tables are currently stored in ibdata1/default ibdata file will the innodb_file_per_table option only affect new tables created after I set this directive?

What would be the best way to take the existing database and split each table into it's own ibd file? Would I have to dump/recover all of the tables to make this happen?

mysql

2 Answers

  1. I agree with @faker's answer's and comments (+1 for his answer), but there is still one major thing to do:

    Before you shutdown mysql, you need to run SET GLOBAL innodb_fast_shutdown = 0;. Why?

    This will completely purge all transactional information left over in ib_logfile0 and ib_logfile1. The shutdown time is longer, but nothing in terms of transactional data that is uncommitted with linger. Thus, there will be no data lost doing this process.

    Here is my post in StackOverflow how to do it : https://stackoverflow.com/a/4056261/491757

    • 2
  2. Best Answer

    http://dev.mysql.com/doc/refman/5.0/en/innodb-multiple-tablespaces.html:

    The --innodb_file_per_table option affects only table creation, not access to 
existing tables. If you start the server with this option, new tables are 
created using .ibd  files, but you can still access tables that exist in the 
shared tablespace. If you start the server without this option, new tables are 
created in the shared tablespace, but you can still access any tables that were
created using multiple tablespaces.

    And yes, a dump and restore is required to move existing tables into their own files.

    • 1