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.

Previously I have mentioned an awesome little command-line tool called ‘pv’. Recently, I was trying to restore a clients of legacy database on my development machine, which was a staggering 30GB .sql file. I was having quite a few problems trying to wrestle this beast, the worst of which being that I had no idea how much longer the import would take, or if it had locked up. Luckily, PV comes to the rescue.

Normally, when importing a MySQL dump file, you can just type:

mysql -uxxx -pxxx dbname < /sqlfile.sql

…to import directly from the file. However, you can pv the file and pipe it into the mysql executable like:

pv sqlfile.sql | mysql -uxxx -pxxxx dbname

And you will get an awesome progress bar about how completed the task is. You can also use it in the reverse. You’d be surprised how useful it can be.