I recently had to import a 30GB MySQL database from a backup of a client’s production database. My development workstation really struggled with the hundred’s of thousands of INSERT queries, and the import either took an unacceptably long time or failed outright.
Fortunately, there is a sure-fire way to increase the import though (in my case a 100 fold increase in speed).
Simply open Terminal and type:
> mysql -uXXX -pXXX
…and replace the X’s with an appropriate username and password and then paste this at the MySQL prompt:
CREATE DATABASE my_database; USE my_database; set global net_buffer_length=1000000; set global max_allowed_packet=1000000000; SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0; SOURCE /some/path/database_file.sql; COMMIT; SET autocommit=1; SET unique_checks=1; SET foreign_key_checks=1;
A few notes: ‘set global net_buffer_length’ and ‘set global max_allowed_packet’ only apply if your source filename and path are over a network. Also simply omit the ‘CREATE DATABASE my_database;’ line if you already imported the blank schema, or the database already exists.