MariaDB Backup and Restore on OpenBSD 7.5

Logical backup with mariadb-dump (mysqldump)

# Single database
mariadb-dump -u root -p appdb > /backups/appdb_$(date +%F).sql

# All databases with binary log coordinates
mariadb-dump -u root -p --all-databases --source-data=2 \
    --single-transaction > /backups/all_$(date +%F).sql

Restore

mariadb -u root -p appdb < /backups/appdb_2026-05-07.sql

Physical backup with Mariabackup (included with MariaDB)

# Full backup
mariabackup --backup --target-dir=/backups/full_$(date +%F) \
    --user=root --password=RootPass!

# Prepare
mariabackup --prepare --target-dir=/backups/full_2026-05-07

# Restore (stop MariaDB first)
systemctl stop mariadb
mariabackup --copy-back --target-dir=/backups/full_2026-05-07
chown -R mysql:mysql /var/lib/mysql
systemctl start mariadb

Binary log backup for PITR

# /etc/mysql/conf.d/binlog.cnf
[mysqld]
log_bin        = /var/log/mysql/mariadb-bin
expire_logs_days = 7

Flush logs regularly:

mariadb -u root -p -e "FLUSH BINARY LOGS;"