How to Automate Database Backups with Scripts

How to Automate Database Backups with Scripts

Regular database backups are critical for disaster recovery. Automating the process on your Breeze instance ensures consistent, reliable backups without relying on manual intervention.

MySQL/MariaDB Backup Script

Create a comprehensive backup script for MySQL or MariaDB:

#!/usr/bin/env bash
# /usr/local/bin/backup-mysql.sh
# Automated MySQL backup with rotation and compression

set -euo pipefail

# Configuration
DB_USER="backup_user"
DB_PASS="SecureBackupP@ss"
BACKUP_DIR="/var/backups/mysql"
RETENTION_DAYS=30
DATE=$(date +%Y-%m-%d_%H%M%S)
LOG_FILE="/var/log/mysql-backup.log"

# Create backup directory
mkdir -p "$BACKUP_DIR"

log() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $*" | tee -a "$LOG_FILE"
}

log "Starting MySQL backup..."

# Get list of databases (excluding system databases)
DATABASES=$(mysql -u"$DB_USER" -p"$DB_PASS" -Bse \
    "SELECT schema_name FROM information_schema.schemata \
     WHERE schema_name NOT IN ('information_schema','performance_schema','mysql','sys')")

# Backup each database separately
for db in $DATABASES; do
    BACKUP_FILE="${BACKUP_DIR}/${db}_${DATE}.sql.gz"
    log "Backing up database: $db"

    mysqldump -u"$DB_USER" -p"$DB_PASS" \
        --single-transaction \
        --routines \
        --triggers \
        --events \
        --add-drop-database \
        --databases "$db" | gzip > "$BACKUP_FILE"

    # Verify the backup
    if gzip -t "$BACKUP_FILE" 2>/dev/null; then
        SIZE=$(du -sh "$BACKUP_FILE" | cut -f1)
        log "  Success: $BACKUP_FILE ($SIZE)"
    else
        log "  ERROR: Backup verification failed for $db"
        rm -f "$BACKUP_FILE"
    fi
done

# Full dump of all databases
ALL_BACKUP="${BACKUP_DIR}/all_databases_${DATE}.sql.gz"
log "Creating full backup of all databases..."
mysqldump -u"$DB_USER" -p"$DB_PASS" \
    --all-databases \
    --single-transaction \
    --routines \
    --triggers \
    --events | gzip > "$ALL_BACKUP"

log "Full backup: $ALL_BACKUP ($(du -sh "$ALL_BACKUP" | cut -f1))"

PostgreSQL Backup Script

#!/usr/bin/env bash
# /usr/local/bin/backup-postgres.sh
set -euo pipefail

BACKUP_DIR="/var/backups/postgresql"
DATE=$(date +%Y-%m-%d_%H%M%S)
RETENTION_DAYS=30

mkdir -p "$BACKUP_DIR"

# Backup all databases using custom format (supports parallel restore)
DATABASES=$(psql -U postgres -t -c \
    "SELECT datname FROM pg_database WHERE datistemplate = false AND datname != 'postgres'")

for db in $DATABASES; do
    db=$(echo "$db" | xargs)  # trim whitespace
    BACKUP_FILE="${BACKUP_DIR}/${db}_${DATE}.dump"

    pg_dump -U postgres -Fc -f "$BACKUP_FILE" "$db"
    echo "Backed up: $db ($(du -sh "$BACKUP_FILE" | cut -f1))"
done

# Global objects (roles, tablespaces)
pg_dumpall -U postgres --globals-only > "${BACKUP_DIR}/globals_${DATE}.sql"

Backup Rotation

Add rotation logic to remove old backups and manage disk space:

# Remove backups older than retention period
log "Cleaning backups older than ${RETENTION_DAYS} days..."
DELETED=$(find "$BACKUP_DIR" -name "*.sql.gz" -o -name "*.dump" | \
    xargs -I {} find {} -mtime +"$RETENTION_DAYS" -delete -print | wc -l)
log "Removed $DELETED old backup files"

# Report disk usage
TOTAL_SIZE=$(du -sh "$BACKUP_DIR" | cut -f1)
BACKUP_COUNT=$(find "$BACKUP_DIR" -type f | wc -l)
log "Backup summary: $BACKUP_COUNT files, $TOTAL_SIZE total"

Offsite Backup with Rsync

Copy backups to a remote Breeze instance for disaster recovery:

# Sync to remote backup server
REMOTE_HOST="breeze-backup-01"
REMOTE_PATH="/var/backups/offsite/$(hostname)"

rsync -avz --delete \
    "$BACKUP_DIR/" \
    "backup@${REMOTE_HOST}:${REMOTE_PATH}/"

log "Offsite sync complete to ${REMOTE_HOST}"

Scheduling with Cron

# Add to crontab
# Daily backup at 2:00 AM with file locking
0 2 * * * /usr/bin/flock -n /tmp/mysql-backup.lock /usr/local/bin/backup-mysql.sh >> /var/log/mysql-backup.log 2>&1

# Weekly full backup on Sundays at 1:00 AM
0 1 * * 0 /usr/local/bin/backup-mysql.sh --full >> /var/log/mysql-backup.log 2>&1

Testing Restores

Backups are only useful if you can restore from them. Regularly test:

# Restore a MySQL backup
gunzip < /var/backups/mysql/mydb_2026-03-01_020000.sql.gz | mysql -u root -p

# Restore a PostgreSQL custom-format backup
pg_restore -U postgres -d mydb /var/backups/postgresql/mydb_2026-03-01_020000.dump

# Restore to a different database name (for testing)
createdb -U postgres mydb_test
pg_restore -U postgres -d mydb_test /var/backups/postgresql/mydb_2026-03-01_020000.dump

Best Practices

• Use --single-transaction — ensures consistent backups without locking tables
• Verify backups — check file integrity and periodically test restores
• Encrypt sensitive backups — use gpg or openssl to encrypt before storing offsite
• Monitor backup jobs — alert if a backup fails or the backup file is suspiciously small
• Store offsite — keep at least one copy on a separate Breeze instance or remote storage
• Document restore procedures — ensure your team knows how to restore from backups

Automated database backups with proper rotation and offsite copies give your Breeze infrastructure reliable disaster recovery capabilities.