Accelerating MySQL file imports

March 2016

Just a quick note on how to speed up the slow process of importing SQL files on Linux Systems. I wanted to import a 50 MiB MySQL dump (and monitor it using pv), which just took way too long.

pv database.sql | mysql -u user -p  database

Enter password: 
 148KiB 0:00:13 [13.8KiB/s] [>         ]  0% ETA 1:13:21
 160KiB 0:00:14 [13.8KiB/s] [>         ]  0% ETA 1:13:03
 228KiB 0:00:19 [12.5KiB/s] [>         ]  0% ETA 1:09:29

I’m importing a 50 MiB file on a SSD here, so that can’t be right. Did I mention pv is great?

Anyway, the solution is to disable autocommit mode, which performs a log flush to disk for every insert. More information here.
Just open the SQL file with any text editor and add these statements to the very top and very bottom of the file (in nano, use CTRL + w + v to jump to the bottom):

SET autocommit=0;

[content]

COMMIT;

That’s it. SQL imports should now be finished in no time.

49.1MiB 0:00:52 [ 960KiB/s] [=========>] 100%