BASH
Automating Database Backups with mysqldump and Compression
Learn to automate database backups for MySQL/MariaDB using 'mysqldump', compressing the output with 'gzip' and timestamping files for robust data preservation.
#!/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="$DB_NAME-$TIMESTAMP.sql.gz"
# Create backup directory if it doesn't exist
mkdir -p "$BACKUP_DIR" || {
echo "Error: Could not create backup directory $BACKUP_DIR." >&2
exit 1
}
# Perform the database dump and compress it
echo "Starting backup of database '$DB_NAME' to $BACKUP_DIR/$BACKUP_FILE"
mysqldump -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" | gzip > "$BACKUP_DIR/$BACKUP_FILE"
# Check if the backup was successful
if [ ${PIPESTATUS[0]} -eq 0 ] && [ ${PIPESTATUS[1]} -eq 0 ]; then
echo "Backup successful: $BACKUP_DIR/$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 "Error: Database backup failed." >&2
exit 1
fi
echo "Backup script finished."
How it works: This script automates the process of backing up a MySQL or MariaDB database. It uses `mysqldump` to create a logical dump of the specified database, piping its output directly to `gzip` for compression. The resulting backup file is named with a timestamp to prevent overwriting previous backups, and stored in a designated backup directory. Error handling ensures that the script exits if the directory cannot be created or if the `mysqldump` or `gzip` commands fail, indicated by checking `PIPESTATUS`.