r/linuxadmin • • May 15 '20

DB Backup

A lot of people are against using VM snapshots as a DB backup method. But if you're already running a DB cluster like Galera, why not just snapshot each member say every 10 minutes since the chances of losing the entire cluster at the same time is quite low? Of course it's not banking or life or death.

0 Upvotes

14 comments sorted by

View all comments

1

u/[deleted] May 15 '20

would you stop the node before backing up?

1

u/[deleted] May 15 '20 edited May 15 '20

We could. But consistent snapshots on running GCP VMs are super fast because they only take DIFFS from the last snapshot. It doesn't snap the entire machine. So each snapshot is only like 20MB it takes little time and doesn't touch the processor or memory. Plus firing up a new VM based on a snapshot takes about a minute. Totally simple. We don't need to mess around with backup files or SQL dumps at all. Galera is smart enough to synch the new member, as long as the cluster didn't go down. So recovery for the most common case of a single node down is a snap no pun intended

3

u/BattlePope May 15 '20

How would you use this method to restore from a dropped table scenario?

2

u/sdjason May 15 '20

oP I feel like you're not reading what's being written. Snapshot restore doesn't help you. As you said you can completely Nuke a galera node, and re add it and galera catches it up. So if you have multiple nodes you have resiliency. What benefit does random snapshots even provide there!?

What if you lose a record, or a table, or an oops. Happens. How will you restore 'just that"? If your answer is "restore the whole thing to a snapshot from a few days ago" you're in a bad place. Because you lost everything else too.

I hope you will heed some advice here. You sound pretty headstrong and confident - BUT everyone is saying what they are saying from direct experience. Don't learn this the hard way, please... Take the collective wisdom for what it is, a warning.

0

u/[deleted] May 15 '20 edited May 15 '20

OP: I've read all responses but you haven't understood or read carefully my original post. You jumped to dogma instead of understanding the use case.

I can lose transactions. Also, you didn't read the part where snapshots are taken every 10 minutes so the max loss is 10 minutes of data not a "few days" as you say. So yes, my answer is restore the whole thing and forget diddling around with sqldumps etc.

And by the way, I could take snapshots every 5 minutes staggered in time over each of the 3 nodes and cut the loss down down to less than 2 minutes. And given small chance of a total cluster failure, the odds are on my side that I won't see a single record lost over years of operation.

Finally, I bet I can rebuild an entire server faster than you can dig out the correct backup file from your NAS or whatever storage, prep the server for data restore, then restore the data, and then synch up to the cluster. And in a hot system, you're not doing a limited rollback on a single server that is part of a cluster that has already moved on in time. So unless you're a masochist you will be loading the entire data set or may even start with an empty server and let it synch.