1

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.

BostonMacOSX
  • 141
  • 5
  • I do pretty much that. I moved on from Borg backup to Restic as with small backups Borg puts the whole backup into a new file, rather than just adding new data in new files. That's not usually a problem, but given I was backing up to S3 it added to the bandwidth required. – Tim Oct 13 '19 at 18:12
  • @Tim the issus I'm having though is understanding what I should backup and when....how often I do a mysqldump and how to backup the binary log files etc etc. I understand the concepts but not the actual mechanics of it. – BostonMacOSX Oct 13 '19 at 18:17
  • 1
    I just do a mysqldump every night during a quiet period, but I have a rarely changing database. You should edit your question to add your RPO (recovery point objective) as that helps define how your backups should be done. If you for example can't lose more than 5 minutes of data then the backup of the bin logs should be done every 5 minutes. I'm not sure I'd run borg that often, I'd probably just mirror them, run a borg backup every night, and delete the binlogs each night. I'm not a DBA though, I'm a solution architect, so a DBA may have other advice. – Tim Oct 13 '19 at 18:41
  • I would suggest, don't take backup from primary server, do it from replica, you never know the future. In replica most of the solution work properly, however you have to keep eye if replica is synced correctly. – asktyagi Oct 14 '19 at 03:42
  • Curious about the 5 minute RPO - I assume the data in the database is really critical to require that short an RPO? Medical or financial systems would fall into that category, though they may require almost zero RPO. – Tim Oct 14 '19 at 20:17
  • @A.B looked at that. Looks like you'd have to run an incremental every minute to truly have a point in time restore. so youu'd lose everything bbetween the incremental and the time of crash. In addition you don't have a binary log stamp so you can't use them. I'm not sure why they'd do this backup if you can't use the binary logs – BostonMacOSX Oct 15 '19 at 01:21
  • Linux Academy has a [MySQL course](https://linuxacademy.com/cp/modules/view/id/374) that includes backups and recovery. They have a trial membership you could use to take the course. – Tim Oct 15 '19 at 02:17
  • @Tim I'm probably not going to have too many changes to the DB or critical changes but am trying not to have to go back too a client and say..oops – BostonMacOSX Oct 15 '19 at 11:49

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
  • 44,038
  • 6
  • 98
  • 162