How to Set Up Automated Daily MySQL Database Backups with Cron Job

Why Automated Database Backups Are Non-Negotiable

Database corruption, accidental table drops, failed WordPress plugin updates, and ransomware attacks can instantly wipe out years of business data. Relying on manual backups guarantees data loss. A fully automated backup workflow with local retention rules and offsite cloud synchronization ensures you can restore any database in under 3 minutes.

Step 1: Creating the Production Backup Bash Script

Create a dedicated script file on your Linux VPS:

sudo mkdir -p /var/backups/mysql
sudo nano /usr/local/bin/mysql_auto_backup.sh

Paste the following production-grade bash backup script:

#!/bin/bash
# ==========================================================
# Automated MySQL Daily Backup & Retention Script
# ==========================================================
BACKUP_DIR="/var/backups/mysql"
TIMESTAMP=$(date +"%Y-%m-%d_%H%M%S")
RETENTION_DAYS=7

# MySQL Credentials (or use ~/.my.cnf for password security)
DB_USER="root"
DB_PASS="YourSecureDatabasePassword"

# Create target backup directory
mkdir -p "$BACKUP_DIR"

# Loop through all non-system databases and dump
DATABASES=$(mysql -u "$DB_USER" -p"$DB_PASS" -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|mysql|sys)")

for DB in $DATABASES; do
    echo "Backing up database: $DB..."
    mysqldump -u "$DB_USER" -p"$DB_PASS"         --single-transaction         --quick         --routines         --triggers         "$DB" | gzip > "$BACKUP_DIR/${DB}_${TIMESTAMP}.sql.gz"
done

# Purge local backups older than retention threshold
find "$BACKUP_DIR" -type f -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete

echo "[$(date)] MySQL daily backup completed successfully." >> /var/log/mysql_backup.log

Step 2: Granting Execution Permissions and Testing

# Secure permissions (root only)
sudo chmod 700 /usr/local/bin/mysql_auto_backup.sh

# Run manual test
sudo /usr/local/bin/mysql_auto_backup.sh

# Verify generated backup files
ls -lh /var/backups/mysql/

Step 3: Scheduling the Daily Cron Job

Open the root user’s crontab:

sudo crontab -e

Add the following cron schedule to execute the script daily at 2:00 AM:

0 2 * * * /usr/local/bin/mysql_auto_backup.sh >/dev/null 2>&1

Step 4: Syncing Backups to Cloud Storage (AWS S3 / Backblaze B2)

Using rclone, append this command to the backup script to mirror daily backups to an offsite S3 cloud bucket:

rclone sync /var/backups/mysql/ remote_s3:cpanelfree-db-backups/daily/

Securing Database Backups with OpenSSL AES-256-CBC Encryption

Storing unencrypted plaintext database dumps on cloud storage exposes customer passwords, hashed tokens, and private user data to potential leaks. Integrate on-the-fly AES encryption into your backup bash script:

# Export, compress, and encrypt in a single pipeline
mysqldump -u root -p'Pass123' --single-transaction --quick dbname | gzip | openssl enc -aes-256-cbc -salt -pbkdf2 -pass pass:'SecretEncryptionKey' > /var/backups/mysql/dbname_encrypted.sql.gz.enc

Automated Decryption and Restoration Command

To restore an encrypted database backup during disaster recovery operations:

openssl enc -d -aes-256-cbc -pbkdf2 -pass pass:'SecretEncryptionKey' -in dbname_encrypted.sql.gz.enc | gunzip | mysql -u root -p dbname

Automating Health Alerts for Failed Database Backups via Webhooks

To ensure you are immediately notified if a backup fails due to disk space exhaustion or permission errors, integrate Slack or Discord webhook alerts directly into your bash script:

# Webhook error notification trap
if [ $? -ne 0 ]; then
    curl -H "Content-Type: application/json" -X POST -d '{"content":"⚠️ CRITICAL: MySQL backup failed on production server!"}' https://discord.com/api/webhooks/your-webhook-url
    exit 1
fi

Testing Backup Restoration Integrity with Automated Staging Scripts

A backup is only as good as its restore test. Set up a monthly automated test script that imports the latest backup file into an isolated temporary staging database and runs mysqlcheck to certify zero table corruption.

Frequently Asked Questions

Setting Up Automated Database Restoration Verification Tests

To verify that automated backups are valid, create a secondary cron script that restores the daily dump into a testing schema (e.g. backup_verify_db) and checks for table existence:

# Automated Restoration Health Test
mysql -u root -p'Pass' -e "CREATE DATABASE IF NOT EXISTS test_restore;"
gunzip < /var/backups/mysql/latest.sql.gz | mysql -u root -p'Pass' test_restore
mysql -u root -p'Pass' -e "DROP DATABASE test_restore;"

Implementing automated cron backups with offsite cloud replication guarantees that your business data remains resilient against hardware failures and ransomware attacks.

Testing and validating your automated backup pipeline ensures that critical business databases can be recovered within minutes during any catastrophic emergency.