Slow Mysqldump Restores

Disabling AUTOCOMMIT, UNIQUE_CHECKS, and FOREIGN_KEY_CHECKS speeds up mysql restores dramatically.  My own tests of a 30MB dump were cut from 6 minutes to 11 seconds.  My 2GB backups can now be imported in a reasonable 40 minutes.

The following script will add one line to the top and bottom of your mysqldump file.  This can take a while for large files.  There’s no shortcut for prepending text, the entire file must be rewritten.

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s