Regular database backups are the last line of defence when a WordPress site is compromised, corrupted by a failed update, or accidentally has content deleted — and they must be tested periodically to confirm they can actually be restored. mysqldump is the standard tool for logical MySQL backups, producing portable SQL files that can be imported on any MySQL-compatible database server regardless of the binary format or storage engine version. Running mysqldump without the --single-transaction flag on InnoDB tables risks an inconsistent snapshot — requests modifying the database during the dump can produce a backup where some tables reflect pre-commit state and others reflect post-commit state. --single-transaction opens a consistent read view at the start of the dump without locking tables, making it safe to run on live production databases during business hours. Compressing the dump immediately with gzip reduces disk usage by 70–90 percent for typical WordPress databases, since SQL text compresses extremely well. Rotating old backups automatically — deleting files older than a threshold — prevents unbounded disk growth without requiring manual maintenance. Offloading backups to object storage such as AWS S3 or Backblaze B2 via rclone or the AWS CLI ensures the backup survives a total server failure or datacenter outage. Encrypting the backup file before upload with gpg --symmetric protects database credentials and user data at rest in the storage bucket. A monthly restore test — importing the dump into a staging database and verifying WordPress loads — is the only way to confirm the backup is actually usable. The MySQL slow query log post explains how to read the MySQL configuration file that also controls backup-relevant settings such as innodb_buffer_pool_size. The server hardening post covers OS-level security measures that protect the server where backups are stored locally before offloading to remote storage. Keep at least three backup generations on two different storage locations — local disk and remote object storage — following the 3-2-1 backup rule.
Problem: WordPress databases without automated, compressed, off-site backups are vulnerable to permanent data loss from server failure, ransomware, or accidental deletion — and unverified backups may be inconsistent or corrupt when a restore is needed.
Solution: Schedule a nightly cron job that runs mysqldump with --single-transaction, compresses the output with gzip, rotates local copies older than 7 days, and uploads the file to remote storage.
#!/bin/bash
# /usr/local/bin/wp-db-backup.sh
DB_NAME="helloadmin"
DB_USER="root"
DB_PASS=""
BACKUP_DIR="/var/backups/mysql"
REMOTE="s3:my-backup-bucket/mysql"
KEEP_DAYS=7
DATE=$(date +%Y-%m-%d_%H-%M)
FILE="${BACKUP_DIR}/${DB_NAME}_${DATE}.sql.gz"
mkdir -p "$BACKUP_DIR"
# Dump with consistent snapshot (no table locks on InnoDB)
mysqldump --user="$DB_USER" --password="$DB_PASS" --single-transaction --routines --triggers --hex-blob "$DB_NAME" | gzip -9 > "$FILE"
echo "Backup written: $FILE ($(du -sh "$FILE" | cut -f1))"
# Rotate local copies older than KEEP_DAYS
find "$BACKUP_DIR" -name "${DB_NAME}_*.sql.gz" -mtime +${KEEP_DAYS} -delete
# Upload to remote (requires rclone configured)
rclone copy "$FILE" "$REMOTE" --quiet && echo "Uploaded to $REMOTE"
# Add to crontab: crontab -e
# 0 2 * * * /bin/bash /usr/local/bin/wp-db-backup.sh >> /var/log/wp-db-backup.log 2>&1NOTE: Store the database password in a ~/.my.cnf file with mode 600 instead of in the script: create [mysqldump] section with password=yourpassword and omit --password from the command — this prevents the password from appearing in the process list visible to other users.