Automating MySQL/PostgreSQL Database Backups

#!/bin/bash

# --- Configuration ---
DB_TYPE="mysql" # or "postgresql"
DB_NAME="your_database_name"
DB_USER="your_db_user"
DB_PASS="your_db_password"
BACKUP_DIR="/var/backups/databases"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)

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

# Perform backup based on DB type
if [ "$DB_TYPE" == "mysql" ]; then
  echo "Backing up MySQL database: $DB_NAME..."
  mysqldump -u $DB_USER -p$DB_PASS $DB_NAME | gzip > "$BACKUP_DIR/$DB_NAME-$TIMESTAMP.sql.gz"
  if [ $? -eq 0 ]; then
    echo "MySQL backup successful: $BACKUP_DIR/$DB_NAME-$TIMESTAMP.sql.gz"
  else
    echo "Error: MySQL backup failed."
    exit 1
  fi
elif [ "$DB_TYPE" == "postgresql" ]; then
  echo "Backing up PostgreSQL database: $DB_NAME..."
  PGPASSWORD=$DB_PASS pg_dump -U $DB_USER $DB_NAME | gzip > "$BACKUP_DIR/$DB_NAME-$TIMESTAMP.sql.gz"
  if [ $? -eq 0 ]; then
    echo "PostgreSQL backup successful: $BACKUP_DIR/$DB_NAME-$TIMESTAMP.sql.gz"
  else
    echo "Error: PostgreSQL backup failed."
    exit 1
  fi
else
  echo "Error: Unsupported database type: $DB_TYPE"
  exit 1
fi

# Optional: Clean up old backups (e.g., keep last 7 days)
# find $BACKUP_DIR -type f -name "*.sql.gz" -mtime +7 -delete

echo "Database backup script finished."