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.

  • Max-P@lemmy.max-p.me
    0·
    11 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.