Using Per-Table Tablespaces

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 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 tbl_name.ibd file 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 tbl_name.MYD data file and an tbl_name.MYI index file. For InnoDB, the data and the indexes are stored together in the .ibd file. The tbl_name.frm file is still created as usual.

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.
Note

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 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:

1.

Issue this ALTER TABLE statement to delete the current .ibd file:

ALTER TABLE tbl_name DISCARD TABLESPACE;

2.

Copy the backup .ibd file to the proper database directory.
3.

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:

1.

Stop all activity from the mysqld server and commit all transactions.
2.

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:

1.

Use InnoDB Hot Backup to back up the InnoDB installation.
2.

Start a second mysqld server on the backup and let it clean up the .ibd files in the backup.

Previous / Next / Up / Table of Contents

User Comments
Posted by Ryan Gerry on October 4 2005 7:54pm [Delete] [Edit]

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.
Posted by Ricardo Marques on May 24 2006 5:48pm [Delete] [Edit]

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
Posted by Sunny Walia on April 4 2007 1:46am [Delete] [Edit]

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/
Posted by Dario Souza on September 19 2007 4:00am [Delete] [Edit]

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.
Posted by murtuja khokhar on March 28 2008 8:52am [Delete] [Edit]

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'
Posted by Hilay Selivansky on June 7 2008 6:14am [Delete] [Edit]

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
הילי זליבנסקי

Posted by Brian Demant on February 5 2009 8:36pm [Delete] [Edit]

the following will also do the trick

mysqlcheck --optimize --all-databases
Posted by Rishi Pande on April 14 2009 8:31am [Delete] [Edit]

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".