Search code examples
mysqldatabaseimportdump

MYSQL: issue while import big db (very slow)

I want to import a very big SQL file into MySql (30GB).

I have tried bigdump.php(some error with foreign keys) script and simple import. After that I tried to do this by command line:

(mysql -u username -p database_name < file.sql)

After 2 hours I had a 9 GB db. Then after a further 10 hours, I had a 10.1 GB database.

It seems that process is running now but very slowly(200000 rows / 100MB per hour).

How to fix this issue ? Thank you

Solution

  • This is most probably due to key constraints that MySQL needs to check for each row. But since you (hopefully) already know that the file contains no constraint violations you might want to turn off some checks. MySQL have a manual page about Bulk Data Loading for InnoDB Tables and Speed of INSERT Statements.

    What you usually want to do is adding these commands to the top of your .sql file:

    SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;

    And these to the bottom:

    COMMIT;
SET unique_checks=1;
SET foreign_key_checks=1;

    You can do this in the MySQL CLI without editing the .sql file directly:

    SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
SOURCE yourbigfile.sql;
COMMIT;
SET unique_checks=1;
SET foreign_key_checks=1;

    Note that this will not work with bigdump.php because that script will not remember these settings between each chunk. I do not think bigdump.php will work very well at all with dumps that contains foreign keys.