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:
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:
Inside the section:
This sets using a datafile by table, although there are two problems:
- It is not retroactive, only new tables will have a separate datafile.
- 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:
- 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.
- Starting MySQL and make individual export from each of the BD, this can be done with::
mysqldump -uroot -ppass --routines --triggers --lock-all-tables name_bd > name_bd.sql
- Remove the BDs (excluding own MySQL of course) with:
mysql> DROP DATABASE name_bd;
- Stop again and delete files MySQL (InnoDB) in the /var/lib/mysql (in my case):
ibdata1 ib_logfile0 (log file1) ib_logfile1 (log file1)
- Starting MySQL and observe the creation of new files deleted in the previous point. This time ibdata1 will occupy about 10MB.
- Create all BD:
mysql> CREATE DATABASE name_bd;
- Import all BDs one by one with:
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