BIP KB:
Table | Use one file per table with MySQL's INNODB storage

Introduction to MySQL INNODB engine table storage InnoDB is a general-purpose storage engine that balances high reliability and high performance. In MySQL 5.6, InnoDB is the default MySQL storage engine. Unless you have configured a different default storage engine, issuing a CREATE TABLE statement without an ENGINE= clause creates an InnoDB table. InnoDB includes all the features that were part of the InnoDB Plugin for MySQL 5.1, plus new features specific to MySQL 5.5 and higher.

Note: The mysql and INFORMATION_SCHEMA databases that implement some of the MySQL internals still use MyISAM. In particular, you cannot switch the grant tables to use InnoDB.

By default MySQL INNODB engine puts all the data for all the INNODB tables for all databases into the same file. This file will grow as it is used but will never shrink, even if you drop a very large INNODB table. This post shows how instead to have a data file per table in the database's directory which is the same as the way the MyISAM engine works.

Disclaimer

Use with caution and ensure you test on a non-production system first.

Change INNODB to use one file per table

Stop the MySQL server (usually "/etc/init.d/mysql stop" on Linux).

Edit the my.cnf file (located at somewhere like /etc/my.cnf or /etc/mysql/my.cnf)

Add "innodb_file_per_table" on a single line by itself into the my.cnf file.

Start the MySQL server ("/etc/init.d/mysql start").

Any new INNODB tables that are created will now be created in the database's directory with a single file per database table. Doing this does not affect any existing INNODB tables; they will still use the common data file.

Reclaiming space

The only way to reclaim the space used by the common file and existing tables is to drop all the INNODB tables, stop MySQL, delete the common INNODB data files, start MySQL and re-import the tables into the appropriate databases. Alternatively you can go the whole hog and make a complete database backup including all triggers, stored procedures etc, drop all databases and so on. When MySQL is restarted after deleting the common INNODB tables it re-creates them.

How to do this is covered here on the MySQL mailing lists. Again, please ensure if you are going to do this that you use it with extreme caution and test first on a non-production environment. Also it may take some time to a) backup your databases/tables and b) re-import them.