Back Up and Restore a WordPress MySQL Database with mysqldump and Cron

Home » MySQL » Back Up and Restore a WordPress MySQL Database with mysqldump and Cron
June 30, 2023 MySQL
Back Up and Restore a WordPress MySQL Database with mysqldump and Cron

A reliable database backup strategy for WordPress requires automated, scheduled exports stored in a location separate from the web server, with a retention policy that keeps recent backups granular and older backups sparse. mysqldump is the standard tool for logical MySQL backups — it exports the entire database as a SQL file containing CREATE TABLE and INSERT statements that can be replayed on any MySQL server. The minimum viable backup command is mysqldump -u root -p helloadmin > backup.sql, but a production-grade command adds --single-transaction (consistent snapshot for InnoDB tables without locking), --routines (stored procedures and functions), --triggers, --add-drop-table (allows clean restore without manually dropping tables), and pipes through gzip to compress the output. The backup file should be named with a timestamp — helloadmin-$(date +%Y%m%d-%H%M%S).sql.gz — and stored in a directory outside the web root with permissions restricted to the backup user. A cron job scheduled with crontab -e runs the backup script daily at 02:00 AM: 0 2 * * * /usr/local/bin/wp-backup.sh. Retention policy is enforced by a find command in the same script that deletes backup files older than 30 days: find /backups/ -name "*.sql.gz" -mtime +30 -delete. Offsite storage is critical — on-server backups are lost if the server is compromised or the disk fails. The backup script should rsync or rclone the backup file to an S3 bucket, a remote SFTP server, or another cloud storage provider immediately after creation. Restoring from a mysqldump backup uses mysql -u root helloadmin < backup.sql after decompressing with gunzip, or in a single pipeline: gunzip < backup.sql.gz | mysql -u root helloadmin. The database optimization post should be run after a restore to refresh index statistics on the imported data.

Problem: A WordPress site has no automated database backups — when a plugin update corrupts the wp_options table, recovery requires restoring from a three-week-old cPanel backup that loses all content created since then.

Solution: Write a Bash backup script that runs mysqldump --single-transaction with gzip compression, names files with timestamps, enforces a 30-day retention policy with find -delete, syncs to offsite storage, and schedules it with a daily cron job.

#!/usr/bin/env bash
# /usr/local/bin/wp-backup.sh
set -euo pipefail

DB_NAME="helloadmin"
DB_USER="root"
DB_PASS=""  # use ~/.my.cnf for password-less auth on production
BACKUP_DIR="/var/backups/wordpress"
REMOTE_DEST="s3://my-bucket/wp-backups/"  # rclone remote:path
RETAIN_DAYS=30
NOW=$(date +%Y%m%d-%H%M%S)
FILE="$BACKUP_DIR/${DB_NAME}-${NOW}.sql.gz"

mkdir -p "$BACKUP_DIR"

# 1. Dump and compress
echo "Dumping $DB_NAME..."
mysqldump \
    --user="$DB_USER" \
    --single-transaction \
    --routines \
    --triggers \
    --add-drop-table \
    "$DB_NAME" | gzip -9 > "$FILE"

echo "Backup saved: $FILE ($(du -sh "$FILE" | cut -f1))"

# 2. Copy to offsite storage (requires rclone configured)
if command -v rclone &> /dev/null; then
    echo "Uploading to $REMOTE_DEST ..."
    rclone copy "$FILE" "$REMOTE_DEST" --quiet
    echo "Upload complete."
fi

# 3. Enforce retention policy — delete backups older than RETAIN_DAYS
echo "Removing backups older than $RETAIN_DAYS days..."
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +"$RETAIN_DAYS" -delete

echo "Backup complete."

# ── Add to crontab (crontab -e): ────────────────────────────────────────
# 0 2 * * * /usr/local/bin/wp-backup.sh >> /var/log/wp-backup.log 2>&1

# ── ~/.my.cnf for password-less auth (chmod 600): ───────────────────────
# [mysqldump]
# user=root
# password=YOUR_PASSWORD

# Restore from a backup

# Decompress and restore in one pipeline:
gunzip < /var/backups/wordpress/helloadmin-20230628-020000.sql.gz | \
    mysql --user=root helloadmin

# Or restore via WP-CLI (handles the connection string from wp-config.php):
gunzip /var/backups/wordpress/helloadmin-20230628-020000.sql.gz
wp db import /var/backups/wordpress/helloadmin-20230628-020000.sql

# After restore: flush caches and refresh index statistics
wp cache flush
wp rewrite flush
mysql --user=root helloadmin -e "
    ANALYZE TABLE wp_posts, wp_postmeta, wp_options, wp_term_relationships;
"

NOTE: Store database credentials for the backup script in ~/.my.cnf (chmod 600) rather than in the script itself — this prevents the password from appearing in process listings (ps aux) and shell history. On servers with multiple MySQL users, create a dedicated backup@localhost user with only SELECT, LOCK TABLES, SHOW VIEW, and TRIGGER privileges rather than using the root account for backups.

Related posts