innodb_file_per_table for existing MySQL database

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:

Other people have already written articles on the subject. I would suggest reading those as well to get other points of view:

After reading these articles you will have a good feel for what has to be done but you won't know the exact commands. With this guide I hope to help you more in detail.

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


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 performance_schema;

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


Explanation: This should always be done before erasing backups.

Step 18: Remove backups

rm all-databases.sql
rm -r /var/lib/mysqlbackup