MySQL Backup and Restore on OpenBSD 7.5

Logical backup with mysqldump

Single database

mysqldump -u root -p appdb > /backups/appdb_$(date +%F).sql

All databases

mysqldump -u root -p --all-databases > /backups/all_$(date +%F).sql

Compressed

mysqldump -u root -p appdb | gzip > /backups/appdb_$(date +%F).sql.gz

Restore

mysql -u root -p appdb < /backups/appdb_2026-05-07.sql
# or compressed:
gunzip < /backups/appdb_2026-05-07.sql.gz | mysql -u root -p appdb

Physical backup with XtraBackup (Percona)

# Full backup
xtrabackup --backup --target-dir=/backups/full_$(date +%F)

# Prepare (apply logs)
xtrabackup --prepare --target-dir=/backups/full_2026-05-07

# Restore (MySQL must be stopped)
systemctl stop mysql
rsync -avrP /backups/full_2026-05-07/ /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

Automated daily script

#!/bin/bash
BACKUP_DIR="/backups/mysql"
RETENTION=7
mkdir -p "$BACKUP_DIR"
mysqldump -u root -p"$MYSQL_ROOT_PASS" --all-databases \
    | gzip > "$BACKUP_DIR/all_$(date +%F_%H%M).sql.gz"
find "$BACKUP_DIR" -name '*.sql.gz' -mtime +$RETENTION -delete

Store the password in /root/.my.cnf:

[client]
user     = root
password = YourRootPassword

chmod 600 /root/.my.cnf