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:

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

Other Articles

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
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/mysqlbackup

Step 6: Start server
service mysql start

Step 7: Dump SQL
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

Explanation: This should always be done before erasing backups.

Step 18: Remove backups
cd && rm all-databases.sql && rm -r /var/lib/mysqlbackup

By | 2013-05-22T13:48:55+00:00 May 22nd, 2013|Uncategorized|1 Comment

One Comment

  1. Wegorz 2015-02-10 at 02:02 - Reply

    Thanks, I can do any operations on production only at late night and Your post made everything so simple. Only thing i had to add was executing SET GLOBAL log_bin_trust_function_creators = 1; and add –events parameter to mysqdump in 5.5. version.

    Regards

Leave A Comment