WordPress database import and export with WP-CLI

Each script will run automated commands from WP-CLI common to migrating your database from one environment to another. The import and export scripts allow for search and replace, user management, and plugin management. In your WordPress root directory, you will create two files:

db-export.sh
db-import.sh

Info

If it's been a while, update your WP-CLI binary with wp cli update. See the official WP-CLI docs for first-time installation.

If you get Error: /usr/local/bin/wp is not writable by current user., run it as sudo:

sudo /usr/local/bin/wp cli update

Usage

Update search variables at the start of the script:

SEARCH="https://dev.example.com"
REPLACE="https://www.example.com"

Update the IDs of the users you want to remain in the Administrator role after the migration, separated by spaces (1 is me, 2 is someone else):

ADMIN_IDS=(1 2)

Export database

db-export.sh:

#!/bin/bash

# Usage: bash db-export.sh filename.sql

# CAREFULLY REVIEW AND CHANGE VARS SPECIFIC TO THIS SITE
WP="php -d display_errors=0 -d max_execution_time=0 /usr/local/bin/wp"

if [[ -z "${1:-}" ]]; then
    echo "Must specify the file name. Usage: bash $0 filename.sql"
    exit 1
fi

echo "exporting db...."

${WP} db export "$1" --add-drop-table --single-transaction

get script

Import database

db-import.sh:

#!/bin/bash

# Usage: bash db-import.sh filename.sql

# CAREFULLY REVIEW AND CHANGE VARS SPECIFIC TO THIS SITE
WP="php -d display_errors=0 -d max_execution_time=0 /usr/local/bin/wp"

# dev to prod
SEARCH="https://dev.example.com"
REPLACE="https://www.example.com"

# separate users with spaces (1: me, 2: another user)
ADMIN_IDS=(1 2)

# Check if any variable is empty
if [[ -z "$SEARCH" || -z "$REPLACE" || ${#ADMIN_IDS[@]} -eq 0 ]]; then
    echo "Error: SEARCH, REPLACE, and/or ADMIN_IDS variables are not set."
    exit 1
fi

if [[ -z "${1:-}" || ! -f "$1" ]]; then
    echo "Must specify an existing dump file. Usage: bash $0 filename.sql"
    exit 1
fi

echo "importing db...."

${WP} db import "$1"

echo "search and replace..."

${WP} search-replace "$SEARCH" "$REPLACE" --report-changed-only --skip-columns=guid

echo "flushing rewrite rules and cache..."

${WP} rewrite flush
${WP} cache flush

echo "disabling dev plugins..."

${WP} plugin deactivate password-protected

echo "set to allow indexing..."

${WP} option update blog_public 1

echo "demoting everyone..."
${WP} user list --field=ID | grep -E '^[0-9]+$' | xargs -n1 -I{} ${WP} user set-role {} ''

echo "promoting admins..."
for admin_id in "${ADMIN_IDS[@]}"; do
    ${WP} user set-role "$admin_id" administrator
done

get script

Run from outside WordPress

The --path directive tells WP CLI where to find your WordPress installation. When working with other teams, or just for security reasons I put the scripts in my /home/ubuntu directory usually.

Set a new variable at the top:

WP_PATH="/var/www/example.com"

Add --path to the end of EACH ${WP} command:

${WP} db import "$1" --path="$WP_PATH"

Optional: exclude tables on export

To exclude certain plugin tables like All-In-One WP Security, Action Scheduler, etc., add --exclude_tables to the export line with:

--exclude_tables="$(${WP} db tables 'wp_aiowps_*' 'wp_actionscheduler_*' --all-tables --format=csv)"