Skip to main content

Automating DB Dump Daily

A daily database dump is one of the highest-value backups you can automate for WordPress. This guide provides a script pattern that is safe-by-default: restricted permissions, compression, verification, and retention.

Quick Summary
  • Write dumps to /backups/db/ with timestamped filenames.
  • Use flock to prevent overlapping runs.
  • Compress immediately and verify integrity.
  • Prune old dumps with a retention rule.

Create the backup directory​

create-db-backup-directory.sh
sudo mkdir -p /backups/db
sudo chmod 700 /backups/db

A daily dump script​

daily-db-dump-script.sh
#!/usr/bin/env bash
set -euo pipefail

umask 077

DB_NAME="wordpress"
BACKUP_DIR="/backups/db"
STAMP="$(date +%F)"
OUT="$BACKUP_DIR/${DB_NAME}-${STAMP}.sql.zst"
LOG="$BACKUP_DIR/${DB_NAME}-${STAMP}.log"

mkdir -p "$BACKUP_DIR"

{
  echo "[$(date -Is)] starting db dump"
  echo "db=$DB_NAME out=$OUT"

  mysqldump \
    --single-transaction \
    --quick \
    --routines --events --triggers \
    --column-statistics=0 \
    "$DB_NAME" \
    | zstd -3 -T0 -o "$OUT"

  echo "[$(date -Is)] verifying"
  zstd -t "$OUT"
  ls -lh "$OUT"

  echo "[$(date -Is)] done"
} | tee "$LOG"

# Retention: keep 14 days
find "$BACKUP_DIR" -type f -name "${DB_NAME}-*.sql.zst" -mtime +14 -delete
find "$BACKUP_DIR" -type f -name "${DB_NAME}-*.log" -mtime +14 -delete
warning

The find ... -delete lines permanently remove files. Test the script with a non-production directory first.

Install the script:

install-db-dump-script.sh
sudo install -m 700 /dev/stdin /usr/local/bin/daily-db-dump <<'EOF'
#!/usr/bin/env bash
set -euo pipefail

umask 077

DB_NAME="wordpress"
BACKUP_DIR="/backups/db"
STAMP="$(date +%F)"
OUT="$BACKUP_DIR/${DB_NAME}-${STAMP}.sql.zst"
LOG="$BACKUP_DIR/${DB_NAME}-${STAMP}.log"

mkdir -p "$BACKUP_DIR"

{
  echo "[$(date -Is)] starting db dump"
  echo "db=$DB_NAME out=$OUT"

  mysqldump \
    --single-transaction \
    --quick \
    --routines --events --triggers \
    --column-statistics=0 \
    "$DB_NAME" \
    | zstd -3 -T0 -o "$OUT"

  echo "[$(date -Is)] verifying"
  zstd -t "$OUT"
  ls -lh "$OUT"

  echo "[$(date -Is)] done"
} | tee "$LOG"

find "$BACKUP_DIR" -type f -name "${DB_NAME}-*.sql.zst" -mtime +14 -delete
find "$BACKUP_DIR" -type f -name "${DB_NAME}-*.log" -mtime +14 -delete
EOF

Schedule it with cron​

See the cron patterns in:

  • opt/docker-data/apps/docusaurus/site/docs/server/linux-server/10-backup-disaster-recovery/cron-jobs-for-backup.mdx

Next steps​

  • Encrypt dumps before offsite upload: opt/docker-data/apps/docusaurus/site/docs/server/linux-server/10-backup-disaster-recovery/encrypt-database-backups.mdx.