14.2.3.1. Using Per-Table Tablespaces
You can store each InnoDB table and its
indexes in 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.
You can enable multiple tablespaces by adding this 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.ibdMyISAM storage engine does, but
MyISAM divides the table into a data file
tbl_name.MYDtbl_name.MYIInnoDB, the data and the indexes are
stored together in the .ibd file. The
tbl_name.frm
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, not access to existing tables. 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
and restart the server, 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
because it puts its internal data dictionary and undo logs
there. The .ibd files are not sufficient
for InnoDB to operate.
Note: You cannot freely move
.ibd files between database directories as
you can with MyISAM table files. This is
because the table definition that is stored in the
InnoDB shared tablespace includes the
database name, and because InnoDB must
preserve the consistency of transaction IDs and log sequence
numbers.
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:
-
Issue this ALTER TABLE statement:
ALTER TABLE tbl_name DISCARD TABLESPACE;
Caution: This statement
deletes the current .ibd file.
Put the backup .ibd file back in the
proper database directory.
-
Issue this ALTER TABLE statement:
ALTER TABLE tbl_name IMPORT TABLESPACE;
In this context, a “clean”
.ibd file backup means:
There are no uncommitted modifications by transactions in
the .ibd file.
There are no unmerged insert buffer entries in the
.ibd file.
Purge has removed all delete-marked index records from the
.ibd file.
mysqld has flushed all modified pages of
the .ibd file 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 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 commercial
InnoDB Hot Backup tool:
Use InnoDB Hot Backup to back up the
InnoDB installation.
Start a second mysqld server on the
backup and let it clean up the .ibd
files in the backup.
