Database Backup Script

Overview

Automated database backup script supporting MySQL/MariaDB and PostgreSQL. Features compression, rotation, and optional remote synchronization.

The Script

#!/bin/bash
# Database Backup Script
# Supports MySQL/MariaDB and PostgreSQL

set -euo pipefail

# Configuration
BACKUP_DIR="/backups/databases"
KEEP_DAYS=7
DATE=$(date +%Y-%m-%d_%H%M%S)

backup_mysql() {
    local db="$1"
    local backup_file="$BACKUP_DIR/mysql_\${db}_\${DATE}.sql.gz"

    echo "Backing up MySQL database: $db"
    mysqldump --single-transaction --routines --triggers "$db" | gzip > "$backup_file"

    local size=$(du -h "$backup_file" | cut -f1)
    echo "Created: $backup_file ($size)"
}

backup_postgres() {
    local db="$1"
    local backup_file="$BACKUP_DIR/postgres_\${db}_\${DATE}.sql.gz"

    echo "Backing up PostgreSQL database: $db"
    pg_dump "$db" | gzip > "$backup_file"

    local size=$(du -h "$backup_file" | cut -f1)
    echo "Created: $backup_file ($size)"
}

rotate_backups() {
    echo "Rotating old backups (keeping $KEEP_DAYS days)..."
    find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$KEEP_DAYS -delete
}

echo "Backup complete!"

Setup Credentials

MySQL (~/.my.cnf)

[client]
user=backup_user
password=secure_password

PostgreSQL (~/.pgpass)

localhost:5432:*:backup_user:secure_password

Cron Setup

# Daily MySQL backup at 3 AM
0 3 * * * /usr/local/bin/db-backup.sh mysql -a >> /var/log/db-backup.log 2>&1