2

I've read for a few hours on this topic but don't have a complete grasp. I'm going to be using borg backup to a network share.

Looking to have pretty frequent backups of the binlog....every 5 minutes or so..

here are my takes so far:

  1. start mariadb with --log-bin to enable binary logging. --not sure where to put the binary log files or how to back them up. Do I have to stop everything to "backup" the binary log files?

  2. mysqldump with flush logs and some other settings too get the point in time of the bin logs into the mysql dump file.

I guess I'm looking for just the generals of making sure I'm backing up my Maria/Mysql Databases correctly.

-R

What I'm thinking...mariabackup with full and incremental....but I'm not sure how to correcly use the binary logs to fill in the last part of the equation to truly be able to restore the database to its failure point.

BostonAreaHuman
  • 150

2 Answers2

1

Don't do mysqldump, it's inefficient and slow. Use xtrabackup, it can run with almost no downtime whereas mysqldump will need to lock all tables for writes to get a consistent copy.

And a good option is to have a slave that replicates the binlogs, that way your binlogs are saved immediately. In newer MariaDB versions you can let the slave trail behind, that way it will fetch the binlogs but not execute them until after a certain time has passed.

Stefan S
  • 33
  • 4
1

Backup via binlog is the most obvious plan; however, restoring a very long chain of binlog would be quite tedious.

For such short RPO/RTO I would suggest a block level backup, namely in the form of zfs send/recv: simply run your MariaDB on a ZFS dataset and use send/recv to replicate on another ZFS machine. Even better, you can use sanoid to automate things.

If you want to avoid non-mainline modules, another approach exists: you can use DRBD on top of a thin lvm volume to have a synchronous replica to another machine, snapshotting every 5 minutes your thin volumes (on source, destination or both) via snapper

shodanshok
  • 52,255