Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

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.
share|improve this answer
    
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 '12 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 '12 at 6:16
    
yes agreed :) .. –  Tom G Feb 5 '12 at 6:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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