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 | OFF   |
   +-----------------------+-------+

   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
   ~/.my.cnf

   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 systemctl restart mysql

   Use sudo systemctl restart mariadb when the unit name is mariadb.

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

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

   $ sudo systemctl status mysql
   ● mysql.service - MySQL Community Server
        Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
        Active: active (running) since Fri 2025-12-12 21:40:12 UTC; 8s ago
      Main PID: 12345 (mysqld)
        Status: "Server is operational"
   ##### snipped #####

   When startup fails, check sudo journalctl --no-pager -u mysql (or ... -u mariadb) 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 12 21:42 /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 -uroot -p -e "ALTER TABLE example_db.example_table ENGINE=InnoDB;"
    Enter password:
    Query OK, 0 rows affected (4.32 sec)

    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.