Mysql – Correct way to have a thesql/mariadb incremental backup

backupbinary-logdatabase-backupmariadbMySQL

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.

Best Answer

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.

Related Topic