BASH
Automated MySQL/PostgreSQL Database Backup
A robust Bash script to automate daily backups of a MySQL or PostgreSQL database, compress the dumps, and manage retention of old backups.
#!/bin/bash
DB_USER="your_db_user"
DB_PASS="your_db_password"
DB_NAME="your_database_name"
BACKUP_DIR="/path/to/backup/dir"
RETENTION_DAYS=7
# --- MySQL Example ---
# DUMP_CMD="mysqldump --user=$DB_USER --password=$DB_PASS $DB_NAME"
# --- PostgreSQL Example ---
DUMP_CMD="pg_dump --username=$DB_USER $DB_NAME"
export PGPASSWORD=$DB_PASS # For pg_dump to use password
FILENAME="$DB_NAME-$(date +%Y%m%d%H%M%S).sql.gz"
mkdir -p "$BACKUP_DIR"
if $DUMP_CMD | gzip > "$BACKUP_DIR/$FILENAME"; then
echo "Database backup successful: $BACKUP_DIR/$FILENAME"
else
echo "Database backup FAILED!" >&2
exit 1
fi
# Clean up old backups
find "$BACKUP_DIR" -type f -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
echo "Old backups cleaned up (older than $RETENTION_DAYS days)."
unset PGPASSWORD # Unset password for security (PostgreSQL only)
How it works: This script automates the backup process for either MySQL (using `mysqldump`) or PostgreSQL (using `pg_dump`). It securely connects to the database, dumps its content, compresses it using `gzip`, and saves it with a timestamp. Crucially, it also includes a cleanup mechanism using `find` to automatically delete old backup files beyond a specified retention period, preventing excessive disk usage.