39
submitted 10 months ago* (last edited 10 months ago) by hypnotic_nerd@programming.dev to c/asklemmy@lemmy.ml

I have came across Percona xtrabackup but I am curious what is the best production deployment best practices and tools that are actually used by companies.

you are viewing a single comment's thread
view the rest of the comments
[-] Max_P@lemmy.max-p.me 19 points 10 months ago

Probably want the selfhosted or sysadmin community for that.

In my experience xtrabackup is a bit hacky and risky but it does work fairly well. I think MariaDB also comes with it, but you must use the exact matching MySQL server for it to work. So Percona Server 8 with the matching xtrabackup, or MariaDB and its xtrabackup. You also need the exact same server version as the backup was made on to be able to restore it correctly.

As for incrementals, it's up to you to track the LSN and then provide it back to xtrabackup when time comes to do the incremental. You also need to make sure the data structure is the same as when you did the original backup. If you have ran an alter table or whatever after the first backup, you have to make a full backup because the table have effectively been rewritten, and it will backup but not restore. It doesn't need the last backup to exist at all to backup, but that also means it knows nothing about the previous backup.

It also only deals with putting the files in place. If you're restoring a single database or a single table, you also need to manage deleting the database before restoring and also reloading the tablespace afterwards so the server rediscovers the new db/table that just popped into existence under its nose.

But if you can, taking a VM snapshot or filesystem snapshot is better if you can because usually the DB is able to recover from such a "crash".

The problem with backing up a live database is that without help from the filesystem (ie. snapshot), you're copying files as it's writing to them so it can end up corrupted. What xtrabackup does is essentially also track and replay what the live server is doing so that it can make a copy that's consistent up to the exact last transaction as if the server shut down cleanly. And that's why the version must match exactly, it runs some bits of actual MySQL code to make it work.

If you want something more robust, I'd also recommend taking a regular mysqldump once in a while, so that if you have backup problems, you have a more universal backup that will restore well on most MySQL versions and forks. Longer possible loss, but better than losing everything. Of course, test your backups. Untested backups is no backup.

I don't know if there's tools for this already. My use case required a custom tool to manage it and integrate it with other automation for restores and adding replicas and whatnot. It's really not that bad even as a simple bash script.

[-] hypnotic_nerd@programming.dev 5 points 10 months ago

Hey I can't tell you how important this response is to me. Thanks for helping out.

this post was submitted on 25 Dec 2023
39 points (95.3% liked)

Asklemmy

43716 readers
2028 users here now

A loosely moderated place to ask open-ended questions

Search asklemmy ๐Ÿ”

If your post meets the following criteria, it's welcome here!

  1. Open-ended question
  2. Not offensive: at this point, we do not have the bandwidth to moderate overtly political discussions. Assume best intent and be excellent to each other.
  3. Not regarding using or support for Lemmy: context, see the list of support communities and tools for finding communities below
  4. Not ad nauseam inducing: please make sure it is a question that would be new to most members
  5. An actual topic of discussion

Looking for support?

Looking for a community?

~Icon~ ~by~ ~@Double_A@discuss.tchncs.de~

founded 5 years ago
MODERATORS