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 other InnoDB
tables.
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 into its own
file in the database directory where the table belongs. This is
similar to what the tbl_name
.ibdMyISAM
storage engine
does, but MyISAM
divides the table into a
data file and an
tbl_name
.MYD
index file. For tbl_name
.MYIInnoDB
, the data and the
indexes are stored together in the .ibd
file. The
file is still created as usual.
tbl_name
.frm
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.
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.
The --innodb_file_per_table
option 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 start
the server without this 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
because it puts its internal data dictionary and undo logs
there. The .ibd
files are not sufficient
for InnoDB
to operate.
To move an .ibd
file and the associated
table from one database to another, use a
RENAME TABLE
statement:
RENAME TABLEdb1.tbl_name
TOdb2.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 to delete the current .ibd
file:
ALTER TABLE tbl_name
DISCARD TABLESPACE;
Copy the backup .ibd
file to the proper
database directory.
Issue this ALTER TABLE
statement to tell InnoDB
to use the new
.ibd
file for the table:
ALTER TABLE tbl_name
IMPORT 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 .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.
User Comments
Note that the advice above regarding the DISCARD TABLESPACE and IMPORT TABLESPACE only works when you restore the .ibd file to the "installation from which it originated." If you try to copy ibd files into a new database instance you will get an error like this:
InnoDB: Error: tablespace id in file './test/<ibd_file_name.ibd>' is 38, but in the InnoDB data dictionary it is 401.
When you run:
ALTER TABLE <table_name> IMPORT TABLESPACE
It is possible to copy InnoDB tables between two databases using mysqldump and mysqlimport.
Note that .ibd files are always single auto-extending files.
So it's NEITHER possible to set an initial size for .ibd files NOR to set a maximum file size for them.
This information was posted by Heikki Tuuri (creator of the InnoDB transactional storage engine) in the InnoDB forum of the MySQL web site, in January 2006, at:
http://forums.mysql.com/read.php?22,26788,63040#msg-63040
I am actually using this feature to allow me to maintain disk usage. Here is details on what I am trying to achieve. Hopefully it will help somebody. http://crazytoon.com/2007/04/03/mysql-ibdata-files-do-not-shrink-on-database-deletion-innodb/
If you need to move some or all of your tables to separate .ibd files, you may try the following:
1) add "innodb_file_per_table" option to my.cnf
2) restart mysqld
3) do an ALTER TABLE <table> ENGINE=InnoDB; on each table you want to move apart. This will recreate the table in its separate .ibd file.
The reverse also applies. Coalescing and/or moving apart tables only requires you to restart mysqld with or without the innodb_file_per_table option flag.
Thanks Dario Souza,
This query will generate ALTER TABLE ENGINE=InnoDB; type statements for all tables.
select concat(concat('alter table ',table_name),' ENGINE=InnoDB;') FROM INFORMATION_SCHEMA.tables
where table_type='BASE TABLE'
I think this SQL would do a better job, following murtuja khokhar advice:
select concat('alter table ',TABLE_SCHEMA ,'.',table_name,' ENGINE=InnoDB;') FROM INFORMATION_SCHEMA.tables
where table_type='BASE TABLE' and engine = 'InnoDB'
As not all tables are in the same schema and also not InnoDB based.
Hilay Selivansky
הילי זליבנסקי
the following will also do the trick
mysqlcheck --optimize --all-databases
Just in case, you can also use:
innodb_file_per_table = 1
..in your my.cnf file under [mysqld] section. It has the same effect as just "innodb_file_per_table".
Although SHOW GLOBAL VARIABLES shows innodb_file_per_table to be "ON", beware of setting
[mysqld]
...
innodb_file_per_table = ON
in the cnf file. "ON" is not a valid value. Either use "1" or leave blank.
Note that ALTER TABLE ... DISCARD TABLESPACE does NOT work if your table uses mysql 5.1 partitioning. ie, if your CREATE TABLE statement has this in it:
PARTITION BY ...
Then trying to discard the tablespace gives you this error:
ERROR 1031 (HY000): Table storage engine for 'foo' doesn't have this option
Add your own comment.