Speed up bulk database importing

Importing database is an obvious task for every developer in their work cycle. When we are importing a bulk database into any other environments like local or cloud it will take minutes, hours or days based on the database size and the environment capacity.

Server Varibles

When we are importing the data into InnoDB there are few checks will happen by default which I would say not needed as we are just importing dump from production.

FOREIGN_KEY_CHECKS

If set to 1 (the default), foreign key constraints for InnoDB tables are checked. If set to 0, foreign key constraints are ignored, with a couple of exceptions.

MySQL

UNIQUE_CHECKS

If set to 1 (the default), uniqueness checks for secondary indexes in InnoDB tables are performed. If set to 0, storage engines are permitted to assume that duplicate keys are not present in input data.

Mysql

AUTOCOMMIT

If set to 1, all changes to a table take effect immediately. If set to 0, you must use COMMIT to accept a transaction or ROLLBACK to cancel it. If autocommit is 0 and you change it to 1, MySQL performs an automaticCOMMIT of any open transaction.

mysql

So how do we seed up the importing process?

So we can safely change these server variables when we start importing the DB and enable it once the dump is restored.

Run the below commands before you start the import.

SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;

and once the import is done, we should change these variable values to it’s older state.

SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;
SET AUTOCOMMIT = 1;
COMMIT;