InnoDB and the innodb file per table option

Introduction

Starting with MySQL 5.5, InnoDB is the default storage engine for MySQL. In most versions of MySQL, 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. 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 which will create a separate file and tablespace for each InnoDB table.

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 the [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 now 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';

As an example, let's assume I have an existing database using InnoDB tables. This database has the following InnoDB tables in it:

myexampledatabase.table1
myexampledatabase.table2
myexampledatabase.table3

In this example, I've just enabled innodb_file_per_table and I want to convert table1, table2, and table3 over to the per-table tablespace. When I run the above SQL query, I will receive this output:

alter table myexampledatabase.table1 ENGINE=InnoDB;
alter table myexampledatabase.table2 ENGINE=InnoDB;
alter table myexampledatabase.table3 ENGINE=InnoDB;

Each of these commands can be run to move the respective table from the shared tablespace to per-table. These commands would need to be run via the MySQL command line.

Further Considerations

System Load

Converting tablespaces my require substantial resources depending on the size of the database. If you plan to alter the tablespaces' of your tables, we suggest doing so during non-peak hours.

Disk Space

As MySQL creates a new .ibd file for your table, it will require the necessary disk space to do so. Please ensure you have plenty of free space on your MySQL partition before attempting this process. Please also note that your ibdata1 file may not reduce in size after converting your tables to per-table tablespaces.

Continued use of ibdata1

If you've followed this guide, the majority of your MySQL data is now contained within separate .ibd files. However, the ibdata1 file is still used by MySQL for the storage of its internal data dictionary and undo logs. The ibdata1 file should never be deleted from the server.