BASH
Automate MySQL Database Backups
Create a robust Bash script to automatically perform daily MySQL database backups, compressing them and optionally removing old backups to save disk space and ensure data safety.
#!/bin/bash
# --- Configuration ---
DB_USER="your_db_user" # MySQL username
DB_PASS="your_db_password" # MySQL password
DB_NAME="your_database_name" # Database to backup
BACKUP_DIR="/var/backups/mysql" # Directory to store backups
MAX_AGE_DAYS=7 # Number of days to keep backups
# Ensure backup directory exists
mkdir -p "${BACKUP_DIR}"
# Define the backup filename with a timestamp
TIMESTAMP=$(date +%Y%m%d%H%M%S)
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${TIMESTAMP}.sql.gz"
echo "Starting backup of database '${DB_NAME}'..."
# Perform the MySQL 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 "Error: Backup failed!"
exit 1
fi
# Clean up old backups
echo "Cleaning up old backups (older than ${MAX_AGE_DAYS} days)..."
find "${BACKUP_DIR}" -type f -name "*.sql.gz" -mtime +${MAX_AGE_DAYS} -delete
echo "Database backup and cleanup complete."
# To restore: gzip -d < backup_file.sql.gz | mysql -u"${DB_USER}" -p"${DB_PASS}" "${DB_NAME}"
How it works: This Bash script provides an automated solution for backing up MySQL databases. It connects to a specified database using provided credentials, performs a full dump using `mysqldump`, and then compresses the resulting SQL file with `gzip`. The backup file is named with a timestamp for easy organization and recovery. After a successful backup, the script cleans up old backup files that exceed a defined retention period, preventing excessive disk usage. This script is invaluable for web developers and administrators to ensure data integrity and facilitate disaster recovery for their applications. It can be scheduled as a daily cron job.