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.
I’d be curious to know how much of an increase this made, and how much time it was taking before. Or if you have any additional advice on importing a large database dump.
I ran your code last night on a 30ish GB .sql dump file. It is still running this morning, clocking in at about 9 hours so far. I was hoping this would be my saving grace, as everything else has proven unreliable.
Mysql Workbench took 3.5 hours and had “1 error”. That 1 error was that it missed half the tables and some of the rows from the tables it did find.
I even tried NaviCat and it looked promising, until it got to 500 and every additional query it ran said it was encountering an error. I let it go for a couple hours anyway, came back, and clicked on the window. When I clicked on the application window, it froze seemingly indefinitely.
I was using a 2010 model iMac with 12GB RAM. I also used the command prompt of mysql – If you are using Windows, or a GUI then you are already handicapping yourself.
But from terminal, on my iMac: