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?

1 Answer 1


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.
  • thanks for the input. that's some really good advice. good thing innodb_flush_log_at_trx_commit is by default on 1 . testing and restoring snapshots is definity a good idea.
    – Tom G
    Feb 5, 2012 at 5:51
  • Glad you found the feedback useful. As a new SE user, people generally express gratitude in the form of an upvote/check mark to an answer they deem sufficient, appropriate and over all correct to the question being asked ;)
    – atxdba
    Feb 5, 2012 at 6:16
  • yes agreed :) ..
    – Tom G
    Feb 5, 2012 at 6:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.