How to Back Up MySQL Databases Automatically on Linux

Automated MySQL backups are essential for any production server. This guide covers a complete backup script with rotation and offsite storage.

Backup Script

#!/bin/bash
BACKUP_DIR="/backups/mysql"
DATE=$(date +%Y-%m-%d_%H-%M)
MYSQL_USER="root"
MYSQL_PASS="your-password"

mkdir -p $BACKUP_DIR

DATABASES=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW DATABASES;" | grep -v -E "^(Database|information_schema|performance_schema|sys)$")

for DB in $DATABASES; do
    mysqldump -u$MYSQL_USER -p$MYSQL_PASS         --single-transaction --routines --triggers         $DB | gzip > $BACKUP_DIR/${DB}_${DATE}.sql.gz
done

find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete
echo "MySQL backup completed"

Schedule

0 2 * * * /usr/local/bin/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1

Upload to S3

s3cmd sync $BACKUP_DIR/ s3://your-bucket/mysql-backups/

Conclusion

Automated MySQL backups protect your most valuable data. Our team configures comprehensive database backup solutions.