BASH
Automate Daily MySQL Database Backups
Learn to create a simple Bash script for automating daily MySQL database backups, compressing them, and storing them with a timestamp for easy recovery.
#!/bin/bash
# --- Configuration ---
DB_USER="your_db_user" # MySQL username
DB_PASS="your_db_password" # MySQL password
DB_NAME="your_database_name" # Database name to backup
BACKUP_DIR="/path/to/backup/directory" # Directory to store backups
RETENTION_DAYS=7 # Number of days to keep backups
# ---------------------
DATE=$(date +%Y-%m-%d_%H-%M-%S)
BACKUP_FILE="$BACKUP_DIR/$DB_NAME-$DATE.sql.gz"
# Create backup directory if it doesn't exist
mkdir -p "$BACKUP_DIR"
echo "Starting backup of database '$DB_NAME' to '$BACKUP_FILE'..."
# Perform the database dump and compress it
mysqldump -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" | gzip > "$BACKUP_FILE"
# Check if the backup was successful
if [ $? -eq 0 ]; then
echo "Backup successful: $BACKUP_FILE"
else
echo "Backup failed! Please check logs and configuration." >&2
exit 1
fi
# Optional: Remove old backups
echo "Removing backups older than $RETENTION_DAYS days..."
find "$BACKUP_DIR" -name "$DB_NAME-*.sql.gz" -type f -mtime +"$RETENTION_DAYS" -delete
echo "Backup process completed."
exit 0
How it works: This Bash script provides a robust solution for automating MySQL database backups. It defines essential configuration variables like database credentials, backup directory, and retention policy. It first ensures the backup directory exists, then uses `mysqldump` to export the specified database. The output is piped directly to `gzip` for compression, creating a timestamped `.sql.gz` file. The script includes error checking to confirm backup success and an optional `find` command to automatically delete backups older than a specified number of days, helping manage disk space.