MySQL: The innodb_file_per_table misconception

Mistakes in the conception phase of a project often pay off in a far far later point in time. This also applies for the setting innodb_file_per_table in MySQL.

Chris / / last updated on

Prio to MySQL 5.6 this option is disabled by default which leads to one huge ibdata1 file being created on your machine for all your databases. The problem with this is that the space allocated on your disk can never be freed again. So if you often import and delete databases (e.g. on your local development machine) the file gets bigger and bigger and there is no way to reduce the size again!

The only way is to export all data, remove all databases and import it again with the innodb_file_per_table option activated (see also StackOverflow)

If innodb_file_per_table setting is active (as in MySQL 5.6 by default) there is not one huge file but single .ibd files per table. Therewith if you delete one table/database the .ibd files will also be removed and the space is yours again.

So perhaps you should keep that in mind if you set up a MySQL server prio to 5.6.

Post Comments to "MySQL: The innodb_file_per_table misconception"

Submit Comment

With the use of this comment form you agree to the saving and processing of your data by this website. More information about the processing of your data can be found in our privacy statement.
Your data will be transmitted securely via SSL.

Meine Magento Extension Bestseller