How can I change the "innodb_file_per_table" parameter from "OFF" to "1" for an existing database?

Question

When switching from MyISAM to InnoBD, I used the default settings. After reading the optimization tips, I realized that each table is better in a separate file. How to transfer the table from one file into a separate file each table mode?

Solution

If you are using MariaDB 10.x, innodb_file_per_table=1 is a default setting there, so as long as you are using defaults, your new InnoDB tables and those converted from MyISAM to InnoDB will already have separate tablespaces.

If you are using MariaDB 5.5, innodb_file_per_table=0 by default. To start using individual tablespaces, run SET GLOBAL innodb_file_per_table = 1; . Since then and till server restart all newly created and altered InnoDB tables will use this option. To make the option permanent, add it to the cnf file.

To convert existing InnoDB tables into individual tablespaces, you can run ALTER TABLE <tablename> ENGINE=InnoDB or ALTER TABLE <tablename> FORCE.