WikiOasis MariaDB Backup System: Scripts and Schedule

The WikiOasis database backup system combines three complementary strategies: weekly full backups, daily incremental backups, and continuous binary log streaming. Together they provide a recovery point objective (RPO) of approximately five minutes — the interval at which binlogs are synced to the remote destination. The entire system is deployed and configured by the mariadb.backup Salt state and is driven entirely by pillar data, so no manual script editing is ever required.

Architecture overview

The backup system is built around mariabackup (the MariaDB fork of Percona XtraBackup) streaming directly over SSH to a remote destination host. No backup data is written to the local disk for full or incremental backups — only two small state files are kept locally to track LSN progress and timestamps. Binary logs are an exception: they are buffered locally in /var/backups/mariadb/binlogs/ and rsynced to the remote host every five minutes.

db-*-us-east-NNN
├── mariabackup --stream=xbstream  ──SSH──►  remote-host:/backup/mariadb/<hostname>/base/
│   (weekly full, Sunday 01:00)
├── mariabackup --incremental      ──SSH──►  remote-host:/backup/mariadb/<hostname>/incremental/<date>/
│   (daily Mon–Sat 02:00)
├── mariadb-binlog-stream.service            /var/backups/mariadb/binlogs/
│   (continuous, mysqlbinlog --read-from-remote-server)
└── mariadb-binlog-sync.sh         ──rsync/SSH──►  remote-host:/backup/mariadb/<hostname>/binlogs/
    (cron, every 5 minutes)

The entire mariadb.backup state is conditionally applied — it only runs if mariadb:backup:destination:host is set in pillar. If the key is absent, no backup packages are installed and no cron jobs are created.

Pillar configuration

All backup parameters are nested under the mariadb:backup pillar key. For a database server to receive backup configuration, its pillar file (e.g. pillar/mariadb/db-c2-us-east-031.sls) must include the following keys alongside the standard MariaDB tuning values.

mariadb:
  # ... standard MariaDB tuning (from pillar/mariadb/init.sls defaults) ...
  backup:
    user: mariadb_backup          # MariaDB user created for mariabackup (default: mariadb_backup)
    password: "s3cr3t"            # Password for the backup user (stored in private pillar)
    ssh_private_key: |            # SSH private key written to /etc/mariadb-backup/ssh_key
      -----BEGIN OPENSSH PRIVATE KEY-----
      ...
      -----END OPENSSH PRIVATE KEY-----
    destination:
      host: backup.wikioasis.org  # Remote SSH hostname — REQUIRED (enables the entire backup system)
      user: backup                # Remote SSH username
      path: /backup/mariadb       # Base path on the remote host (default: /backup/mariadb)

Pillar key	Default	Description
`mariadb:backup:user`	`mariadb_backup`	MariaDB user granted `RELOAD`, `LOCK TABLES`, `PROCESS`, `REPLICATION CLIENT`, `REPLICATION SLAVE`
`mariadb:backup:password`	(none)	Password for the backup user — store in private pillar
`mariadb:backup:ssh_private_key`	(none)	SSH private key written to `/etc/mariadb-backup/ssh_key` (mode `0600`)
`mariadb:backup:destination:host`	(none)	Required. Remote SSH host. If absent, the backup state does nothing.
`mariadb:backup:destination:user`	(none)	Remote SSH username for rsync and streaming connections
`mariadb:backup:destination:path`	`/backup/mariadb`	Base directory on the remote host; sub-directories per hostname are created automatically

The backup user password and SSH private key are secrets. They must live in the private pillar (which is git-ignored and never committed to the main repository), not in pillar/mariadb/.

Backup schedule

Job	Schedule	Command	Log
Full backup	Sunday at 01:00	`mariadb-backup-run.sh full`	`/var/log/mariadb-backup.log`
Incremental backup	Mon–Sat at 02:00	`mariadb-backup-run.sh incremental`	`/var/log/mariadb-backup.log`
Binlog sync	Every 5 minutes	`mariadb-binlog-sync.sh`	`/var/log/mariadb-backup.log`
Binlog streaming	Continuous (systemd)	`mariadb-binlog-stream.service`	`journalctl -u mariadb-binlog-stream`

Backup scripts

mariadb-backup-run.sh

The main backup script deployed to /usr/local/bin/mariadb-backup-run.sh. It accepts a single argument — full or incremental — and streams the backup directly to the remote destination via SSH using mbstream. Full backup behaviour:

Rotates the previous base backup on the remote (base → base.prev) before writing a new one.
Writes LSN state to /var/backups/mariadb/xtrabackup_checkpoints for subsequent incrementals.
Updates /var/backups/mariadb/last_full_backup and /var/backups/mariadb/last_backup with the current epoch.

Incremental backup behaviour:

Reads the to_lsn from /var/backups/mariadb/xtrabackup_checkpoints and passes it as --incremental-lsn.
If the checkpoints file is missing, falls back to a full backup automatically.
If the full backup is more than 7 days old (i.e. the weekly cron was missed), falls back to a full backup automatically.
If the incremental fails due to LSN incompatibility, falls back to a full backup automatically.
Saves each incremental to <remote_path>/<host>/incremental/<YYYYMMDD-HHMMSS>/.

Both modes send a Discord or Slack webhook notification on success and failure (if notifications:discord_webhook_url or notifications:slack_webhook_url is set in pillar).

# Run a full backup manually
/usr/local/bin/mariadb-backup-run.sh full

# Run an incremental backup manually
/usr/local/bin/mariadb-backup-run.sh incremental

mariadb-binlog-stream.sh

Deployed to /usr/local/bin/mariadb-binlog-stream.sh and run as a systemd service (mariadb-binlog-stream.service) with Restart=always. It connects to the local MariaDB instance via the Unix socket and streams binary logs using mariadb-binlog --read-from-remote-server --stop-never --raw, writing files to /var/backups/mariadb/binlogs/. On startup the script looks for the most recently written binlog file in the staging directory and resumes from that file. If no local files exist yet, it queries SHOW MASTER STATUS to find the current binlog filename and starts streaming from there.

# Check streaming service status
systemctl status mariadb-binlog-stream

# View streaming logs
journalctl -u mariadb-binlog-stream -f

mariadb-binlog-sync.sh

Deployed to /usr/local/bin/mariadb-binlog-sync.sh and run every 5 minutes by cron. It rsyncs the contents of /var/backups/mariadb/binlogs/ to <remote_user>@<remote_host>:<remote_path>/<hostname>/binlogs/ using the SSH key at /etc/mariadb-backup/ssh_key.

# Run a manual binlog sync
/usr/local/bin/mariadb-binlog-sync.sh

Local paths

Path	Purpose
`/var/backups/mariadb/`	Root backup state directory
`/var/backups/mariadb/binlogs/`	Local binlog staging area (rsynced every 5 min)
`/var/backups/mariadb/xtrabackup_checkpoints`	LSN state file for incremental backups
`/var/backups/mariadb/last_full_backup`	Epoch of last successful full backup
`/var/backups/mariadb/last_backup`	Epoch of last successful backup of any type
`/etc/mariadb-backup/`	Backup configuration directory (mode `0750`)
`/etc/mariadb-backup/ssh_key`	SSH private key for remote destination (mode `0600`)
`/var/log/mariadb-backup.log`	Cron job output log

Monitoring and NRPE checks

The mariadb.nrpe_backup state installs check_mariadb_backup.sh as an NRPE plugin. It exposes three independent check modes:

Mode	Warning threshold	Critical threshold	What is checked
`base`	> 8 days (192 h)	> 9 days (216 h)	Age of `last_full_backup`
`incremental`	> 26 hours	> 28 hours	Age of `last_backup`
`binlog`	newest file > 15 min old	`mariadb-binlog-stream` not active, or newest file > 30 min old	Streaming service health

# Run NRPE checks manually on the database server
/usr/lib/nagios/plugins/check_mariadb_backup.sh base
/usr/lib/nagios/plugins/check_mariadb_backup.sh incremental
/usr/lib/nagios/plugins/check_mariadb_backup.sh binlog

Applying the backup state

# Apply backup state to all database servers
salt 'db*' state.apply mariadb.backup

# Apply to a specific host
salt 'db-c2-us-east-031' state.apply mariadb.backup

# Dry run first
salt 'db-c2-us-east-031' state.apply mariadb.backup test=True

Applying this state will:

Install mariadb-backup and jq packages.
Create /etc/mariadb-backup/ and write the SSH key from pillar.
Create /var/backups/mariadb/ and /var/backups/mariadb/binlogs/.
Deploy the three backup scripts.
Deploy and enable mariadb-binlog-stream.service.
Create the backup MariaDB user with the required grants.
Install the three cron jobs (weekly full, daily incremental, 5-minute binlog sync).

Verifying backups

Check the backup log

tail -50 /var/log/mariadb-backup.log

A successful run looks like:

[2025-01-12 02:00:03] Starting streamed incremental backup (from LSN 12345678)
[2025-01-12 02:04:11] Incremental backup complete — size: 1.2G, duration: 248s

Check the NRPE backup plugin

/usr/lib/nagios/plugins/check_mariadb_backup.sh base
# OK: full backup 18h old | age_hours=18

/usr/lib/nagios/plugins/check_mariadb_backup.sh incremental
# OK: last backup 6h old | age_hours=6

/usr/lib/nagios/plugins/check_mariadb_backup.sh binlog
# OK: binlog stream active, newest file 2min old | age_minutes=2

Verify binlog streaming service

systemctl is-active mariadb-binlog-stream
# active

ls -lh /var/backups/mariadb/binlogs/
# Binlog files should be present and recently modified

Confirm remote destination is reachable

The backup scripts use StrictHostKeyChecking=no and BatchMode=yes. Test connectivity manually with the configured key:

ssh -i /etc/mariadb-backup/ssh_key \
    -o StrictHostKeyChecking=no \
    -o BatchMode=yes \
    backup@backup.wikioasis.org "echo connected"

Adding a Server

Runbook for provisioning a new database server and applying the MariaDB and backup states.

Maintenance Runbook

How to schedule downtime, depool a database server, and apply configuration changes safely.

Deployment

Salt Modules

Runbooks

WikiOasis MariaDB Backup System: Scripts and Schedule

Architecture overview

Pillar configuration

Backup schedule

Backup scripts

mariadb-backup-run.sh

mariadb-binlog-stream.sh

mariadb-binlog-sync.sh

Local paths

Monitoring and NRPE checks

Applying the backup state

Verifying backups

Adding a Server

Maintenance Runbook

Build docs developers (and LLMs) love

Deployment

Salt Modules

Runbooks

Documentation Index

​Architecture overview

​Pillar configuration

​Backup schedule

​Backup scripts

​mariadb-backup-run.sh

​mariadb-binlog-stream.sh

​mariadb-binlog-sync.sh

​Local paths

​Monitoring and NRPE checks

​Applying the backup state

​Verifying backups

Adding a Server

Maintenance Runbook

Build docs developers (and LLMs) love

Architecture overview

Pillar configuration

Backup schedule

Backup scripts

mariadb-backup-run.sh

mariadb-binlog-stream.sh

mariadb-binlog-sync.sh

Local paths

Monitoring and NRPE checks

Applying the backup state

Verifying backups