Set up automated MySQL database backups with compression and rotation

Create MySQL backup configuration file

Store MySQL credentials securely in a configuration file with restricted permissions. This prevents passwords from appearing in process lists or command history.

[client]
user=backup_user
password=SecureBackupPass123!
host=localhost
port=3306

[mysql]
default-character-set=utf8mb4

[mysqldump]
single-transaction=true
routines=true
triggers=true
events=true
opt=true

sudo chown mysql-backup:mysql-backup /etc/mysql/backup.cnf
sudo chmod 600 /etc/mysql/backup.cnf

Create comprehensive backup script

Create the main backup script that handles database dumps, compression, encryption, and rotation. This script includes error handling and logging for reliable operation.

#!/bin/bash

MySQL Backup Script with Compression and Rotation
Author: Infrastructure Team
Version: 2.1

set -euo pipefail

Configuration
BACKUP_DIR="/var/backups/mysql"
LOG_FILE="/var/log/mysql-backup/backup.log"
MYSQL_CONFIG="/etc/mysql/backup.cnf"
RETENTION_DAYS=30
COMPRESSION_LEVEL=6
GPG_RECIPIENT="backup@example.com"
ALERT_EMAIL="admin@example.com"
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
HOSTNAME=$(hostname)

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

Error handling
error_exit() {
    log "ERROR: $1"
    echo "MySQL backup failed on $HOSTNAME at $(date)" | mail -s "MySQL Backup Failed - $HOSTNAME" "$ALERT_EMAIL" 2>/dev/null || true
    exit 1
}

Check prerequisites
check_prerequisites() {
    log "Checking prerequisites..."
    
    # Check if MySQL is accessible
    if ! mysql --defaults-file="$MYSQL_CONFIG" -e "SELECT 1" >/dev/null 2>&1; then
        error_exit "Cannot connect to MySQL server"
    fi
    
    # Check backup directory
    if [[ ! -d "$BACKUP_DIR" ]]; then
        mkdir -p "$BACKUP_DIR" || error_exit "Cannot create backup directory"
    fi
    
    # Check disk space (require at least 2GB free)
    AVAILABLE_SPACE=$(df "$BACKUP_DIR" | awk 'NR==2 {print $4}')
    if [[ $AVAILABLE_SPACE -lt 2097152 ]]; then
        error_exit "Insufficient disk space. Available: ${AVAILABLE_SPACE}KB, Required: 2GB"
    fi
    
    log "Prerequisites check completed successfully"
}

Get list of databases
get_databases() {
    mysql --defaults-file="$MYSQL_CONFIG" -e "SHOW DATABASES;" | grep -Ev '^(Database|information_schema|performance_schema|mysql|sys)$'
}

Backup individual database
backup_database() {
    local db_name=$1
    local backup_file="${BACKUP_DIR}/${HOSTNAME}_${db_name}_${TIMESTAMP}.sql"
    local compressed_file="${backup_file}.gz"
    local encrypted_file="${compressed_file}.gpg"
    
    log "Starting backup for database: $db_name"
    
    # Create database dump
    if ! mysqldump --defaults-file="$MYSQL_CONFIG" \
        --single-transaction \
        --routines \
        --triggers \
        --events \
        --add-drop-database \
        --databases "$db_name" > "$backup_file"; then
        error_exit "Failed to dump database: $db_name"
    fi
    
    # Compress backup
    if ! pigz -"$COMPRESSION_LEVEL" "$backup_file"; then
        rm -f "$backup_file"
        error_exit "Failed to compress backup for database: $db_name"
    fi
    
    # Encrypt backup
    if ! gpg --trust-model always --batch --yes --cipher-algo AES256 \
        --compress-algo 1 --recipient "$GPG_RECIPIENT" \
        --encrypt "$compressed_file"; then
        rm -f "$compressed_file"
        error_exit "Failed to encrypt backup for database: $db_name"
    fi
    
    # Remove unencrypted compressed file
    rm -f "$compressed_file"
    
    # Calculate and log file size
    local file_size=$(stat -c%s "$encrypted_file")
    local file_size_mb=$((file_size / 1024 / 1024))
    
    log "Successfully backed up database: $db_name (${file_size_mb}MB)"
}

Rotate old backups
rotate_backups() {
    log "Starting backup rotation (retention: $RETENTION_DAYS days)..."
    
    local deleted_count=0
    while IFS= read -r -d '' file; do
        rm -f "$file"
        ((deleted_count++))
    done < <(find "$BACKUP_DIR" -name "*.gpg" -type f -mtime +$RETENTION_DAYS -print0)
    
    log "Backup rotation completed. Deleted $deleted_count old backup files"
}

Generate backup report
generate_report() {
    local total_size=$(find "$BACKUP_DIR" -name "*.gpg" -type f -exec stat -c%s {} + | awk '{sum+=$1} END {print sum/1024/1024}')
    local backup_count=$(find "$BACKUP_DIR" -name "*_${TIMESTAMP}.sql.gz.gpg" -type f | wc -l)
    
    log "Backup completed successfully:"
    log "  - Databases backed up: $backup_count"
    log "  - Total backup size: ${total_size}MB"
    log "  - Backup location: $BACKUP_DIR"
    log "  - Retention period: $RETENTION_DAYS days"
}

Main execution
main() {
    log "=== MySQL Backup Started ==="
    
    check_prerequisites
    
    # Get databases and backup each one
    local databases
    databases=$(get_databases)
    
    if [[ -z "$databases" ]]; then
        error_exit "No databases found to backup"
    fi
    
    while IFS= read -r database; do
        backup_database "$database"
    done <<< "$databases"
    
    rotate_backups
    generate_report
    
    log "=== MySQL Backup Completed Successfully ==="
}

Run main function
main "$@"

Set script permissions and test

Configure proper permissions for the backup script and test it manually to ensure everything works correctly before automating it.

sudo chown mysql-backup:mysql-backup /usr/local/bin/mysql-backup.sh
sudo chmod 750 /usr/local/bin/mysql-backup.sh

Test the backup script
sudo -u mysql-backup /usr/local/bin/mysql-backup.sh

Create systemd service for backups

Create a systemd service unit that runs the backup script. This provides better logging and management compared to running scripts directly from cron.

[Unit]
Description=MySQL Database Backup Service
Wants=mysql-backup.timer
After=mysql.service
Requires=mysql.service

[Service]
Type=oneshot
User=mysql-backup
Group=mysql-backup
ExecStart=/usr/local/bin/mysql-backup.sh
Environment="PATH=/usr/local/bin:/usr/bin:/bin"
PrivateTmp=yes
PrivateDevices=yes
ProtectSystem=strict
ReadWritePaths=/var/backups/mysql /var/log/mysql-backup
NoNewPrivileges=yes
UMask=0077

Security hardening
ProtectHome=yes
ProtectKernelTunables=yes
ProtectKernelModules=yes
ProtectControlGroups=yes
RestrictRealtime=yes
RestrictSUIDSGID=yes
LockPersonality=yes
MemoryDenyWriteExecute=yes
RestrictNamespaces=yes
SystemCallFilter=@system-service
SystemCallFilter=~@debug @mount @cpu-emulation @obsolete @privileged

[Install]
WantedBy=multi-user.target

Create systemd timer for automated scheduling

Configure a systemd timer to run backups automatically. This example runs backups daily at 2:00 AM with some randomization to prevent multiple servers from backing up simultaneously.

[Unit]
Description=MySQL Database Backup Timer
Requires=mysql-backup.service

[Timer]
Run daily at 2:00 AM with 30 minute randomization
OnCalendar=--* 02:00:00
RandomizedDelaySec=1800
Persistent=true
AccuracySec=1m

Prevent running if system was suspended
WakeSystem=false

[Install]
WantedBy=timers.target

Enable and start the backup timer

Enable the systemd timer and service so they start automatically on boot. The timer will handle scheduling the backup service.

sudo systemctl daemon-reload
sudo systemctl enable mysql-backup.service
sudo systemctl enable mysql-backup.timer
sudo systemctl start mysql-backup.timer

Create backup monitoring script

Create a monitoring script that checks backup status and sends alerts if backups fail or become too old. This provides proactive monitoring of your backup system.

#!/bin/bash

MySQL Backup Monitor Script
Checks backup status and sends alerts

set -euo pipefail

BACKUP_DIR="/var/backups/mysql"
LOG_FILE="/var/log/mysql-backup/backup.log"
ALERT_EMAIL="admin@example.com"
MAX_BACKUP_AGE_HOURS=26  # Alert if no backup in 26 hours
HOSTNAME=$(hostname)

Check if recent backups exist
check_backup_freshness() {
    local latest_backup
    latest_backup=$(find "$BACKUP_DIR" -name "*.gpg" -type f -newermt "-$MAX_BACKUP_AGE_HOURS hours" | head -1)
    
    if [[ -z "$latest_backup" ]]; then
        echo "No recent MySQL backups found on $HOSTNAME. Last backup is older than $MAX_BACKUP_AGE_HOURS hours." | \
        mail -s "MySQL Backup Alert - No Recent Backups - $HOSTNAME" "$ALERT_EMAIL"
        exit 1
    fi
}

Check for errors in log file
check_backup_errors() {
    if [[ -f "$LOG_FILE" ]]; then
        local recent_errors
        recent_errors=$(grep -i "error\|failed" "$LOG_FILE" | tail -5)
        
        if [[ -n "$recent_errors" ]]; then
            echo "Recent errors found in MySQL backup log on $HOSTNAME:\n\n$recent_errors" | \
            mail -s "MySQL Backup Errors - $HOSTNAME" "$ALERT_EMAIL"
        fi
    fi
}

Check disk space
check_disk_space() {
    local available_space
    available_space=$(df "$BACKUP_DIR" | awk 'NR==2 {print $4}')
    local available_gb=$((available_space / 1024 / 1024))
    
    if [[ $available_gb -lt 1 ]]; then
        echo "Low disk space for MySQL backups on $HOSTNAME. Available: ${available_gb}GB" | \
        mail -s "MySQL Backup Disk Space Warning - $HOSTNAME" "$ALERT_EMAIL"
    fi
}

Generate backup status report
generate_status_report() {
    local backup_count
    local total_size
    local latest_backup_time
    
    backup_count=$(find "$BACKUP_DIR" -name "*.gpg" -type f | wc -l)
    total_size=$(find "$BACKUP_DIR" -name "*.gpg" -type f -exec stat -c%s {} + | awk '{sum+=$1} END {print sum/1024/1024/1024}')
    latest_backup_time=$(find "$BACKUP_DIR" -name "*.gpg" -type f -printf '%T@ %p\n' | sort -n | tail -1 | awk '{print $2}' | xargs stat -c %y 2>/dev/null || echo "No backups found")
    
    echo "MySQL Backup Status Report for $HOSTNAME:"
    echo "  - Total backup files: $backup_count"
    echo "  - Total backup size: ${total_size}GB"
    echo "  - Latest backup: $latest_backup_time"
    echo "  - Backup directory: $BACKUP_DIR"
}

Main monitoring function
main() {
    check_backup_freshness
    check_backup_errors
    check_disk_space
    
    # Optionally generate weekly status report
    if [[ $(date +%u) == "1" ]]; then
        generate_status_report | mail -s "Weekly MySQL Backup Status - $HOSTNAME" "$ALERT_EMAIL"
    fi
}

main "$@"

Set up monitoring timer

Create a systemd timer to run the monitoring script every 6 hours. This ensures you're alerted quickly if backups stop working.

sudo chown mysql-backup:mysql-backup /usr/local/bin/mysql-backup-monitor.sh
sudo chmod 750 /usr/local/bin/mysql-backup-monitor.sh

[Unit]
Description=MySQL Backup Monitor Service
After=network.target

[Service]
Type=oneshot
User=mysql-backup
Group=mysql-backup
ExecStart=/usr/local/bin/mysql-backup-monitor.sh
Environment="PATH=/usr/local/bin:/usr/bin:/bin"

[Unit]
Description=MySQL Backup Monitor Timer
Requires=mysql-backup-monitor.service

[Timer]
OnCalendar=--* 00,06,12,18:15:00
Persistent=true
AccuracySec=1m

[Install]
WantedBy=timers.target

sudo systemctl daemon-reload
sudo systemctl enable mysql-backup-monitor.timer
sudo systemctl start mysql-backup-monitor.timer

Configure log rotation

Set up logrotate to manage backup log files and prevent them from consuming excessive disk space over time.

/var/log/mysql-backup/*.log {
    daily
    rotate 30
    compress
    delaycompress
    missingok
    notifempty
    create 644 mysql-backup mysql-backup
    postrotate
        systemctl reload rsyslog > /dev/null 2>&1 || true
    endscript
}