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.
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.
If set to 1 (the default), foreign key constraints forMySQL
InnoDBtables are checked. If set to 0, foreign key constraints are ignored, with a couple of exceptions.
If set to 1 (the default), uniqueness checks for secondary indexes inMysql
InnoDBtables are performed. If set to 0, storage engines are permitted to assume that duplicate keys are not present in input data.
If set to 1, all changes to a table take effect immediately. If set to 0, you must usemysql
COMMITto accept a transaction or
ROLLBACKto cancel it. If
autocommitis 0 and you change it to 1, MySQL performs an automatic
COMMITof any open transaction.
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;