Speed Up MySQL import for Large dump file (.sql)

gistfile1.txt

I use linux ( ubuntu ) as my Primary Environment. Recently, I had to import a very large Mysql dump file (40.5GB .sql) in my local environment. Normally, it was taking couple of hours (4/5 hours). I would be happy with that ( as we dont need to import db regularly), but my dump file had couple of errors which was inturrpting import process.

So keeping the following notes for my personal usage. But that might help others also.

Below are the steps to follow, to speed up the process.

# OPEN THIS FILE

sudo gedit /etc/mysql/my.cnf

Configurations to put under [mysqld]

#PUT BELOW MENTIONED VALUES AT THE END OF my.cnf file 
[mysqld]
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 

Commands to restart Mysql Service

# RESTART MySQL USING THE COMMAND BELOW 

sudo service mysql restart --innodb-doublewrite=0 



# AFTER IMPORT IS DONE, RESTART MySQL Normally 

sudo service mysql restart 



## THINGS TO REMEMBER REGARDING PERMISSIONS 
sudo chmod 0444 /etc/mysql/my.cnf


## Additional Configuration (Not related to speed up) 
## REMOVE MYSQL Strict Mode. Add below line at my.cnf under [mysqld] 

sql-mode=

This configuration allowed me to import (40.5 GB) .sql dump in around 10 minutes.