2

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?

nulltek
  • 1,251
  • 3
  • 14
  • 22

2 Answers2

2

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

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

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.

faker
  • 17,496
  • 2
  • 60
  • 70
  • Thanks for the reference. What would be the recommended procedure to dump/restore as individual .ibd files? Should I dump the entire database, set the innodb_file_per_table, restart mysqld, then restore the database? Or would I have to dump/restore tables individually? Thanks again for your help. – nulltek May 19 '13 at 23:02
  • Dump all Databases, drop all Databases (except "mysql"), stop MySQL, adjust my.cnf, remove ibdata1/ib_logfile* files (careful! This assumes you want to move *all* InnoDB DBs to file_per_table style!), start MySQL, import the dump – faker May 19 '13 at 23:06