BASH
Automate Compressed MySQL Database Backups
Learn to create automated, timestamped, and gzipped backups of your MySQL or MariaDB databases, essential for disaster recovery and data protection.
#!/bin/bash
DB_USER="your_db_user"
DB_PASS="your_db_password"
DB_NAME="your_database_name"
BACKUP_DIR="/var/backups/mysql"
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"
# Perform the database dump and compress it
mysqldump -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" | gzip > "$BACKUP_FILE"
if [ $? -eq 0 ]; then
echo "Database backup successful: $BACKUP_FILE"
# Optional: Remove backups older than 7 days
find "$BACKUP_DIR" -type f -name "*.sql.gz" -mtime +7 -delete
echo "Old backups cleaned up."
else
echo "Database backup failed."
exit 1
fi
How it works: This script automates backing up a MySQL/MariaDB database. It uses `mysqldump` to export the database, pipes the output to `gzip` for compression, and saves it with a timestamped filename. It also creates the backup directory if needed and includes an optional cleanup step to remove old backups, making it suitable for cron jobs for scheduled, reliable database backups.