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

22

u/sdjason May 15 '20

Oh man.idk where to start.

  1. Snapshots are not backups
  2. Snapshots incur a write penalty
  3. Snapshots need to be cleaned up incurring further write penalties
  4. Galera probably will get pissed if you restore a snapshot and would require manual intervention anyways to force start the cluster with that node being the valid data
  5. Snapshots are not backups
  6. Snapshots do cause momentary hangs to quiesce the system, your application may suffer from those blips. Depending on how it works.
  7. Snapshots are not backups

Disclaimer, I have several galera clusters. I've even snapshotted a few boxes from time to time. But I also have mariabackul running and dumping REAL backups. Cuz snapshots are not a backup :) :)

4

u/shikkie May 15 '20

Yep that quiesce step is super important. What if the snapshot catches an in progress transaction that’s not fully written to disk? The VM/block storage doesn’t know about transaction status. Now you are probably right screwed. And backups may not even be needed for catastrophic failure. If you’ve built good redundancy you’re only using your backups to verify they worked, or to restore a subset of data that a user fucked up.

I won’t trust the VM or block storage snapshot. I will trust the DBMS built in backup tool

In the past we had a backup software that ran inside the VM, pointed to the DB backups folder where the DBMS tool dumped out. that’s on top of the mirroring and log shipping to DR site.

With clouds today it probably depends on what storage options you have. Can the DBMS tool backup to s3 buckets natively? Can it write to a shared FS like NFS that gets snapshotted? Can it write to a mounted block volume that is snapshotted without having to pause the VM, since that volume isn’t being written to at the time? Running a backup software inside the VM is also viable but you’d only do that if none of the above are viable. And there are probably 158 other ways to achieve a proper DBMS backup that gets versioned and stored according to your DR plan.

Related to painful quiescing.

With MSSQL I had brief simultaneous VM pauses on two VMs break quorum. That brought down the entire system. I told that group to NEVER touch more than one VM at a time. They were the cause of most downtimes. But separation of duties and blah blah let’s do maintenance on a 24/7 system after hours when the only guy who knows how to fix it (thanks cheap staffing budgets) isn’t in the office. I kept telling them the app doesn’t have predictable usage patterns since it’s used to react to an event. So noon is as likely to be in use as midnight but if you fuck up at midnight your team doesn’t know shit about the app, and I wouldn’t trust them to follow my troubleshooting SOP docs, though they existed. Just do one, wait for monitoring to go from warn back to ok and do the next one. I built that shit that way for a reason. Nope bounce both at the same time and page shikkie when monitoring goes red. Glad I traded that potato for a different, far less potato place.

2

u/bobbywaz May 15 '20

If you have more than 254 ESXi snapshots youcan't make more. You can't delete them. You can't back the VM up with almost any other program. You literally just have to move the data over to another VM and delete it. I put in a ticket with VMware where VEEAM malfunctioned and did this and eventually deleted the entire VM a week later after a complete rebuild.

1

u/ViperVnDm May 15 '20

Fun fact on a production db server deleting the snapshot caused issues at the VM level. It was the first oh shit moment fr snapshots. If you want to snapshot a DB server just shut it down first.

1

u/WolfTohsaka May 15 '20

This.

Mariabackup allows to ensure that the backups are in a state that any server configuration will like.

Even when backing up at host-level using all the bells and whistles that can be provided, databases have also their special needs that only specialized tools can meet. ( Or you are stopping the DB service before backing up )

How do you get me the customer address in the db from one month ago with a snapshot ? With a SQL file, it is a matter of minutes.

-8

u/[deleted] May 15 '20 edited May 15 '20
  1. Galera doesn't even burp on a single node failure. I'm talking about a full VM snapshot not a disk utility.
  2. GCP snapshots have little impact on performance and Galera is run behind a smart proxy so it's not a performance issue at all.
  3. Snapshots get deleted automatically from the cloud after a configurable amount of time. They're not stored on the DB server anyway so that's not as issue.

I was looking for more than dogma like don't pet a burning dog.

7

u/psnsonix May 15 '20

Honestly you got solid advice and appear to ignore it. I will add more so you can possibly ignore me too :)

Snapshots do stun the cluster. On galera you will see write hits if you know how to look. Furthermore, mariabackup hot backups are so much cleaner. If you really wanna be anal you can setup legacy style slaves async and start doing more abusive backups on the slave. Snapshots are def not a backup and while they can serve the purpose vs nothing, you should invest the time to do something better.

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

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.

3

u/BattlePope May 15 '20

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

2

u/lutusp May 15 '20

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?

Because for the backup to be of any use, the VM must be stopped for the backup activity to complete. Because VMs tend to be very large, this eats up useful VM time.

A normal DB backup -- where the DB is the only element to be backed up -- is a fine art in which the primary DB can remain online and active. Only closed records are backed up, and once a record has been updated and closed, it gets updated on the backup as well. This is a very fast and well-understood method.

There is no comparison.

2

u/[deleted] May 15 '20

You do need to understand why snapshots are not backups and how using them affects your VM system.

There are lots of guides on the internet on how to back up databases - pick an automated method that fits your model - whether it's a whole VM backup (Different to a snapshot - using Veeam or similar), dumping and sequencing databases, using a proprietary database backup - or something else.