How to enable innodb_file_per_table in MySQL or MariaDB

Enabling innodb_file_per_table keeps each InnoDB table in its own tablespace file, making drops and rebuilds reclaim disk space at the filesystem level and preventing the shared system tablespace from becoming a forever-growing junk drawer.

MySQL and MariaDB expose innodb_file_per_table as a server variable read by mysqld from its option files (for example /etc/mysql/conf.d or /etc/my.cnf.d). When the setting is ON, user tables are typically stored as individual .ibd files under the server datadir, while internal structures remain in the system tablespace.

Many modern installations already default to ON, but an explicit configuration override keeps the behavior consistent across restarts and across servers in a fleet. Switching the variable does not automatically convert existing tables created with it disabled; moving those tables into their own .ibd files requires a table rebuild that can be slow, I/O heavy, and temporarily space-hungry. A planned restart window and a recent backup reduce the risk of an unpleasant surprise.

1. Check the current server value of innodb_file_per_table.

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

   On systems using socket authentication for root, sudo mysql can replace mysql -uroot -p.

2. List the option files that mysqld 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.0
   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.
   --defaults-file=#       Only read default options from the given file #.
   --defaults-extra-file=# Read this file after the global files are read.
   --defaults-group-suffix=#
                           Also read groups with concat(group, suffix)
   --login-path=#          Read this path from the login file.

   A drop-in file under an included directory (common examples: /etc/mysql/conf.d/, /etc/my.cnf.d/, /etc/mysql/mariadb.conf.d/) keeps the change isolated and easy to revert.

3. Create a mysqld configuration snippet that forces innodb_file_per_table to ON.

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

   When /etc/mysql/conf.d/ does not exist, place the snippet in a directory shown by mysqld –verbose –help (frequent alternatives: /etc/my.cnf.d/ or /etc/mysql/mariadb.conf.d/).

4. Confirm the option file content contains the correct [mysqld] stanza.

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

   A typo or placing the option outside [mysqld] can prevent mysqld from starting.

5. Restart the database service to apply the configuration.

   $ sudo docker restart sg-mysql
   sg-mysql

   On non-container hosts, restart the service manager unit for MySQL or MariaDB instead.

   Restarting mysqld interrupts active connections and should be scheduled for a maintenance window.

6. Confirm the service is running cleanly after the restart.

   $ sudo docker ps --filter name=sg-mysql --format 'table {{.Names}}\t{{.Image}}\t{{.Status}}'
   NAMES      IMAGE       STATUS
   sg-mysql   mysql:8.0   Up 4 seconds

   When startup fails, check the service manager logs for the first configuration error.

7. Verify the runtime value is now ON.

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

8. Create a small InnoDB test table to confirm per-table tablespaces.

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

   The setting affects new InnoDB tables immediately, even when older tables still live in the shared system tablespace.

9. Check the server datadir path used for table files.

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

10. Confirm the test table created a new .ibd file under datadir.

    $ sudo ls -lh /var/lib/mysql/sg_innodb_test/file_per_table_check.ibd
    -rw-r----- 1 mysql mysql 112K Dec 25 00:48 /var/lib/mysql/sg_innodb_test/file_per_table_check.ibd

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

11. Drop the test database after validation.

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

12. Rebuild an existing InnoDB table created while the setting was OFF to migrate it into an .ibd file.

    mysql> ALTER TABLE appdb.orders ENGINE=InnoDB;
    Query OK, 0 rows affected (0.04 sec)
    Records: 0  Duplicates: 0  Warnings: 0

    A table rebuild can be slow and I/O heavy, can require extra free disk space, and can block writers depending on version, storage, and table characteristics.