How to enable innodb_file_per_table in MySQL or MariaDB

Enabling innodb_file_per_table makes new InnoDB tables use their own tablespace files instead of placing user-table data in the shared system tablespace. That keeps per-table storage easier to inspect, makes filesystem-level space reclamation more predictable after drops or rebuilds, and avoids pushing ordinary table data into a single forever-growing shared file.

The setting is a global server variable read by mysqld or mariadbd at startup and available at runtime through SHOW VARIABLES and SET GLOBAL. When it is ON, new InnoDB tables usually create a matching .ibd file inside the schema directory under the server datadir. Tables that were created while the setting was OFF keep their original layout until they are rebuilt or moved.

Current MySQL and MariaDB releases already default to ON, but an explicit option-file override keeps that behavior consistent after restarts and across hosts where the variable was previously disabled. Changing the variable requires an account that can modify global settings, and moving older tables into per-table tablespaces can be slow, space-hungry, and disruptive on busy systems.

1. Connect with an account that can change global variables.

   Examples below use mysql. On some MariaDB systems, the client command is mariadb instead. When root uses socket authentication, sudo mysql can replace mysql -u root -p.

2. Check the current runtime value.

   $ mysql --table -u root -p -e "SHOW VARIABLES LIKE 'innodb_file_per_table';"
   Enter password:
   +-----------------------+-------+
   | Variable_name         | Value |
   +-----------------------+-------+
   | innodb_file_per_table | ON    |
   +-----------------------+-------+

3. Enable the variable immediately when it is currently OFF.

   $ mysql -u root -p -e "SET GLOBAL innodb_file_per_table = ON;"
   Enter password:

   The runtime change affects new InnoDB tables right away. Existing tables that were created while the setting was OFF remain in their current tablespace until they are rebuilt or moved.

4. Verify the runtime value again.

   $ mysql --table -u root -p -e "SHOW VARIABLES LIKE 'innodb_file_per_table';"
   Enter password:
   +-----------------------+-------+
   | Variable_name         | Value |
   +-----------------------+-------+
   | innodb_file_per_table | ON    |
   +-----------------------+-------+

5. Check which option files the server reads at startup.

   $ mysqld --verbose --help 2>/dev/null | sed -n '/Default options are read from the following files in the given order:/,/^$/p'
   Default options are read from the following files in the given order:
   /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 
   The following groups are read: mysql_cluster mysqld server mysqld-8.4
   The following options may be given as the first argument:
   --print-defaults        Print the program argument list and exit.
   --no-defaults           Don't read default options from any option file,
                           except for login file.

   On MariaDB, mariadbd --verbose --help may be clearer, and the output often points to drop-in directories such as /etc/mysql/mariadb.conf.d/ and /etc/mysql/conf.d/.

6. Create a drop-in option file that keeps the setting enabled after restarts.

   $ sudo tee /etc/mysql/conf.d/innodb-file-per-table.cnf >/dev/null <<'EOF'
   [mysqld]
   innodb_file_per_table=ON
   EOF

   Use a directory that is actually included on the server. Common alternatives are /etc/my.cnf.d/ and /etc/mysql/mariadb.conf.d/.

   The running server already uses the value set with SET GLOBAL. This option file keeps the same behavior after the next service restart or host reboot.

7. Confirm the option file content.

   $ sudo cat /etc/mysql/conf.d/innodb-file-per-table.cnf
   [mysqld]
   innodb_file_per_table=ON

   A typo or placing the directive outside [mysqld] can prevent the database service from starting cleanly after the next restart.

8. Create a small test table after innodb_file_per_table is ON.

   $ mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS sg_innodb_test; DROP TABLE IF EXISTS sg_innodb_test.file_per_table_check; CREATE TABLE sg_innodb_test.file_per_table_check (id INT PRIMARY KEY) ENGINE=InnoDB;"
   Enter password:

9. Check the server datadir so the expected tablespace path is clear.

   $ mysql --table -u root -p -e "SHOW VARIABLES LIKE 'datadir';"
   Enter password:
   +---------------+-----------------+
   | Variable_name | Value           |
   +---------------+-----------------+
   | datadir       | /var/lib/mysql/ |
   +---------------+-----------------+

10. Confirm that the new table created its own .ibd file.

    $ sudo ls -lh /var/lib/mysql/sg_innodb_test/file_per_table_check.ibd
    -rw-r----- 1 mysql mysql 112K Apr  9 15:02 /var/lib/mysql/sg_innodb_test/file_per_table_check.ibd

    The schema directory is the database name under datadir, and the tablespace file usually matches the table name with an .ibd suffix.

11. Remove the test schema after validation.

    $ mysql -u root -p -e "DROP DATABASE sg_innodb_test;"
    Enter password:

12. Move older tables that were created while innodb_file_per_table was OFF into per-table tablespaces.

    $ mysql -u root -p -e "ALTER TABLE appdb.orders TABLESPACE=innodb_file_per_table;"
    Enter password:

    On MariaDB, rebuild the table with ALTER TABLE appdb.orders ENGINE=InnoDB; after the variable is ON.

    Moving or rebuilding a large table can take time, consume extra disk space, and hold blocking locks depending on server version, table structure, and workload. Schedule the change during a maintenance window and confirm backups first.