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


The tables are all InnoDB and there are no foreign keys defined. There are, however, many indexes.
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.
Setting innodb_flush_log_at_trx_commit = 2 as suggested here seems to make no (clearly visible/exponential) improvement.
Server stats during the import (from MySQL Workbench): https://imgflip.com/gif/ed0c8.
MySQL version is 5.6.20 community.
innodb_buffer_pool_size = 16M and innodb_log_buffer_size = 8M. Do I need to increase these?


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



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 ?


innodb_buffer_pool_size will cache frequently read data
innodb_log_buffer_size : Larger buffer reduces write I/O to Transaction Logs
innodb_log_file_size : Larger log file reduces checkpointing and write I/O
innodb_write_io_threads : Service Write Operations to .ibd files. According to MySQL Documentation on Configuring the Number of Background InnoDB I/O Threads, each thread can handle up to 256 pending I/O requests. Default for MySQL is 4, 8 for Percona Server. Max is 64.
innodb_flush_log_at_trx_commit


In the event of a crash, both 0 and 2 can lose once second of data.
The tradeoff is that both 0 and 2 increase write performance.
I choose 0 over 2 because 0 flushes the InnoDB Log Buffer to the Transaction Logs (ib_logfile0, ib_logfile1) once per second, with or without a commit. Setting 2 flushes the InnoDB Log Buffer only on commit. There are other advantages to setting 0 mentioned by @jynus, a former Percona instructor. 



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.
