Speed up a MySQL restore from a dump file

This is a small snippet I found to speed up importing MySQL dumps, it’s is almost as fast as coping the DB files directly.

Pre INSERT

Put the commands at the top of the dump file, e.g pre.sql,

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;

 Post INSERT

Put these statements at the end of the dump file, e.g post.sql,

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

Example

cat <(cat pre.sql) <(bzcat mydb_backup.sql.bz2) <(cat post.sql) | mysql