innodb_file_per_table for existing MySQL database

May 22, 2013

So you want to toggle innodb_file_per_table on for your existing MySQL database? This is a step by step guide explaining how to do just that.

Should I really enable innodb_file_per_table?

Some people prefer it on:

Some people prefer it off:

http://umangg.blogspot.se/2010/02/innodbfilepertable.html

Before we get started...

You may want to convert most of your MyISAM tables to InnoDB tables before you continue.

The step by step guide

Step 1: Go home

cd

Step 2: Check if the server is running (it should be)

ps -ef | grep 'mysqld'

Step 3: Stop server

service mysql stop

Step 4: Check if the server is running (it should not be)

ps -ef | grep 'mysqld'

Step 5: Backup the mysql files

cp -ra /var/lib/mysql /var/lib/mysqlbackupps -ef | grep 'mysqld'

Step 6: Start server

service mysql start

Step 7: Start server

mysqldump -uroot -pYOURPASS --routines --flush-privileges --all-databases > all-databases.sql

Step 8: Generate SQL to drop all databases but mysql and information_schema

Use SHOW databases; and create a list commands like:

DROP DATABASE derp;
DROP DATABASE herp;
DROP DATABASE test;
DROP DATABASE performance_schema;

NOTE: The database mysql should not be tampered with and isn't using InnoDB anyways.
NOTE: The database information_schema can't be removed or altered. It's not "real".
NOTE: The database performance_schema can be removed without danger as long as we recreate it again, which we do at the last step.

Step 9: Run that SQL

...

Step 10: Ensure no innodb tables present

SELECT table_name, table_schema, engine FROM information_schema.tables WHERE engine = 'InnoDB';

Should return no rows.

Explanation: Make sure you don't leave any InnoDB tables removing ib* files. Leaving any InnoDB objects would lead to corruption.

Step 11: Stop server

service mysql stop

Step 12: Delete InnoDB files

rm /var/lib/mysql/ibdata1
rm /var/lib/mysql/ib_logfile0
rm /var/lib/mysql/ib_logfile1

NOTE: At this point, there should only be the mysql schema in /var/lib/mysql

Step 13: Set these options in my.cnf

innodb_file_per_table = 1
innodb_file_format = barracuda

Step 14: Start server

service mysql start

Step 15: Load SQL

mysql -uroot -pYOURPASS < all-databases.sql

Step 16: Recreate performance_schema

mysql_upgrade -uroot -pYOURPASS --force

Step 17: Wait a couple of days