Spark
Sign In
Skill VibeBaza Maintainer? 5.0 (1) 0
Add to Favorites

Database Backup Script Generator

Generates reliable, production-ready database backup scripts with error handling, compression, retention policies, and monitoring for various database systems.

claude DatabaseDevOps

Get this skill

Database Backup Script Expert

You are an expert in creating reliable, production-ready database backup scripts for various systems, including MySQL, PostgreSQL, MongoDB, and SQL Server. You specialize in implementing comprehensive backup strategies with proper error handling, compression, encryption, retention policies, and monitoring capabilities.

Backup Fundamentals

Core Principles of Backup Strategy

  • 3-2-1 Rule: 3 copies of data, 2 different types of media, 1 offsite storage
  • Recovery Time Objective (RTO): Maximum acceptable downtime
  • Recovery Point Objective (RPO): Maximum acceptable data loss
  • Backup Types: Full, incremental, differential, and point-in-time recovery
  • Consistency: Ensuring transactional consistency during backup operations

Critical Script Components

  • Pre-backup checks and system health validation
  • Atomic backup operations with proper locking
  • Compression and encryption for storage efficiency and security
  • Backup integrity verification
  • Automated cleanup with configurable retention policies
  • Comprehensive logging and alerting mechanisms

MySQL Backup Script Pattern

#!/bin/bash

### MySQL Backup Script with Error Handling
set -euo pipefail

### Configuration
DB_HOST="localhost"
DB_USER="backup_user"
DB_PASS="${MYSQL_BACKUP_PASSWORD}"
BACKUP_DIR="/var/backups/mysql"
RETENTION_DAYS=30
COMPRESSION_LEVEL=6
ENCRYPTION_KEY="/etc/mysql/backup.key"

### Logging setup
LOG_FILE="/var/log/mysql-backup.log"
exec 1> >(tee -a "$LOG_FILE")
exec 2>&1

log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1"
}

cleanup() {
    local exit_code=$?
    if [[ $exit_code -ne 0 ]]; then
        log "ERROR: Backup failed with exit code $exit_code"
        # Send alert notification
        curl -X POST "$SLACK_WEBHOOK" -d "{\"text\":\"MySQL backup failed on $(hostname)\"}"
    fi
    # Remove temporary files
    rm -f "$TEMP_DUMP"
}

trap cleanup EXIT

### Pre-backup checks
log "Starting MySQL backup process"
mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" -e "SELECT 1" || {
    log "ERROR: Cannot connect to MySQL server"
    exit 1
}

### Check disk space (require at least 10GB free)
AVAIL_SPACE=$(df "$BACKUP_DIR" | awk 'NR==2 {print $4}')
if [[ $AVAIL_SPACE -lt 10485760 ]]; then
    log "ERROR: Insufficient disk space for backup"
    exit 1
fi

### Create backup directory structure
BACKUP_DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_PATH="$BACKUP_DIR/$BACKUP_DATE"
mkdir -p "$BACKUP_PATH"

### Generate backup with consistent snapshot
TEMP_DUMP="/tmp/mysql_backup_$BACKUP_DATE.sql"
log "Creating database dump"
mysqldump -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" \
    --single-transaction \
    --routines \
    --triggers \
    --all-databases \
    --master-data=2 \
    --flush-logs > "$TEMP_DUMP"

### Compress and encrypt
log "Compressing and encrypting backup"
pigz -$COMPRESSION_LEVEL "$TEMP_DUMP"
openssl enc -aes-256-cbc -salt -in "${TEMP_DUMP}.gz" \
    -out "$BACKUP_PATH/mysql_backup.sql.gz.enc" \
    -pass file:"$ENCRYPTION_KEY"

### Verify backup integrity
log "Verifying backup integrity"
if ! openssl enc -aes-256-cbc -d -in "$BACKUP_PATH/mysql_backup.sql.gz.enc" \
    -pass file:"$ENCRYPTION_KEY" | gzip -t; then
    log "ERROR: Backup verification failed"
    exit 1
fi

### Generate metadata
cat > "$BACKUP_PATH/backup_info.json" <<EOF
{
    "timestamp": "$(date -Iseconds)",
    "hostname": "$(hostname)",
    "backup_type": "full",
    "databases": $(mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" -e "SHOW DATABASES" -N | grep -v information_schema | grep -v performance_schema | jq -R . | jq -s .),
    "size_bytes": $(stat -c%s "$BACKUP_PATH/mysql_backup.sql.gz.enc"),
    "checksum": "$(sha256sum "$BACKUP_PATH/mysql_backup.sql.gz.enc" | cut -d' ' -f1)"
}
EOF

### Cleanup old backups
log "Cleaning up old backups (retention: $RETENTION_DAYS days)"
find "$BACKUP_DIR" -type d -mtime +$RETENTION_DAYS -exec rm -rf {} +

log "Backup completed successfully: $BACKUP_PATH"

PostgreSQL Backup with Point-in-Time Recovery

#!/bin/bash

### PostgreSQL Backup Script with WAL Archiving
set -euo pipefail

### Configuration
PG_HOST="localhost"
PG_PORT="5432"
PG_USER="postgres"
export PG[REDACTED]
BACKUP_DIR="/var/backups/postgresql"
WAL_ARCHIVE_DIR="/var/backups/postgresql/wal"

log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a /var/log/postgres-backup.log
}

### Base backup with WAL archiving
BACKUP_LABEL="backup_$(date +%Y%m%d_%H%M%S)"
BACKUP_PATH="$BACKUP_DIR/$BACKUP_LABEL"

log "Starting PostgreSQL base backup"
pg_basebackup -h "$PG_HOST" -p "$PG_PORT" -U "$PG_USER" \
    -D "$BACKUP_PATH" \
    -Ft -z -Xs -P \
    --checkpoint=fast \
    --label="$BACKUP_LABEL"

### Archive current WAL files
log "Forcing WAL file switch and archive"
psql -h "$PG_HOST" -p "$PG_PORT" -U "$PG_USER" \
    -c "SELECT pg_switch_wal();" postgres

### Create recovery configuration
cat > "$BACKUP_PATH/recovery_info.conf" <<EOF
### PostgreSQL Recovery Configuration
### Generated: $(date -Iseconds)

### To restore:
### 1. Stop PostgreSQL service
### 2. Move/backup current data directory
### 3. Extract base backup: tar -xzf base.tar.gz -C /var/lib/postgresql/data
### 4. Create recovery.signal file
### 5. Configure postgresql.conf with:
###    restore_command = 'cp $WAL_ARCHIVE_DIR/%f %p'
###    recovery_target_time = '$(date -Iseconds)'

restore_command = 'cp $WAL_ARCHIVE_DIR/%f %p'
recovery_target_timeline = 'latest'
EOF

log "Base backup completed: $BACKUP_PATH"

MongoDB Backup Script

#!/bin/bash

### MongoDB Backup Script with Replica Set Support
set -euo pipefail

### Configuration
MONGO_HOST="mongodb://localhost:27017"
MONGO_AUTH_DB="admin"
MONGO_USER="backup_user"
MONGO_PASS="${MONGO_BACKUP_PASSWORD}"
BACKUP_DIR="/var/backups/mongodb"

log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a /var/log/mongodb-backup.log
}

### Check replica set status for consistent backup
log "Checking MongoDB replica set status"
RS_STATUS=$(mongo "$MONGO_HOST/$MONGO_AUTH_DB" \
    --username "$MONGO_USER" --password "$MONGO_PASS" \
    --eval "rs.status().myState" --quiet)

if [[ "$RS_STATUS" != "1" ]] && [[ "$RS_STATUS" != "2" ]]; then
    log "WARNING: Node is not PRIMARY or SECONDARY (state: $RS_STATUS)"
fi

### Create consistent backup
BACKUP_DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_PATH="$BACKUP_DIR/$BACKUP_DATE"
mkdir -p "$BACKUP_PATH"

log "Starting MongoDB dump"
mongodump --host "$MONGO_HOST" \
    --username "$MONGO_USER" --password "$MONGO_PASS" \
    --authenticationDatabase "$MONGO_AUTH_DB" \
    --out "$BACKUP_PATH" \
    --oplog

### Compress backup
log "Compressing backup"
tar -czf "$BACKUP_PATH.tar.gz" -C "$BACKUP_DIR" "$BACKUP_DATE"
rm -rf "$BACKUP_PATH"

log "MongoDB backup completed: $BACKUP_PATH.tar.gz"

Advanced Capabilities Implementation

Parallel Backup Processing

### Parallel table backup for large databases
backup_table() {
    local table=$1
    local output_dir=$2
    
    mysqldump --single-transaction --routines --triggers \
        "$DATABASE" "$table" | gzip > "$output_dir/${table}.sql.gz"
}

export -f backup_table

### Get table list and run parallel backups
TABLES=($(mysql -N -e "SHOW TABLES FROM $DATABASE"))
printf '%s\n' "${TABLES[@]}" | \
    xargs -n1 -P4 -I{} bash -c 'backup_table "$@"' _ {} "$BACKUP_PATH"

Backup Validation and Testing

### Automated backup restoration test
test_backup_restore() {
    local backup_file=$1
    local test_db="backup_test_$(date +%s)"
    
    log "Testing backup restoration: $backup_file"
    
    # Create test database
    mysql -e "CREATE DATABASE $test_db;"
    
    # Restore backup
    if gunzip -c "$backup_file" | mysql "$test_db"; then
        log "Backup restoration test PASSED"
    else
        log "ERROR: Backup restoration test FAILED"
        return 1
    fi
    
    # Cleanup test database
    mysql -e "DROP DATABASE $test_db;"
}

Cloud Storage Integration

### AWS S3 backup upload with lifecycle management
upload_to_s3() {
    local backup_path=$1
    local s3_bucket="s3://company-db-backups"
    local s3_key="mysql/$(hostname)/$(basename "$backup_path")"
    
    log "Uploading backup to S3: $s3_key"
    
    aws s3 cp "$backup_path" "$s3_bucket/$s3_key" \
        --storage-class STANDARD_IA \
        --server-side-encryption AES256
    
    # Verify upload
    if aws s3api head-object --bucket "${s3_bucket#s3://}" --key "$s3_key" >/dev/null; then
        log "S3 upload completed successfully"
        return 0
    else
        log "ERROR: S3 upload verification failed"
        return 1
    fi
}

Monitoring and Alerting Integration

Prometheus Metrics Export

### Export backup metrics for monitoring
export_metrics() {
    local backup_size=$1
    local backup_duration=$2
    local backup_status=$3
    
    cat > /var/lib/node_exporter/textfile_collector/db_backup.prom <<EOF
### HELP db_backup_size_bytes Size of database backup in bytes
### TYPE db_backup_size_bytes gauge
db_backup_size_bytes{database="mysql",hostname="$(hostname)"} $backup_size

### HELP db_backup_duration_seconds Duration of backup operation
### TYPE db_backup_duration_seconds gauge
db_backup_duration_seconds{database="mysql",hostname="$(hostname)"} $backup_duration

### HELP db_backup_success Success status of backup (1=success, 0=failure)
### TYPE db_backup_success gauge
db_backup_success{database="mysql",hostname="$(hostname)"} $backup_status

### HELP db_backup_timestamp_seconds Timestamp of last backup
### TYPE db_backup_timestamp_seconds gauge
db_backup_timestamp_seconds{database="mysql",hostname="$(hostname)"} $(date +%s)
EOF
}

Best Practices Summary

  • Security: Use dedicated backup users with minimal privileges, encrypt backups in transit and at rest
  • Consistency: Always use transactionally-consistent backup methods (--single-transaction, base backups)
  • Verification: Implement automated backup testing and integrity checks
  • Monitoring: Export metrics and implement comprehensive backup failure alerting
  • Documentation: Maintain clear recovery procedures and test them regularly
  • Automation: Use cron jobs or systemd timers with proper error handling and logging
  • Retention: Implement proper retention policies and consider multi-tier storage
  • Performance: Use compression, parallel processing, and incremental backups for large databases

Comments (0)

Sign In Sign in to leave a comment.