MySQL InnoDB file per table

During a default installation of MySQL, a common mistake is not changing the settings as stored data in InnoDB engine. By default, all information is stored in one datafile, which is usually named:

Source   
ibdata1

After so happily import a BD weighing 500 Mb at the time and one year later you can take 4Gb, well everything is stored in the datafile. If we then add a new BD also with InnoDB tables, the problem increases then we all mixed, everything is in the same datafile.

A ibdata1 corruption involves restoring all tables in all BD using InnoDB. Rather dangerous truth.

To fix this we can add in the parameter file:

Source   
innodb_file_per_table

Inside the section:

Source   
[mysqld]

This sets using a datafile by table, although there are two problems:

  1. It is not retroactive, only new tables will have a separate datafile.
  2. There is no way to reduce or compact the datafile ibdata1.

They are to perform certain tasks to get the goal of having a table and datafile by reduced ibdata1:

  1. Cold copy of all files from MySQL. That is stop the MySQL service and copy /var/lib/mysql (in my case) where they are stored all BD.
  2. Starting MySQL and make individual export from each of the BD, this can be done with::
    Source   
    mysqldump -uroot -ppass --routines --triggers --lock-all-tables name_bd > name_bd.sql
  3. Remove the BDs (excluding own MySQL of course) with:
    Source   
    mysql> DROP DATABASE name_bd;
  4. Stop again and delete files MySQL (InnoDB) in the /var/lib/mysql (in my case):
    Source   
    ibdata1
    ib_logfile0 (log file1)
    ib_logfile1 (log file1)
  5. Starting MySQL and observe the creation of new files deleted in the previous point. This time ibdata1 will occupy about 10MB.
  6. Create all BD:
    Source   
    mysql> CREATE DATABASE name_bd;
  7. Import all BDs one by one with:
    Source   
    mysql -uroot -ppass name_bd < name_bd

If we have not skip any steps should have no problem, now a corruption of a datafile affect only a table

Leave a Reply