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 [email protected]_FOREIGN_KEY_CHECKS; SET UNIQ[email protected]_UNIQUE_CHECKS; SET AUTOCOMMIT = @OLD_AUTOCOMMIT;
Example
cat <(cat pre.sql) <(bzcat mydb_backup.sql.bz2) <(cat post.sql) | mysql