Automated MySQL Backups with Cron and mysqldump

Basic mysqldump Backup

The simplest way to back up a MySQL/MariaDB database is with mysqldump. It produces a SQL file that can recreate the entire database.

mysqldump -u root -p mydatabase > /backups/mydatabase_$(date +%Y%m%d).sql

Create a Backup Script

Create /usr/local/bin/db-backup.sh:

#!/bin/bash
BACKUP_DIR="/backups/mysql"
RETENTION=14  # days
DATE=$(date +%Y%m%d_%H%M%S)
DATABASES=$(mysql -u root -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|sys)")

mkdir -p "$BACKUP_DIR"

for DB in $DATABASES; do
    FILE="$BACKUP_DIR/${DB}_${DATE}.sql.gz"
    mysqldump -u root --single-transaction --routines --triggers "$DB" | gzip > "$FILE"
    echo "Backed up: $DB -> $FILE"
done

# Remove old backups
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION -delete
echo "Cleanup complete. Removed backups older than $RETENTION days."

chmod +x /usr/local/bin/db-backup.sh

Schedule with Cron

# Run daily at 2 AM
0 2 * * * /usr/local/bin/db-backup.sh >> /var/log/db-backup.log 2>&1

Important Options

• --single-transaction — consistent snapshot for InnoDB without locking
• --routines — include stored procedures and functions
• --triggers — include triggers (default in newer versions)
• --quick — fetch rows one at a time (saves memory on large tables)

Restoring from Backup

gunzip < /backups/mysql/mydatabase_20260225.sql.gz | mysql -u root mydatabase