Database Backup Script

You are a database administrator. The user wants to create automated backup and restore scripts for database operations.

What to check first

• Verify database credentials and connection string are available (check .env or config files for DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
• Confirm mysqldump (MySQL), pg_dump (PostgreSQL), or equivalent backup utility is installed: which mysqldump or which pg_dump
• Ensure backup destination directory exists and has write permissions: mkdir -p ./backups && chmod 755 ./backups

Steps

1. Define backup configuration variables at the top of the script (database type, credentials, backup path, retention days)
2. Create a backup function that uses the appropriate dump command with compression (gzip or bzip2) to reduce file size
3. Implement timestamped filenames using date +%Y%m%d_%H%M%S so backups don't overwrite each other
4. Add a restore function that validates the backup file exists before attempting recovery
5. Implement cleanup logic to remove backups older than retention period using find command with -mtime flag
6. Add error handling with exit codes and logging to a dedicated log file (backup.log)
7. Set up the script to be executable (chmod +x backup.sh) and schedule it with cron for automated runs
8. Test restore functionality on a non-production database to verify backup integrity

Code

#!/bin/bash

# Database Backup & Restore Script
# Supports MySQL and PostgreSQL

set -euo pipefail

# Configuration
DB_TYPE="${DB_TYPE:-mysql}"  # mysql or postgresql
DB_HOST="${DB_HOST:-localhost}"
DB_USER="${DB_USER}"
DB_PASSWORD="${DB_PASSWORD}"
DB_NAME="${DB_NAME}"
BACKUP_DIR="${BACKUP_DIR:-./backups}"
RETENTION_DAYS="${RETENTION_DAYS:-30}"
LOG_FILE="${BACKUP_DIR}/backup.log"

# Create backup directory if it doesn't exist
mkdir -p "$BACKUP_DIR"

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

# Backup function for MySQL
backup_mysql() {
  local timestamp=$(date +%Y%m%d_%H%M%S)
  local backup_file="${BACKUP_DIR}/${DB_NAME}_${timestamp}.sql.gz"
  
  log "Starting MySQL backup of $DB_NAME..."
  
  MYSQL_PWD="$DB_PASSWORD" mysqldump \
    --host="$DB_HOST" \
    --user="$DB_USER" \
    --single-transaction \
    --quick \
    "$DB_NAME" | gzip > "$backup_file"
  
  if [ $? -eq 0 ]; then
    log "✓

Note: this example was truncated in the source. See the GitHub repo for the latest full version.

Common Pitfalls

• Treating this skill as a one-shot solution — most workflows need iteration and verification
• Skipping the verification steps — you don't know it worked until you measure
• Applying this skill without understanding the underlying problem — read the related docs first

When NOT to Use This Skill

• When a simpler manual approach would take less than 10 minutes
• On critical production systems without testing in staging first
• When you don't have permission or authorization to make these changes

How to Verify It Worked

• Run the verification steps documented above
• Compare the output against your expected baseline
• Check logs for any warnings or errors — silent failures are the worst kind

Production Considerations

• Test in staging before deploying to production
• Have a rollback plan — every change should be reversible
• Monitor the affected systems for at least 24 hours after the change