BASH
Automating MySQL Database Backup and Compression
Learn to create a robust Bash script for automating MySQL database backups, compressing them, and storing them with a timestamp for easy recovery.
#!/bin/bash
# --- Configuration ---
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"
# Create backup directory if it doesn't exist
mkdir -p "${BACKUP_DIR}"
# Perform the database backup and compress it
if mysqldump -u"${DB_USER}" -p"${DB_PASS}" "${DB_NAME}" | gzip > "${BACKUP_FILE}"; then
echo "Backup of ${DB_NAME} successful: ${BACKUP_FILE}"
else
echo "Error: Database backup failed for ${DB_NAME}" >&2
exit 1
fi
# Optional: Remove old backups (e.g., older than 7 days)
find "${BACKUP_DIR}" -type f -name "*.sql.gz" -mtime +7 -delete
if [ $? -eq 0 ]; then
echo "Old backups removed successfully."
else
echo "Warning: Failed to remove old backups." >&2
fi
How it works: This script automates the process of backing up a MySQL database. It first defines configuration variables for database credentials, backup directory, and file naming. It then ensures the backup directory exists. Using `mysqldump`, it exports the specified database, pipes the output to `gzip` for compression, and saves it to a timestamped file. Finally, it includes an optional step to find and delete backup files older than 7 days, helping manage disk space.