Mysql database import extremely slow

Question

I know this question has already been asked, but no answers seem to be helpful. I have a database containing 40,000,000 entries that I need to upload to wamp. I used the mysql command line and typed the following commands...

use database_name

source D:/pathtodatabase

It has been running all night and only uploaded 3,195,000 rows (1.3 Gib). What is a way I can speed up this import? I also know for a fact my computer is not a bottleneck as I am using a 7200 rpm drive and an i7-3770.

Answer 1

Got the same issue when trying to import a large database. I tried several solutions and found this answer by Alex worked for me. Here is the complete process based on his suggestion (using Ubuntu 16.04):

1, enter mysql server

mysql -u username -p

2, change to the database that you want to import the data

use database_name

3, optimize the import operation, more info here

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

4, import data

source path/to/datafile.sql

5, change back the default configuration

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