I have this huge 32 GB SQL dump that I need to import into MySQL. I haven't had to import such a huge SQL dump before. I did the usual:

mysql -uroot dbname < dbname.sql

It is taking too long. There is a table with around 300 million rows, it's gotten to 1.5 million in around 3 hours. So, it seems that the whole thing would take 600 hours (that's 24 days) and is impractical. So my question is, is there a faster way to do this?

Further Info/Findings

  1. The tables are all InnoDB and there are no foreign keys defined. There are, however, many indexes.
  2. I do not have access to the original server and DB so I cannot make a new back up or do a "hot" copy etc.
  3. Setting innodb_flush_log_at_trx_commit = 2 as suggested here seems to make no (clearly visible/exponential) improvement.
  4. Server stats during the import (from MySQL Workbench): https://imgflip.com/gif/ed0c8.
  5. MySQL version is 5.6.20 community.
  6. innodb_buffer_pool_size = 16M and innodb_log_buffer_size = 8M. Do I need to increase these?
  • Can you add faster components to the server, namely more RAM and SSD storage? – Bert Nov 19 '14 at 20:48
  • @Bert the server has 8 GB of RAM most of which is just unused. Can't add more storage either. How would that help? Is it really the write operations that are so slow? – SBhojani Nov 19 '14 at 20:53
  • What's the bottleneck? Is a CPU Core pegged? – Chris S Nov 19 '14 at 21:26
  • @ChrisS no, the CPU usage is 3 to 4%. I'm not sure what the bottleneck is. I'm thinking it's the indexes. How would one find/confirm the bottleneck? – SBhojani Nov 19 '14 at 21:28

Percona's Vadim Tkachenko made this fine Pictorial Representation of InnoDB

InnoDB Architecture

You definitely need to change the following

innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_write_io_threads = 16
innodb_flush_log_at_trx_commit = 0

Why these settings ?

Restart mysql like this

service mysql restart --innodb-doublewrite=0

This disables the InnoDB Double Write Buffer

Import your data. When done, restart mysql normally

service mysql restart

This reenables the InnoDB Double Write Buffer

Give it a Try !!!

SIDE NOTE : You should upgrade to 5.6.21 for latest security patches.

  • 1
    I made a linux bash script for it, lowered some values to work inside vagrant with lower memory gist.github.com/OZZlE/57d550c3cc1c1ff17481e465e4f6d674 – OZZIE Dec 14 '18 at 9:12
  • there is no parameter name like innodb_write_io_threads . i am gonna test other suggested changes – MonsterMMORPG Feb 14 '20 at 8:38
  • @MonsterMMORPG innodb_write_io_threads came into existence starting in MySQL 5.1.38. If you are using MySQL 5.0 up to 5.1.37, that's why you don't see it. See dev.mysql.com/doc/refman/8.0/en/… – RolandoMySQLDBA Feb 14 '20 at 15:08
  • @MonsterMMORPG Just for completeness, Percona had that option in Percona Server 5.0 in its InnoDB/XtraDB before MySQL made it available in 5.1.38 by means of the InnoDB Plugin.. – RolandoMySQLDBA Jun 20 '20 at 14:27
  • Did anyone actually ever try this? What is the average performance improvement after changing these flags? – Nebulastic Oct 27 '20 at 11:39

Do you really need the entire database to be restored? If you don't, my 2c:

You can extract specific tables to do your restore on "chunks". Something like this:

zcat your-dump.gz.sql | sed -n -e '/DROP TABLE.*`TABLE_NAME`/,/UNLOCK TABLES/p' > table_name-dump.sql

I did it once and it took like 10 minutes to extract the table I needed - my full restore took 13~14 hours, with a 35GB (gziped) dump.

The /pattern/,/pattern/p with the -n parameter makes a slice "between the patterns" - including them.

Anyways, to restore the 35GB I used an AWS EC2 machine (c3.8xlarge), installed Percona via yum (Centos) and just added/changed the following lines on my.cnf:


I think the numbers are way too high, but worked for my setup.

  • This is a good 2 cents. +1 !!! – RolandoMySQLDBA Nov 28 '19 at 18:16
  • no parameter name as wait_timeout – MonsterMMORPG Feb 14 '20 at 8:39

The fastest way to import your database is to copy the ( .frm, .MYD, .MYI ) files if MyISAM, directly to the /var/lib/mysql/"database name".

Otherwise you can try : mysql > use database_name; \. /path/to/file.sql

Thats another way to import your data.

  • This is not called "import" and it is not safe between MySQL versions. Don't do this until you go with all other options. – tanaydin Aug 4 '20 at 9:15

One way to help speed up the import is to lock the table while importing. Use the --add-locks option to mysqldump.

mysqldump --add-drop-table --add-locks --database db > db.sql

or you could turn on some useful parameters with --opt this turns on a bunch of useful things for the dump.

mysqldump --opt --database db > db.sql

If you have another storage device on the server, then use that - copying from one device to another is a way to speed up transfers.

You can also filter out tables that are not required with --ignore-table


This will do:

mysql --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;SET UNIQUE_CHECKS=0;" -u root -p < Backup_Database.mysql
  • 5
    I suggest to explain, why it makes the import faster and how. – peterh Nov 28 '19 at 17:19
  • 3
    .. and what problems these settings may cause. – Michael Green Nov 29 '19 at 0:39
  • These are already included in the default mysqldump export. – hackel Oct 8 '20 at 20:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.