WordPress Database Backup and Restore with WP-CLI and mysqldump

Regular database backups are non-negotiable. For WordPress, the two most reliable CLI tools are WP-CLI’s wp db subcommand (which reads credentials from wp-config.php automatically) and the standard mysqldump utility. Here’s the practical reference for both.

Problem: How do you reliably back up and restore a WordPress database from the command line — for local development workflows, site migrations, or scheduled server backups?

Solution: Use wp db export for a WP-CLI-aware dump that reads credentials from wp-config.php, or use mysqldump directly for more control over compression and output format. Restore with wp db import or mysql < dump.sql.

WP-CLI (recommended — reads credentials automatically):

# Export the full database
wp db export backup-$(date +%Y-%m-%d).sql

# Export specific tables only
wp db export --tables=wp_posts,wp_postmeta backup-posts.sql

# Import / restore
wp db import backup-2019-02-20.sql

# Check database for corruption
wp db check

# Repair tables
wp db repair

# Open a MySQL shell (for ad-hoc queries)
wp db cli

mysqldump directly:

# Full database export
mysqldump -u root -p wordpress > backup.sql

# Compressed export
mysqldump -u root -p wordpress | gzip > backup-$(date +%Y%m%d).sql.gz

# Restore from compressed dump
gunzip < backup-20190220.sql.gz | mysql -u root -p wordpress

# Export without CREATE TABLE statements (data only)
mysqldump --no-create-info -u root -p wordpress > data-only.sql

# Export structure only (no data)
mysqldump --no-data -u root -p wordpress > structure.sql

Automated nightly backup via cron (writes to a rotating directory):

#!/bin/bash
# /usr/local/bin/wp-backup.sh
BACKUP_DIR="/var/backups/wordpress"
SITE_PATH="/var/www/html"
DATE=$(date +%Y-%m-%d)

mkdir -p "$BACKUP_DIR"

# Database
cd "$SITE_PATH" && wp db export - | gzip > "$BACKUP_DIR/db-$DATE.sql.gz"

# Keep only the last 14 days
find "$BACKUP_DIR" -name "db-*.sql.gz" -mtime +14 -delete

# Add to cron: 0 2 * * * /usr/local/bin/wp-backup.sh

NOTE: A local backup is not a real backup — if the server fails, the backup goes with it. Always copy backups off-site: to Amazon S3, Google Cloud Storage, or even an SFTP server. Tools like aws s3 cp or rclone make this easy to add to the backup script above.