MySQL Reference Manual :: 15.7.6 Using Per-Table Tablespaces

NOTE: A bug in versions <= 4.1.8 if you specify innodb_file_per_table in my.cnf! If you shut down mysqld, then records may disappear from the secondary indexes of a table. See (Bug #7496) for more information and workarounds. This is fixed in 4.1.9, but another bug (Bug #8021) bit the Windows version in 4.1.9, and in the Windows version of 4.1.9 you must put the line innodb_flush_method=unbuffered to your my.cnf or my.ini to get mysqld to work.

Starting from MySQL 4.1.1, you can store each InnoDB table and its indexes into its own file. This feature is called ``multiple tablespaces'' because in effect each table has its own tablespace.

Using multiple tablespaces can be beneficial to users who want to move specific tables to separate physical disks or who wish to restore backups of single tables quickly without interrupting the use of the remaining InnoDB tables.

If you need to downgrade to 4.0, you have to take table dumps and re-create the whole InnoDB tablespace. If you have not created new InnoDB tables under MySQL 4.1.1 or later, and need to downgrade quickly, you can also do a direct downgrade to the MySQL 4.0.18 or later in the 4.0 series. Before doing the direct downgrade to 4.0.x, you have to end all client connections to the mysqld server that is to be downgraded, and let it run the purge and insert buffer merge operations to completion, so that SHOW INNODB STATUS shows the main thread in the state waiting for server activity. Then you can shut down mysqld and start 4.0.18 or later in the 4.0 series.

You can enable multiple tablespaces by adding a line to the [mysqld] section of my.cnf:

[mysqld]
innodb_file_per_table

After restarting the server, InnoDB stores each newly created table into its own file tbl_name.ibd 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 data file tbl_name.MYD and the index file tbl_name.MYI. 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, InnoDB creates tables inside the shared tablespace files again.

innodb_file_per_table affects only table creation. If you start the server with this option, new tables are created using .ibd files, but you can still access tables that exist in the shared tablespace. If you remove the option, new tables are created in the shared tablespace, but you can still access any tables that were created using multiple tablespaces.

InnoDB always needs the shared tablespace. The .ibd files are not sufficient for InnoDB to operate. The shared tablespace consists of the familiar ibdata files where InnoDB puts its internal data dictionary and undo logs.

You cannot freely move .ibd files around between database directories the way you can with MyISAM table files. This is because the table definition is stored in the InnoDB shared tablespace, and also because InnoDB must preserve the consistency of transaction IDs and log sequence numbers.

Within a given MySQL installation, you can move an .ibd file and the associated table from one database to another with the familiar RENAME TABLE statement:

RENAME TABLE old_db_name.tbl_name TO new_db_name.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 means:

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

Another method for making a clean copy of an .ibd file is to use the commercial InnoDB Hot Backup tool:

It is in the TODO to also allow moving clean .ibd files to another MySQL installation. This requires resetting of transaction IDs and log sequence numbers in the .ibd file.