BASH
Automated MySQL Database Backup with Timestamp
Create a robust bash script to automate daily MySQL database backups, compressing them with a timestamp for easy recovery and storage management.
#!/bin/bash
DB_USER="your_db_user"
DB_PASS="your_db_password"
DB_NAME="your_database_name"
BACKUP_DIR="/var/backups/mysql"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/$DB_NAME-$TIMESTAMP.sql.gz"
mkdir -p "$BACKUP_DIR"
echo "Starting backup of database '$DB_NAME'...
"
mysqldump -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" | gzip > "$BACKUP_FILE"
if [ $? -eq 0 ]; then
echo "Backup successful: $BACKUP_FILE"
# Optional: Remove old backups (e.g., older than 7 days)
find "$BACKUP_DIR" -type f -name "*.sql.gz" -mtime +7 -delete
echo "Old backups cleaned up."
else
echo "Backup failed for database '$DB_NAME'."
fi
How it works: This script automates backing up a specified MySQL database. It utilizes `mysqldump` to export the database content, pipes the output directly to `gzip` for efficient compression, and saves the compressed backup with a unique timestamp in a designated directory. An optional `find` command is included to automatically remove backups older than 7 days, helping manage disk space. This is ideal for cron-scheduled daily backups.