Is a backup transactionally consistent?

Posted on November 29, 2018

Assume a server is busy processing transactions (e.g. MySQL on Ubuntu).

When the DO backup is taken is it guaranteed to maintain transactional (ACID) consistency within the database files?

In other words, if I recover from a backup will it represent a valid database (of some time ago) or will the block copy process sometimes update the log but not the buffer-cache spill or vice versa?

Happy to lose some data, I just need consistent data files for a historic point-in-time :-)

Lastly, is the same true for DO snapshots?

Thanks.

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

jarland

November 29, 2018

Hey friend,

This is a great question, thank you for asking it here so that others can see the answer. The easy answer is that you should never trust a live disk backup of a running database. The more complicated answer is “maybe.” This is a bit old, but it’s my favorite document for referencing a bit of how we do backups/snapshots:

https://kashyapc.fedorapeople.org/virt/lc-2012/snapshots-handout.html

Backups and snapshots are generally more consistent with the use of overlays, and it’s more likely that your MySQL database is fine. That said, it’s still far too likely that something like InnoDB corruption occurs on the backup image during a process like this (if the MySQL server is running). For this reason, I highly recommend that you regularly make use of mysqldump to make static backups of your database that you can restore. Keep a copy on your server, but also send a copy off somewhere else (Dropbox, S3, whatever).

While I trust our backups, it’s important to assume that they will fail and be useless. Two backup strategies is a good measure, three geographically diverse backups mean you’ve seen hard times and never want to again ;)

Hope that helps!

Jarland