InnoDB and the innodb file per table option

Starting with MySQL 5.5, InnoDB is the default storage engine for MySQL. In earlier versions, you can enable or disable the InnoDB storage engine as you desire. If you are running a default installation of MySQL 5.5 or have explicitly enabled InnoDB, there are some recommended practices you should be aware of before using your system in a production environment.

Within this article, we will be specifically talking about the innodb_file_per_table option. For a full list of recommended best practices, please see the MySQL Reference Manual Best Practices for InnoDB Tables

By default, InnoDB stores all table data and indexes in the shared tablespace in the file ibdata1 usually located here:

/var/lib/mysql/ibdata1

Since all MySQL data is stored within this file, a single corrupt table can create problems for your entire MySQL service. When corruption occurs within an InnoDB table, it is often necessary to drop the table and restore it from a current copy of your backups. As always, the key to running a reliable database service is to run frequent backups of your databases. However, if your InnoDB engine is storing the data and indexes for all tables within ibdata1, recovering from a crash can mean restoring ALL of your database backups, a time-consuming and tedious process.

To address this issue, you should enable the innodb_file_per_table option in your my.cnf file.

Enabling the innodb_file_per_table option

Within your favorite text editor, open the file:

/etc/my.cnf

Within your my.cnf, you should see the section:

[mysqld]

This section controls which options are loaded when the mysqld service daemon starts up. Below [mysqld] section start, add:

innodb_file_per_table=1

Save your changes and restart the MySQL service to enable this option.

The innodb_file_per_table option will create tables with their own per-table tablespaces. Tables that are created with innodb_file_per_table enabled will have a .ibd file created in the appropriate directory (e.g. /var/lib/mysql/mydatabase/mytable.ibd). Note that this file will contain both the data and indexes for your InnoDB tables as opposed to MyISAM which stores data in a .MYD file and indexes in a .MYI file. In the event that a single table experiences database corruption, now only that table will need to be restored from backups. The corruption will be restricted to the specific table's .ibd file.

Converting existing InnoDB tables

The changes made above will only apply to new tables created after innodb_file_per_table was enabled. Any tables that previously existed prior to enabling this option will still be using the shared tablespace in ibdata1. We'll now need to switch these tables over to per-table tablespaces. The following SQL query will compile a list of tables using InnoDB and generate the necessary commands to convert them each of them to a per-table tablespace. This query should be run from the MySQL command line.

select concat('alter table ',TABLE_SCHEMA ,'.',table_name,' ENGINE=InnoDB;') FROM INFORMATION_SCHEMA.tables 
where table_type='BASE TABLE' and engine = 'InnoDB';