Automate WordPress Database Backup with mysqldump and Cron

Step 1 — Create the Backup Directory

sudo mkdir -p /var/backups/wordpress/db
sudo chown root:root /var/backups/wordpress/db
sudo chmod 750 /var/backups/wordpress/db

Step 2 — Create the Backup Script

sudo nano /usr/local/bin/wp-db-backup.sh

#!/bin/bash

# WordPress Database Backup Script
# Runs via cron daily — backs up all WordPress databases

# Configuration
BACKUP_DIR="/var/backups/wordpress/db"
DATE=$(date +%Y-%m-%d_%H-%M-%S)
RETENTION_DAYS=30

# Database credentials
DB_USER="root"
DB_PASS="your-mariadb-root-password"

# List of databases to back up — add each WordPress database
DATABASES=(
    "wordpress_site"
    # "second_site_db"
    # "third_site_db"
)

# Create backup for each database
for DB in "${DATABASES[@]}"; do
    FILENAME="${BACKUP_DIR}/${DB}_${DATE}.sql.gz"
    
    mysqldump \
        --user="${DB_USER}" \
        --password="${DB_PASS}" \
        --single-transaction \
        --routines \
        --triggers \
        "${DB}" | gzip > "${FILENAME}"
    
    if [ $? -eq 0 ]; then
        echo "$(date): Backup successful: ${FILENAME}" >> /var/log/wp-backup.log
    else
        echo "$(date): Backup FAILED for ${DB}" >> /var/log/wp-backup.log
    fi
done

# Remove backups older than retention period
find "${BACKUP_DIR}" -name "*.sql.gz" -mtime +${RETENTION_DAYS} -delete
echo "$(date): Old backups cleaned (>${RETENTION_DAYS} days)" >> /var/log/wp-backup.log

Make it executable:

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

Step 3 — Test the Script Manually

sudo /usr/local/bin/wp-db-backup.sh

Verify the backup file was created:

ls -lh /var/backups/wordpress/db/
# Should show: wordpress_site_2026-06-08_02-00-00.sql.gz

Check the file is valid:

# List contents of the compressed backup
gunzip -l /var/backups/wordpress/db/*.sql.gz

Check the log:

cat /var/log/wp-backup.log

Step 4 — Schedule with Cron

sudo crontab -e

Add this line — runs at 2:00 AM daily:

0 2 * * * /usr/local/bin/wp-db-backup.sh

Save and exit. Verify the cron entry:

sudo crontab -l

Step 5 — Secure the Credentials

The backup script contains your database password in plain text. Restrict access:

sudo chmod 700 /usr/local/bin/wp-db-backup.sh
sudo chown root:root /usr/local/bin/wp-db-backup.sh

Only root can read or execute it. For better practice, use a MariaDB option file instead of inline credentials:

sudo nano /root/.my.cnf

[mysqldump]
user=root
password=your-mariadb-root-password

sudo chmod 600 /root/.my.cnf

Then simplify the mysqldump command in the script to remove the credential flags — it reads them from .my.cnf automatically.

Verify After First Automated Run

After the cron runs for the first time (check the next morning):

# List backup files
ls -lh /var/backups/wordpress/db/

# Check log for success/failure
cat /var/log/wp-backup.log

# Verify backup is not empty
gunzip -c /var/backups/wordpress/db/wordpress_site_*.sql.gz | head -5
# Should show SQL comments and CREATE TABLE statements

The next article uploads these backups offsite so they survive a server-level failure.