Speed up mysql innodb restore

Submitted by Jeff on Wed, 16/02/2022 - 10:13

When restoring a large innodb mysqldump file the process takes hours.

One can put put these commands at the top of the dump file:

SET @OLD_AUTOCOMMIT=@@AUTOCOMMIT, AUTOCOMMIT = 0;
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS = 0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS = 0;

And these at the end of the dump file:

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET AUTOCOMMIT = @OLD_AUTOCOMMIT;
COMMIT;

OR


mysql --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;SET UNIQUE_CHECKS=0;" Database < Database_mysqldump.mysql

 

https://serverfault.com/questions/146525/how-can-i-speed-up-a-mysql-restore-from-a-dump-file

 

Category