Importing huge SQL dump into MySQL

If you are running a WAMP or a LAMP server chances are that at some point of time you might have wanted to restore a DB backup and you find that your PhpMyAdmin and your MySql times out frequently when you try to import a huge SQL file (sometimes even around 100MB of SQL dumps fail).

There are lots of workaround that could be tried I'll touch upon a few and then the magic one that worked for me (I restored a DB dump around 320MB in less than a minute!)

(1) MySQLAdministrator from MySQL is a very good tool but unfortunately chokes on huge DB restores

(2) Bigdump.php available at http://www.ozerov.de/bigdump.php promises to do a staggered import of very large MySQL DBs but honestly never did work for me thought!

(3) MySQLDumper available at http://www.mysqldumper.net/ is a PHP and Perl based tool

(4) Modify the settings in your PHP.ini file as

Find:
post_max_size = 8M
upload_max_filesize = 2M
max_execution_time = 30
max_input_time = 60
memory_limit = 8M

Change to:
post_max_size = 1024M
upload_max_filesize = 512M
max_execution_time = 6000
max_input_time = 6000
memory_limit = 2048M

Save your PHP.ini and restart your server for the settings to take effect. - This works sometimes for me

(5) And someone suggested this

[mysqld]
# Performance settings used for import.
delay_key_write=ALL
bulk_insert_buffer_size=256M

(6) The one that worked like a charm for me -

• Copy the SQL dump to the bin directory of your MySQL (this is just to save the trouble with keying in the right path to the SQL file!)
• Login to your MySql server
  # mysql -uroot -p
• Enter your password and get to the MySQL command prompt
  mysql> use [database] ; //replace [database] with your database name
• Import the SQL file mysql> source [filename].sql; //replace [filename] with the name of the SQL file you moved to the bin directory earlier