WP-CLI Database Backup: Complete Command Guide

WP-CLI provides powerful command-line database management for WordPress. Command-line backups enable automation, server-side efficiency, and integration with deployment workflows. This complete guide teaches WP-CLI database backup, import, optimization, and automated scheduling for professional WordPress management.

Why Use WP-CLI for Database Backups

Advantages Over GUI Methods:

Scriptable and automatable
Faster for large databases
No PHP timeout limits
Server resource efficient
Integration with cron jobs
Remote execution capability

WP-CLI essential for professional WordPress workflows.

Installing WP-CLI

Installation on Linux/Mac:

curl -O https://raw.githubusercontent.com/wp-cli/builds/gh-pages/phar/wp-cli.phar
chmod +x wp-cli.phar
sudo mv wp-cli.phar /usr/local/bin/wp

Verify Installation:

wp --info

Installation on Windows: Download from wp-cli.org and add to PATH.

Basic Database Export

Export complete database to SQL file:

wp db export backup.sql

This creates backup.sql in current directory.

Export with Automatic Filename:

wp db export

Generates timestamped filename automatically.

Export to Specific Directory:

wp db export /backups/database-$(date +%Y%m%d).sql

Exporting Specific Tables

Export only certain tables instead of entire database:

wp db export --tables=wp_posts,wp_postmeta backup.sql

Exclude Specific Tables:

wp db export --exclude_tables=wp_comments,wp_commentmeta backup.sql

Useful for excluding large temporary tables.

Database Import

Import SQL file to restore database:

wp db import backup.sql

Warning: This replaces existing database content. Backup current database first.

Database Optimization

Optimize database tables via WP-CLI:

wp db optimize

Runs OPTIMIZE TABLE on all WordPress tables, defragmenting and improving performance.

Database Repair

Repair corrupted tables:

wp db repair

Equivalent to MySQL REPAIR TABLE command for all WordPress tables.

Database Search and Replace

Update URLs or text across entire database:

wp search-replace 'http://olddomain.com' 'https://newdomain.com'

Essential for domain migrations and URL structure changes.

Dry Run (test without changes):

wp search-replace 'http://olddomain.com' 'https://newdomain.com' --dry-run

Database Query Execution

Execute custom SQL queries:

wp db query "SELECT * FROM wp_posts WHERE post_status='publish' LIMIT 10"

From SQL File:

wp db query < custom-query.sql

Database Statistics

View database size and table information:

wp db size --tables

Shows size of each table, useful for identifying bloat.

Automated Backup Script

Create comprehensive backup script:

#!/bin/bash
# WordPress Database Backup Script

# Configuration
SITE_PATH="/var/www/html"
BACKUP_DIR="/backups/wordpress"
DATE=$(date +%Y%m%d-%H%M)
RETENTION_DAYS=30

# Create backup directory
mkdir -p $BACKUP_DIR

# Navigate to WordPress directory
cd $SITE_PATH

# Export database
wp db export $BACKUP_DIR/database-$DATE.sql

# Compress backup
gzip $BACKUP_DIR/database-$DATE.sql

# Remove backups older than retention period
find $BACKUP_DIR -name "database-*.sql.gz" -mtime +$RETENTION_DAYS -delete

# Log completion
echo "Database backup completed: database-$DATE.sql.gz"

Make Executable:

chmod +x backup-script.sh

Scheduling with Cron

Automate daily backups at 2 AM:

crontab -e

Add line:

0 2 * * * /path/to/backup-script.sh >> /var/log/wp-backup.log 2>&1

Remote Database Backup

Backup remote WordPress database via SSH:

ssh user@remote-server 'cd /var/www/html && wp db export -' > remote-backup.sql

The - outputs to stdout, piped to local file.

Database Backup with Cloud Upload

Backup and upload to Amazon S3:

#!/bin/bash
DATE=$(date +%Y%m%d-%H%M)
BACKUP_FILE="database-$DATE.sql.gz"

# Export and compress
wp db export - | gzip > $BACKUP_FILE

# Upload to S3
aws s3 cp $BACKUP_FILE s3://my-backups/wordpress/

# Remove local copy
rm $BACKUP_FILE

Error Handling in Scripts

Add error checking to backup scripts:

#!/bin/bash
set -e  # Exit on error

trap 'echo "Backup failed at line $LINENO"' ERR

wp db export backup.sql

if [ $? -eq 0 ]; then
    echo "Backup successful"
else
    echo "Backup failed"
    exit 1
fi

Differential Backups

Create differential backups comparing to baseline:

# Full baseline backup
wp db export baseline.sql

# Later, export only changed tables
wp db export --tables=$(wp db tables --format=csv | grep -v '^#') incremental.sql

Database Backup Verification

Verify backup integrity after creation:

# Test if SQL file is valid
mysql --execute="source backup.sql" test_database

# Check file size
if [ -s backup.sql ]; then
    echo "Backup file not empty"
else
    echo "Backup file empty - ERROR"
fi

Multi-Site Database Backups

For WordPress Multisite networks:

# Export all network tables
wp db export --network

# Export specific site
wp db export --url=site2.example.com

Performance Considerations

Large Database Optimization:

Export during low-traffic periods
Use --skip-extended-insert for better import reliability
Split very large databases by table groups

wp db export --skip-extended-insert large-backup.sql

Conclusion

WP-CLI provides powerful command-line database backup capabilities for WordPress. Master wp db export, automate with cron jobs, implement error handling, and integrate with cloud storage for professional database management. Command-line backups offer speed, automation, and flexibility impossible with GUI tools, essential for managing multiple WordPress sites efficiently.

External Links

Call to Action

Command-line backups need reliable storage. Backup Copilot Pro integrates WP-CLI with automated cloud redundancy. Professional command-line backup management—start your free 30-day trial today!