Skip to main content

Faster MySQL Imports

This blog post might be outdated!
This blog post was published more than one year ago and might be outdated!
· One min read
Stephan Hochdörfer
Stephan Hochdörfer
Head of IT Business Operations

Importing larger database dumps can take a while, especially when foreign key checks are enabled. This slowdown happens because each foreign key reference must be verified by the database server.

Disabling foreign key checks is easy. Add the following line to your SQL dump and you are good to go:

SET SESSION FOREIGN_KEY_CHECKS=0;

However, I was wondering if I really need to modify the SQL dump - as this could potentially corrupt data - and I came across this neat alternative. The MySQL console client has an option --init-command which allows you to set an initial command that gets sent to the MySQL server before the data to import gets sent. Instead of adding the setting to ignore the foreign key checks in the SQL dump, we can pass it as init command:

mysql -h 127.0.0.1 -P 3306 -u root -p my_database --default-character-set=utf8 --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;" < dump.sql