Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.1Mb
PDF (A4) - 31.1Mb
PDF (RPM) - 29.3Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb
HTML Download (RPM) - 6.2Mb
Man Pages (TGZ) - 176.3Kb
Man Pages (Zip) - 286.5Kb
Info (Gzip) - 3.0Mb
Info (Zip) - 3.0Mb
Excerpts from this Manual

MySQL 5.6 Reference Manual  /  ...  /  Enabling and Disabling File-Per-Table Tablespaces Enabling and Disabling File-Per-Table Tablespaces

The innodb_file_per_table option is enabled by default.

To set the innodb_file_per_table option at startup, start the server with the --innodb_file_per_table command-line option, or add this line to the [mysqld] section of my.cnf:


You can also set innodb_file_per_table dynamically, while the server is running:

mysql> SET GLOBAL innodb_file_per_table=1;

With innodb_file_per_table enabled, you can store InnoDB tables in a tbl_name.ibd file. Unlike the MyISAM storage engine, with its separate tbl_name.MYD and tbl_name.MYI files for indexes and data, InnoDB stores the data and the indexes together in a single .ibd file. The tbl_name.frm file is still created as usual.

If you disable innodb_file_per_table in your startup options and restart the server, or disable it with the SET GLOBAL command, InnoDB creates new tables inside the system tablespace.

You can always read and write any InnoDB tables, regardless of the file-per-table setting.

To move a table from the system tablespace to its own tablespace, change the innodb_file_per_table setting and rebuild the table:

mysql> SET GLOBAL innodb_file_per_table=1;
mysql> ALTER TABLE table_name ENGINE=InnoDB;

InnoDB always needs the system tablespace because it puts its internal data dictionary and undo logs there. The .ibd files are not sufficient for InnoDB to operate.

When a table is moved out of the system tablespace into its own .ibd file, the data files that make up the system tablespace remain the same size. The space formerly occupied by the table can be reused for new InnoDB data, but is not reclaimed for use by the operating system. When moving large InnoDB tables out of the system tablespace, where disk space is limited, you may prefer to enable innodb_file_per_table and recreate the entire instance using the mysqldump command.

User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by Bill Karwin on November 30, 2015
If you want to move the table from a .ibd individual tablespace into the global tablespace, you must use ALGORITHM=COPY:


Moving a table from the global tablespace out to its own individual tablespace in an .ibd file does not require that extra option.
Sign Up Login You must be logged in to post a comment.