14.8.4 InnoDB File-Per-Table Tablespaces Enabling and Disabling File-Per-Table Tablespaces

By default, all InnoDB tables and indexes are stored in the system tablespace. As an alternative, you can store each InnoDB table and associated indexes in its own data file. This feature is called file-per-table tablespaces because each table has its own tablespace, and each tablespace has its own .ibd data file. This feature is controlled by the innodb_file_per_table configuration option.

Advantages of File-Per-Table Tablespaces

Potential Disadvantages of File-Per-Table Tablespaces

Enabling and Disabling File-Per-Table Tablespaces

To enable file-per-table tablespaces, start the server with the --innodb_file_per_table option. For example, add a line to the [mysqld] section of my.cnf:


With innodb_file_per_table enabled, InnoDB stores each newly created table into its own tbl_name.ibd file in the database directory where the table belongs. This is similar to what the MyISAM storage engine does, but MyISAM divides the table into a tbl_name.MYD data file and an tbl_name.MYI index file. For InnoDB, the data and the indexes are stored together in the .ibd file. The tbl_name.frm file is still created as usual.

If you remove the innodb_file_per_table line from my.cnf and restart the server, newly created InnoDB tables are created inside the shared tablespace files again.

To move a table from the system tablespace to its own tablespace, change the innodb_file_per_table setting and rebuild the table:

SET GLOBAL innodb_file_per_table=1;

InnoDB requires the shared tablespace to store its internal data dictionary and undo logs. The .ibd files alone are not sufficient for InnoDB to operate.

When a table is moved out of the system tablespace into its own .ibd file, the data files that make up the system tablespace remain the same size. The space formerly occupied by the table can be reused for new InnoDB data, but is not reclaimed for use by the operating system. When moving large InnoDB tables out of the system tablespace, where disk space is limited, you might prefer to turn on innodb_file_per_table and then recreate the entire instance using the mysqldump command.

Portability Considerations for .ibd Files

You cannot freely move .ibd files between database directories as you can with MyISAM table files. The table definition stored in the InnoDB shared tablespace includes the database name. The transaction IDs and log sequence numbers stored in the tablespace files also differ between databases.

To move an .ibd file and the associated table from one database to another, use a RENAME TABLE statement:

RENAME TABLE db1.tbl_name TO db2.tbl_name;

If you have a clean backup of an .ibd file, you can restore it to the MySQL installation from which it originated as follows:

  1. The table must not have been dropped or truncated since you copied the .ibd file, because doing so changes the table ID stored inside the tablespace.

  2. Issue this ALTER TABLE statement to delete the current .ibd file:

  3. Copy the backup .ibd file to the proper database directory.

  4. Issue this ALTER TABLE statement to tell InnoDB to use the new .ibd file for the table:


In this context, a clean .ibd file backup is one for which the following requirements are satisfied:

You can make a clean backup .ibd file using the following method:

  1. Stop all activity from the mysqld server and commit all transactions.

  2. Wait until SHOW ENGINE INNODB STATUS shows that there are no active transactions in the database, and the main thread status of InnoDB is Waiting for server activity. Then you can make a copy of the .ibd file.

Another method for making a clean copy of an .ibd file is to use the MySQL Enterprise Backup product:

  1. Use MySQL Enterprise Backup to back up the InnoDB installation.

  2. Start a second mysqld server on the backup and let it clean up the .ibd files in the backup.