14.5.3 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;
      
Note

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 might prefer to turn on innodb_file_per_table and then recreate the entire instance using the mysqldump command.