Restore Mysqldump Backup Faster

On the off chance that you take general backups utilizing mysqldump, you’ll realize that moving down a vast database can regularly take a couple of minutes to finish. What the vast majority don’t think about while making these sort of backups, is that restoring your database from mysqldump backup files takes a whole lot longer.

Speed Up This Process

By setting two or flags banners previously you import your database dumps, you can significantly accelerate the reestablish procedure:

SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;

Above we have disabled auto-commit, at the end of the restore we will need to manually commit.

COMMIT;

We are restoring all database, so we can speed up this process by disabling unique checks and foreign key checks, also committing everything at the end of restore, rather than as the restore is in progress we get significant additional speed increases.

You can set above flags during restoring your database I would prefer to add these lines into the backup as it is created.

Using below bash script you can add above flags into your backup .sql file.

#!/bin/bash
echo "SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;" > alldb_backup.sql
mysqldump -u myuser --password=mypassword mydatabase >> alldb_backup.sql
echo "COMMIT;" >> alldb_backup.sql

Thanks:)