3

I am wondering if I can trust my host doing it's nightly snapshots with live Innodb tables. From what I have read: "Innodb is ACID-compliant, so you can "safely" use fs snapshot with Innodb tables."

Is this true? Can you safely snapshot Mysql instances with large Innodb tables and have no ill effects? Is this documented anywhere?

Tom G
  • 151
  • 4

1 Answers1

2

Snapshot are a common practice for backups. See http://www.mysqlperformanceblog.com/2009/02/05/disaster-lvm-performance-in-snapshot-mode/

For this to work safely though you'll want to ensure innodb_flush_log_at_trx_commit is set to 1.

That being said, the general practice with backups is test restoring them. You want to be sure your backup method is working functionally and you want to be sure you know the steps to restore them. If you're having to restore a backup it likely means your in a situation you're loosing money while it's down. You should be able to restore a backup while being groggy after getting a 3am wake up call.

If you have a dev environment you might setup a weekly script to restore the backup into your dev environment.

  • You now have a push button script to restore in a pinch; just point it to production instead of dev
  • Your dev env. now has a production style dataset to work against. (You may want to have a secondary scrub script to anonymize customer, financial or other sensitive information).
  • You get automatic weekly tests of your restore process. If there's a problem you can be alerted of it and fix it before it's needed for real.
atxdba
  • 5,293
  • 5
  • 41
  • 62