MySQL Restores: waiting… waiting… waiting…

There are gazillions of posts with basic instructions for using mysqldump. But unless you are looking for it, very few of these comment on the shockingly slow restore times. However, there are some good hints and tips:

  1. http://loopj.com/2009/07/06/fast-mysql-backup-restore/
  2. http://vitobotta.com/smarter-faster-backups-restores-mysql-databases-with-mysqldump/

Interesting to note that in recent versions of mysqldump the –opt flag is enabled by default. This speeds up the restore process as detailed here: https://dev.mysql.com/doc/refman/5.5/en/mysqldump.html#option_mysqldump_opt. by taking out table locks, disabling keys and performing insert on chunks of rows (c.f. RBAR).  In addition to explicitly setting the –opt flag, I disable integrity check enforced by unique constraints and foreign keys, and also added the –no-autocommit flag.

I had been hoping for a superfast speedup. But unfortunately not. Perhaps this is because the –opt flag is set by default and therefore, there are not a lot of gains to be had. This is certainly an issue for production environment. And perhaps in these cases, forking out for the enterprise backup solution is the way to go. Percona’s Xtrabackup seems to be well received here (http://vitobotta.com/painless-hot-backups-mysql-live-databases-percona-xtrabackup/). But I shuddered at the point where it said “stop mysql” and then later on, “fix the permissions and start mysql”. Cringe.

Finally, I can no longer deny the plunge in the MySQL DBA world, useful scripts will be continually added here: https://github.com/nickb-/MySQLTips.

Advertisements

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

%d bloggers like this: