#!/usr/bin/env bash
# mysql-backup-all.sh, dump every MySQL database to a timestamped, gzipped file,
# then prune anything older than KEEP_DAYS.
# Source: https://techearl.com/export-or-backup-all-mysql-databases
# Site:   https://techearl.com/
set -euo pipefail

BACKUP_DIR="/var/backups/mysql"
TIMESTAMP=$(date +%Y%m%d-%H%M%S)
KEEP_DAYS=14

mkdir -p "$BACKUP_DIR"

# Dump + compress in one pipe
mysqldump \
  --defaults-file=/root/.my.cnf \
  --single-transaction \
  --routines \
  --events \
  --triggers \
  --hex-blob \
  --quick \
  --all-databases \
  | zstd -T0 > "$BACKUP_DIR/mysql-$TIMESTAMP.sql.zst"

# Prune backups older than KEEP_DAYS
find "$BACKUP_DIR" -name "mysql-*.sql.zst" -mtime "+$KEEP_DAYS" -delete

echo "$(date -Iseconds) Backup complete: $BACKUP_DIR/mysql-$TIMESTAMP.sql.zst"