By default, all InnoDB tables and indexes are stored in the system tablespace. As an alternative, you can store each InnoDB table and its indexes in its own file. This feature is called “multiple tablespaces” because each table that is created when this setting is in effect has its own tablespace.

Using multiple tablespaces is useful in a number of situations:

Enabling and Disabling Multiple Tablespaces

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

[mysqld]
innodb_file_per_table

With multiple tablespaces enabled, InnoDB stores each newly created table in its own tbl_name.ibd file in the appropriate database directory. Unlike the MyISAM storage engine, with its separate tbl_name.MYD and tbl_name.MYI files for indexes and data, InnoDB stores the data and the indexes together in a single .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, InnoDB creates any new tables inside the shared tablespace files.

You can always access both tables in the system tablespace and tables in their own tablespaces, regardless of the file-per-table setting. To move a table from the system tablespace to its own tablespace, or vice versa, you can change the innodb_file_per_table setting and issue the command:

-- Move table from system tablespace to its own tablespace.
SET GLOBAL innodb_file_per_table=1;
ALTER TABLE table_name ENGINE=InnoDB;
-- Move table from its own tablespace to system tablespace.
SET GLOBAL innodb_file_per_table=0;
ALTER TABLE table_name ENGINE=InnoDB;

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:

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:

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