5.4.1.1. Enabling and Disabling File-Per-Table Mode

To make file-per-table mode the default for a MySQL server, start the server with the --innodb_file_per_table command-line option, or add this line to the [mysqld] section of my.cnf:

[mysqld]innodb_file_per_table

You can also issue the command while the server is running:

SET GLOBAL innodb_file_per_table=1;

With file-per-table mode enabled, InnoDB stores each newly created table in its own tbl_name.ibd file in the appropriate database directory. 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 remove innodb_file_per_table from your startup options and restart the server, or turn it off with the SET GLOBAL command, InnoDB creates any 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, or vice versa, change the innodb_file_per_table setting and rebuild the table:

-- Move table from system tablespace to its own tablespace.SET GLOBAL innodb_file_per_table=1;ALTER TABLE table_name ENGINE=InnoDB;-- Move table from its own tablespace to system tablespace.SET GLOBAL innodb_file_per_table=0;ALTER TABLE table_name ENGINE=InnoDB;