Automate Database Backups (MySQL/PostgreSQL)

#!/bin/bash

# Script to automate database backups

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

mkdir -p "$BACKUP_DIR"

if [ "$DB_TYPE" == "mysql" ]; then
  BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_mysql_${TIMESTAMP}.sql.gz"
  echo "Backing up MySQL database '$DB_NAME' to '$BACKUP_FILE'..."
  mysqldump -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" | gzip > "$BACKUP_FILE"
  if [ $? -eq 0 ]; then
    echo "MySQL backup successful."
  else
    echo "MySQL backup failed!"
    exit 1
  fi
elif [ "$DB_TYPE" == "postgresql" ]; then
  BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_psql_${TIMESTAMP}.sql.gz"
  echo "Backing up PostgreSQL database '$DB_NAME' to '$BACKUP_FILE'..."
  PGPASSWORD="$DB_PASS" pg_dump -U "$DB_USER" "$DB_NAME" | gzip > "$BACKUP_FILE"
  if [ $? -eq 0 ]; then
    echo "PostgreSQL backup successful."
  else
    echo "PostgreSQL backup failed!"
    exit 1
  fi
else
  echo "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 process completed."