Fastest Way to Import Large SQL Dump File

Posted on November 11, 2016 at 6:51 pm

Do you have a huge SQL dump file and you need to import it in a new MySQL database? The first thing I generally do when I need to import a large SQL file is:

Login to your mysql server:

mysql -u root -p

Increase net buffer length and allowed packets:

set global net_buffer_length = 1048576; 
set global max_allowed_packet = 1073741824;

Disable foreign key checks, unique checks and autocommit:

set foreign_key_checks = 0;
set unique_checks = 0;
set autocommit = 0;

Then select your database with this command:

use dbname;

And now start the importing of the SQL dump file:

source /path/to/dump.sql

You should see something like this:

Query OK, 6583 rows affected (0.84 sec)
Records: 6583  Duplicates: 0  Warnings: 0
 
Query OK, 6660 rows affected (0.38 sec)
Records: 6660  Duplicates: 0  Warnings: 0
 
Query OK, 6703 rows affected (1.22 sec)
Records: 6703  Duplicates: 0  Warnings: 0
 
Query OK, 6623 rows affected (0.51 sec)
Records: 6623  Duplicates: 0  Warnings: 0

When it has finished, re-enabled the disabled checks:

set foreign_key_checks = 1;
set unique_checks = 1;
set autocommit = 1;
commit;

Another possibility would be to use this:

deviantintegral/mysql-parallel

More interesting resources:

Importing huge databases faster

Stay Updated

Other Posts

Updated Posts