BASH
Automating Daily MySQL/PostgreSQL Database Backups
Create timestamped, compressed backups of your database (MySQL or PostgreSQL) daily. This script automates backup generation and retention policy, removing old files.
#!/bin/bash
DB_USER="your_db_user"
DB_PASS="your_db_password"
DB_NAME="your_database_name"
BACKUP_DIR="/path/to/your/backups"
RETENTION_DAYS=7
# Ensure backup directory exists
mkdir -p "$BACKUP_DIR"
TIMESTAMP=$(date +%Y%m%d%H%M%S)
BACKUP_FILE="${DB_NAME}_${TIMESTAMP}.sql.gz"
# --- MySQL Backup ---
# If using MySQL, uncomment the following block and comment out PostgreSQL
# mysqldump -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" | gzip > "$BACKUP_DIR/$BACKUP_FILE"
# if [ $? -eq 0 ]; then
# echo "MySQL backup of $DB_NAME created successfully: $BACKUP_FILE"
# else
# echo "MySQL backup of $DB_NAME FAILED!"
# fi
# --- PostgreSQL Backup ---
# If using PostgreSQL, uncomment the following block and comment out MySQL
export PGPASSWORD="$DB_PASS" # Set password for pg_dump
pg_dump -U "$DB_USER" "$DB_NAME" | gzip > "$BACKUP_DIR/$BACKUP_FILE"
if [ $? -eq 0 ]; then
echo "PostgreSQL backup of $DB_NAME created successfully: $BACKUP_FILE"
else
echo "PostgreSQL backup of $DB_NAME FAILED!"
fi
unset PGPASSWORD # Unset password for security
# Clean up old backups
find "$BACKUP_DIR" -type f -name "${DB_NAME}_*.sql.gz" -mtime +$RETENTION_DAYS -delete
echo "Cleaned up backups older than $RETENTION_DAYS days."
How it works: This script automates database backups for either MySQL or PostgreSQL. It takes database credentials, a backup directory, and a retention period. It first creates the backup directory if it doesn't exist, then generates a timestamped, gzipped SQL dump of the specified database. After a successful backup, it uses the `find` command to locate and delete backup files older than the specified retention days, helping to manage disk space. Remember to adjust database type (MySQL/PostgreSQL) and credentials.