Netzgemeinde.eu

Backup (en)

3 min read

File System

I’m using btrbk for two different, but interconnected, purposes:

  1. Snapshots. Every day a snapshot of the file system (btrfs) will be created. This is useful to correct mistakes: Just have a look at a previous snapshot and restore your configuration1. Occasionally I create a snapshot manually in case of risky updates/work. The standard snapshot retention policy: snapshot_preserve_min 31d. However, 31 days will never be reached:
  2. Backups. I’m backing up the snapshots to my home, which is easily done because of the differential/incremental kind of btrfs’s snapshots. This btrbk process uses a different retention policy: snapshot_preserve_min 15d. So the 31 days mentioned above are just a safety measure if my internet at home should break down, the backup server has a problem and so on. If everything works well, the snapshots are only kept for 15 days.

MariaDB

Using a file system backup - even using a snapshot - of MySQL ist a very bad idea, there’s the danger of having a inconsistent state in your backup which means that your backups are worthless.

There are tools of backing up a MariaDB, either mariadb-dump or mariabackup. Both have their pro and con, I’m using mariabackup:

mariadb-dump

Pros

  • It’s possible to dump/backup only a subset of databases/tables
  • It’s rather fast.
  • It’s the only way to shrink ibdata1, since optimize won’t affect this.
  • No need for binlogs.

Cons

  • The Restore of a database can take a very long time: Since the indexes have to be recreated and it’s only single-threaded, it may take hours2 or more.

mariabackup

Pros

  • It’s also quite fast
  • It’s very easy to setup a secondary using this, there’s no replication lag after it has completed it’s job.
  • It’s a matter of minutes(!) of restoring the backup, either to the original server or to a new one.

Cons

  • It’s a kind of file system backup, albeit a safe one. so it’s only possible to backup every database.
  • You have to enable binlogs, even if you don’t have a secondary.
  • There might be problems when restoring a backup on a different platform, for example: Intel to ARM64.

My backup script

/usr/local/sbin/mariadb-backup.sh 

#!/bin/bash
BACKUPDIR=/var/backup/mariadb/mariadb-backup/
/usr/bin/mariadb-backup --defaults-extra-file=/root/.my.cnf --backup --stream=xbstream | zstd -T5 > $BACKUPDIR/mariadb-backup-$(date -Iminutes).xbstream.zst
find $BACKUPDIR -type f  -mtime +1 -delete

This scripts runs every day. It uses parallel zstd for compression - in this case 5 parallel processes3. I used to use bzip2, but zstd has got a couple of advantages:

  • It takes less than 40 minutes to backup the 150 GB databases. bzip2 took literally hours.
  • Even if it’s much faster than bzip2, it’s more efficient - the file sizes are smaller than bzip2’s result. They are about 11 GB, which is not bad indeed for a database of roughly 150 GB size.

The backups are stored in the main file system. This means there will be (btrfs) snapshots and backups. The find deletes backups older than 24h hours. However, due to the varying time of the backups, it’s possible that there are two or three backups in each snapshot. Well, redundancy isn’t a bad thing in backups.

Footnotes

  1. In fact, it’s easy to set / to a previous snapshot if push comes to the shove. ↩

  2. In fact, it took nealry 4 hours to import my dump when moving the database to another server ↩

  3. Half of the cores ↩

Graphansicht

Backlinks