Skip to main content

Database Operations

Overview

WP-CLI provides complete database management capabilities without phpMyAdmin or MySQL CLI. Create, export, import, query, and maintain your WordPress database with simple commands—perfect for migrations, backups, and troubleshooting.

Understanding WordPress Database Management​

The WordPress database stores everything: posts, pages, users, settings, and plugin data. WP-CLI's wp db commands give you direct control over:

  • Database lifecycle: Create, drop, reset
  • Backup & restore: Export and import
  • Queries: Run custom SQL
  • Maintenance: Check, repair, optimize tables

Database Creation & Destruction​

Create Database​

# Create database using wp-config.php credentials
wp db create

What it does:

  • Reads database name from wp-config.php
  • Creates the database if it doesn't exist
  • Uses credentials defined in config

Use cases:

  • Fresh WordPress installation
  • Automated deployment scripts
  • Resetting development environment

Drop Database​

# Delete database (requires confirmation)
wp db drop

# Skip confirmation prompt
wp db drop --yes
Destructive Operation

This permanently deletes your entire database. Always backup first!

Use cases:

  • Removing test databases
  • Clean slate before reinstall
  • Automated testing cleanup

Reset Database​

# Drop and recreate database
wp db reset --yes

What it does:

  • Drops the existing database
  • Creates a new empty database
  • Faster than drop + create separately

Use cases:

  • Quick development environment reset
  • Starting fresh after testing
  • Automated test suite cleanup

Database Export (Backup)​

Basic Export​

# Export to default filename (wordpress.sql)
wp db export

# Export to custom filename
wp db export backup.sql

# Export with timestamp
wp db export backup-$(date +%Y%m%d-%H%M%S).sql

Export with Options​

# Export with DROP TABLE statements (safe overwrite on import)
wp db export backup.sql --add-drop-table

# Export with single transaction (reduces locking on live sites)
wp db export backup.sql --single-transaction

# Combine both (recommended for production)
wp db export backup.sql --add-drop-table --single-transaction
Production Exports

Always use --single-transaction on live sites to minimize database locking and prevent downtime.

Export Specific Tables​

# Export only specific tables
wp db export users-backup.sql --tables=wp_users,wp_usermeta

# Export all tables except specific ones
wp db export content-only.sql --exclude_tables=wp_users,wp_usermeta

Use cases:

  • Migrating only user data
  • Backing up content without settings
  • Selective data transfer

Compressed Exports​

# Export and compress with gzip
wp db export backup.sql.gz

# Export and compress with bzip2
wp db export backup.sql.bz2

# Manual compression
wp db export - | gzip > backup.sql.gz

Benefits:

  • Saves disk space (70-90% reduction)
  • Faster transfers
  • Essential for large databases

Database Import (Restore)​

Basic Import​

# Import SQL file
wp db import backup.sql

# Import compressed file
wp db import backup.sql.gz

What it does:

  • Reads SQL file
  • Executes all statements
  • Restores database to backed-up state

Safe Import Workflow​

# 1. Backup current database first
wp db export before-import-$(date +%Y%m%d).sql

# 2. Import new database
wp db import production-backup.sql

# 3. Verify import
wp db check

# 4. If issues, restore original
# wp db import before-import-YYYYMMDD.sql

Import from Remote Source​

# Download and import in one command
curl https://example.com/backups/database.sql.gz | gunzip | wp db import -

# Import from S3
aws s3 cp s3://bucket/backup.sql.gz - | gunzip | wp db import -

Running SQL Queries​

Execute SQL Queries​

# Run a query
wp db query "SELECT COUNT(*) FROM wp_posts WHERE post_status='publish';"

# Query with cleaner output
wp db query "SELECT ID, post_title FROM wp_posts LIMIT 5;" --skip-column-names

# Multi-line query
wp db query "
  SELECT post_type, COUNT(*) as count 
  FROM wp_posts 
  WHERE post_status='publish' 
  GROUP BY post_type;
"

Common Query Examples​

# Count published posts
wp db query "SELECT COUNT(*) FROM wp_posts WHERE post_status='publish';"

# List all users
wp db query "SELECT ID, user_login, user_email FROM wp_users;"

# Find posts by author
wp db query "SELECT post_title FROM wp_posts WHERE post_author=1 AND post_status='publish';"

# Check database size
wp db query "
  SELECT 
    table_name AS 'Table',
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
  FROM information_schema.TABLES
  WHERE table_schema = DATABASE()
  ORDER BY (data_length + index_length) DESC;
"

Query from File​

# Execute SQL from file
wp db query < custom-query.sql

# With output to file
wp db query < query.sql > results.txt

Database Maintenance​

Check Database​

# Check all tables for errors
wp db check

What it checks:

  • Table structure integrity
  • Index corruption
  • Data consistency

Output example:

wp_posts                                   OK
wp_postmeta                                OK
wp_users                                   OK

Repair Database​

# Repair corrupted tables
wp db repair

When to use:

  • After server crash
  • Database corruption errors
  • "Table is marked as crashed" messages

What it does:

  • Attempts to fix corrupted tables
  • Rebuilds indexes
  • Recovers data when possible
Data Loss Risk

Repair may not recover all data. Always backup before repairing.

Optimize Database​

# Optimize all tables
wp db optimize

What it does:

  • Defragments tables
  • Reclaims unused space
  • Rebuilds indexes for better performance

Benefits:

  • Faster queries
  • Reduced disk usage
  • Improved database performance

When to run:

  • After deleting large amounts of data
  • Monthly maintenance
  • Before major updates

Real-World Scenarios​

Scenario 1: Complete Site Migration​

#!/bin/bash
# migrate-site.sh - Complete database migration workflow

# On source site
echo "Exporting source database..."
wp db export source-backup.sql --add-drop-table --single-transaction

# Transfer file to destination
scp source-backup.sql user@destination:/tmp/

# On destination site
echo "Importing database..."
wp db import /tmp/source-backup.sql

# Update URLs
echo "Updating URLs..."
wp search-replace 'https://oldsite.com' 'https://newsite.com' --precise

# Clear cache
wp cache flush

echo "Migration complete!"

Scenario 2: Automated Daily Backups​

#!/bin/bash
# daily-backup.sh

# Configuration
BACKUP_DIR="/backups/wordpress"
RETENTION_DAYS=30

# Create backup directory if it doesn't exist
mkdir -p $BACKUP_DIR

# Export database with timestamp
FILENAME="db-backup-$(date +%Y%m%d-%H%M%S).sql.gz"
wp db export "$BACKUP_DIR/$FILENAME" --add-drop-table --single-transaction

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

echo "Backup created: $FILENAME"

Add to crontab:

# Run daily at 2 AM
0 2 * * * /path/to/daily-backup.sh

Scenario 3: Clone Production to Staging​

#!/bin/bash
# clone-to-staging.sh

PROD_PATH="/var/www/production"
STAGING_PATH="/var/www/staging"

# Export production database
cd $PROD_PATH
wp db export /tmp/prod-clone.sql --add-drop-table --single-transaction

# Import to staging
cd $STAGING_PATH
wp db import /tmp/prod-clone.sql

# Update URLs for staging
wp search-replace 'https://example.com' 'https://staging.example.com' --precise

# Update staging-specific settings
wp option update blogname "Example Site (STAGING)"
wp option update admin_email "staging@example.com"

# Clean up
rm /tmp/prod-clone.sql

echo "Staging environment updated from production!"

Scenario 4: Emergency Database Recovery​

#!/bin/bash
# emergency-recovery.sh

echo "Starting emergency database recovery..."

# 1. Check database status
echo "Checking database..."
wp db check

# 2. Repair if needed
echo "Repairing database..."
wp db repair

# 3. Optimize tables
echo "Optimizing database..."
wp db optimize

# 4. Verify WordPress can connect
echo "Verifying WordPress connection..."
wp db query "SELECT 1;" > /dev/null && echo "✓ Database accessible" || echo "✗ Database connection failed"

# 5. Check critical tables
echo "Checking critical tables..."
for table in wp_posts wp_users wp_options; do
  COUNT=$(wp db query "SELECT COUNT(*) FROM $table;" --skip-column-names)
  echo "$table: $COUNT rows"
done

echo "Recovery complete!"

Scenario 5: Database Size Analysis​

#!/bin/bash
# analyze-database.sh

echo "=== WordPress Database Analysis ==="
echo ""

# Total database size
echo "Total Database Size:"
wp db query "
  SELECT 
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
  FROM information_schema.TABLES
  WHERE table_schema = DATABASE();
" --skip-column-names

echo ""

# Size by table
echo "Size by Table:"
wp db query "
  SELECT 
    table_name AS 'Table',
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
  FROM information_schema.TABLES
  WHERE table_schema = DATABASE()
  ORDER BY (data_length + index_length) DESC
  LIMIT 10;
"

echo ""

# Row counts
echo "Row Counts:"
wp db query "
  SELECT 
    table_name AS 'Table',
    table_rows AS 'Rows'
  FROM information_schema.TABLES
  WHERE table_schema = DATABASE()
  ORDER BY table_rows DESC
  LIMIT 10;
"

Advanced Database Operations​

Get Database Credentials​

# Show database name
wp db cli --print-command | grep -oP '(?<=--database=)[^ ]+'

# Get all DB info
wp config get DB_NAME
wp config get DB_USER
wp config get DB_HOST

Database CLI Access​

# Open MySQL CLI for WordPress database
wp db cli

# Run command in MySQL CLI
wp db cli --execute="SHOW TABLES;"

# Get MySQL command
wp db cli --print-command

Database Table Prefix​

# Get table prefix
wp db prefix

# List all tables with prefix
wp db tables

Best Practices​

1. Always Backup Before Destructive Operations​

# Before any risky operation
wp db export safety-backup-$(date +%Y%m%d-%H%M%S).sql

2. Use Appropriate Export Flags​

# For production sites (minimal locking)
wp db export backup.sql --single-transaction

# For portable backups (safe imports)
wp db export backup.sql --add-drop-table

# Best of both
wp db export backup.sql --add-drop-table --single-transaction

3. Compress Large Databases​

# Automatically compress
wp db export backup.sql.gz --add-drop-table --single-transaction

# Check size savings
ls -lh backup.sql*

4. Secure Backup Storage​

# Store outside web root
wp db export /backups/db-$(date +%Y%m%d).sql.gz

# Set restrictive permissions
chmod 600 /backups/db-*.sql.gz

# Encrypt sensitive backups
wp db export - | gpg --encrypt --recipient admin@example.com > backup.sql.gpg

5. Regular Maintenance Schedule​

# Weekly optimization (cron)
0 3 * * 0 cd /var/www/html && wp db optimize

# Monthly check and repair
0 4 1 * * cd /var/www/html && wp db check && wp db repair

Troubleshooting​

Export Fails​

# Check disk space
df -h

# Check database connection
wp db check

# Try exporting to stdout
wp db export - > backup.sql

# Check MySQL user permissions
wp db query "SHOW GRANTS;"

Import Fails​

# Check SQL file syntax
head -n 20 backup.sql

# Import with error logging
wp db import backup.sql 2>&1 | tee import-errors.log

# Import in smaller chunks (for large files)
split -l 10000 backup.sql chunk_
for file in chunk_*; do
  wp db query < $file
done

Database Connection Errors​

# Verify credentials
wp config get DB_NAME
wp config get DB_USER
wp config get DB_HOST

# Test connection
wp db check

# Check if database exists
wp db cli --execute="SHOW DATABASES;"

Quick Reference​

Essential Commands​

# Create/Drop/Reset
wp db create                                # Create database
wp db drop --yes                            # Delete database
wp db reset --yes                           # Drop and recreate

# Export
wp db export backup.sql                     # Basic export
wp db export backup.sql --add-drop-table    # With DROP TABLE
wp db export backup.sql --single-transaction # Minimal locking
wp db export backup.sql.gz                  # Compressed
wp db export backup.sql --tables=wp_users,wp_usermeta  # Specific tables

# Import
wp db import backup.sql                     # Import SQL file
wp db import backup.sql.gz                  # Import compressed

# Query
wp db query "SELECT * FROM wp_users;"       # Run SQL
wp db query < query.sql                     # From file
wp db cli                                   # Open MySQL CLI

# Maintenance
wp db check                                 # Check tables
wp db repair                                # Repair tables
wp db optimize                              # Optimize tables

# Info
wp db prefix                                # Get table prefix
wp db tables                                # List tables

Comparison: WP-CLI vs phpMyAdmin​

TaskWP-CLIphpMyAdmin
Export Database~5 seconds30-60 seconds
Import Database~10 seconds1-5 minutes
Automation✅ Fully scriptable❌ Manual only
Large Databases✅ No size limits⚠️ Upload limits
Works When Site Broken✅ Yes⚠️ Requires web access
Visual Interface❌ Command-line only✅ GUI
Complex Queries⚠️ Manual SQL✅ Query builder
Key Takeaway

WP-CLI excels at automation, speed, and handling large databases. Use it for backups, migrations, and scripted operations. Use phpMyAdmin when you need a visual interface for complex queries or data browsing.

Next Steps​