How to import big MySQL dump quickly

Warning: This trick is only for InnoDB storage engine.

Open MySQL config file “/etc/mysql/my.cnf”:

mcedit /etc/mysql/my.cnf

Write into [mysqld] section:

[mysqld]
...
innodb_flush_log_at_trx_commit = 2

What does that mean? Now, data will be recorded not more often than once per second! It will very unload file system and increase speed of import.

But you need check that you haven’t this option:

...
general_log = 1
...

Because everything will be worthless with that option! Every SQL query will be written on the disk and import will be worked slowly.

You need to disable “slow_query_log” if it enabled too.

Well, if you don’t have that options or you have deactivated it, for the next step you need to restart the database server:

service mysql restart

I have to notice you, if your dump archived in gzip, you need to unpack it and import only raw dump! Because unpacking on the fly and importing at the same time works slowly.

So, let’s import:

mysql -u username -p db_name < dump.sql

After all don’t forget to delete or restore “innodb_flush_log_at_trx_commit” option from config “/etc/mysql/my.cnf” and reload mysql server!

Sources: ruhighload and mysql.com.

 44   1 mon  
Popular