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.

When I import a DB I always use this command: `mysql -u root -p database_name < path_to_sql_file/file.sql` — CptMisery, Nov 30 '16 at 19:26
FYI, I'm not saying it works better. That is just what I use and I've never heard of your way. — CptMisery, Nov 30 '16 at 19:26
100 possible reasons. Start by telling us is it a MYISAM or INNODB database? — RiggsFolly, Nov 30 '16 at 20:02
I haven't done any buffer configuration, how would I go about do that? — John C, Nov 30 '16 at 20:21
INNODB - https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb.html assuming its INNODB tables you are using — RiggsFolly, Dec 01 '16 at 09:36

score 3 · Answer 1 · answered May 07 '18 at 13:04

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;

Mysql database import extremely slow

1 Answers1