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:
Storing specific tables on separate physical disks, for I/O optimization or backup purposes.
Restoring backups of single tables quickly without interrupting the use of other
InnoDBtables.Using compressed row format to compress table data.
Reclaiming disk space when truncating a table.
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.ibdMyISAM storage engine, with its separate
tbl_name.MYDtbl_name.MYIInnoDB stores the data
and the indexes together in a single .ibd
file. The
tbl_name.frm
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:
ALTER TABLE table_name ENGINE=InnoDB;
InnoDB always needs the shared tablespace
because it puts its internal data dictionary and undo logs
there. The .ibd files are not sufficient
for InnoDB to operate.
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 TABLEdb1.tbl_nameTOdb2.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:
Issue this
ALTER TABLEstatement to delete the current.ibdfile:ALTER TABLE
tbl_nameDISCARD TABLESPACE;Copy the backup
.ibdfile to the proper database directory.Issue this
ALTER TABLEstatement to tellInnoDBto use the new.ibdfile for the table:ALTER TABLE
tbl_nameIMPORT TABLESPACE;
In this context, a “clean” .ibd
file backup is one for which the following requirements are
satisfied:
There are no uncommitted modifications by transactions in the
.ibdfile.There are no unmerged insert buffer entries in the
.ibdfile.Purge has removed all delete-marked index records from the
.ibdfile.mysqld has flushed all modified pages of the
.ibdfile from the buffer pool to the file.
You can make a clean backup .ibd file using
the following method:
Stop all activity from the mysqld server and commit all transactions.
Wait until
SHOW ENGINE INNODB STATUSshows that there are no active transactions in the database, and the main thread status ofInnoDBisWaiting for server activity. Then you can make a copy of the.ibdfile.
Another method for making a clean copy of an
.ibd file is to use the MySQL Enterprise
Backup product:
Use MySQL Enterprise Backup to back up the
InnoDBinstallation.Start a second mysqld server on the backup and let it clean up the
.ibdfiles in the backup.