The WordPress Command Line Interface (WP-CLI) Tool and its set of commands have always formed a powerful mechanism for managing WordPress websites.
The most optimal way to access and manage your WordPress MySQL database has always been a topic of debate among WordPress developers.
In this article, we mainly focus on WP-CLI database commands that you can use to easily manage and optimize your WordPress database.
Managed WordPress Hosting Plans
Before we get started, you may refer to the Managed WordPress Hosting page to get an idea of our plans if you are in the market for a web hosting provider.
Backing Up & Restoring WordPress Database
Exporting Your WordPress Database to a SQL File
# Export your WordPress database to an .sql file. $ wp db export … Success: Exported to 'ad067e60_ae743a-2022-01-18-f9de130.sql'.
Exporting Your WordPress Database to a SQL File Excluding Specific Tables
# Skip certain tables from the exported WordPress database.$ wp db export --exclude_tables=wp_options,wp_users…Success: Exported to 'ad067e60_ae743a-2022-01-18-e9afd56.sql'.
Importing a Database from a SQL File to a WordPress Database
# Import from .sql file to WordPress database.$ wp db import ad067e60_ae743a-2022-01-18-f9de130.sql…Success: Imported from 'ad067e60_ae743a-2022-01-18-f9de130.sql'.
Managing & Optimizing the WordPress Database
Creating a New WordPress Database
# Create a new WordPress database using the credentials (DB_HOST, DB_NAME, DB_USER and DB_PASSWORD) mentioned in wp-config.php. $ wp db create … Success: Database created.
Dropping an Existing WordPress Database
# Drop an existing WordPress database. $ wp db drop --yes … Success: Database dropped.
Dropping Tables of Your WordPress Database
# Drop the existing tables in your WordPress database. $ wp db clean --yes … Success: Tables dropped.
Resetting the Current WordPress Database & Removing All Tables
# Reset the current WordPress database, removing all tables. $ wp db reset --yes … Success: Database reset.
Opening the MySQL Console to Access Your WordPress MySQL Database
# Open the MySQL Console and access your WordPress MySQL database. $ wp db cli
Executing a Query within Your WordPress Database
# Execute a SQL query within your WordPress MySQL database. $ wp db query ‘query’
Executing a Query with an Example Using SELECT & FROM &WHERE Statements
$ wp db query 'SELECT * FROM wp_options WHERE option_name like "%twentytwenty%"' --skip-column-name 142 theme_mods_twentytwentyone a:1:{s:18:"custom_css_post_id";i:-1;} yes
Checking, Repairing, & Optimizing Your WordPress Database
Checking Your Database for Errors
# Check your WordPress database for errors.$ wp db checkad067e60_ae743a.wp_commentmeta OKad067e60_ae743a.wp_comments OK…Success: Database checked.
Repairing Your Database Tables
# Some of the MySQL errors when running WordPress can be checked and fixed by repairing the database tables. $ wp db repair ad067e60_ae743a.wp_commentmeta ad067e60_ae743a.wp_comments … Success: Database repaired.
Optimizing Your WordPress Database Tables
# Optimize WordPress database tables periodically for better efficiency. $ wp db optimize ad067e60_ae743a.wp_commentmeta note : Table does not support optimize, doing recreate + analyze instead status : OK ad067e60_ae743a.wp_comments status : OK … Success: Database optimized.
Searching and Replacing Text Strings in Your WordPress Database
Searching for a Text String in Your WordPress Database
# Search through the database for the ‘newpost’ string $ wp db search newpost wp_posts:post_title 4:newpost
Searching in Your WordPress Database Using regex Commands
# Database can be searched using regex commands for better outputs. # Search through the database for the 'https?://' using regular expression with statistics (--stats). $ wp db search 'https?://8c36393ae4.nxcli.net' --regex --stats wp_options:option_value 1:https://8c36393ae4.nxcli.net wp_options:option_value 2:https://8c36393ae4.nxcli.net wp_posts:post_content … Success: Found 9 matches in 0.017s (0.016s searching). Searched 12 tables, 53 columns, 508 rows. 1 table skipped: wp_term_relationships.
Searching & Replacing a Text String in Your WordPress Database
# Search through the database for nexcess.com’ and replace it with ‘nexcess.net’.$ wp search-replace 'nexcess.com' 'nexcess.net'+------------------+-----------------------+--------------+------+| Table | Column | Replacements | Type |+------------------+-----------------------+--------------+------+| wp_commentmeta | meta_key | 0 | SQL || wp_posts | guid | 4 | SQL |…Success: Made 10 replacements. Please remember to flush your persistent object cache with `wp cache flush`.
Searching & Replacing a Text String in Your WordPress Database Using -dry run
# The search and replace command using wp search-replace is always better run with –dry-run to see expected results before making changes in the database.$ wp search-replace --dry-run nexcess.com' nexcess.net' –dry-run+------------------+-----------------------+--------------+------+| Table | Column | Replacements | Type |+------------------+-----------------------+--------------+------+| wp_commentmeta | meta_key | 0 | SQL || wp_commentmeta | meta_value | 0 | SQL || wp_options | option_value | 3 | PHP |…Success: 10 replacements to be made.
Other Useful Commands
Checking the WordPress Database Size
# Check the WordPress database size. $ wp db size +-----------------+----------+ | Name | Size | +-----------------+----------+ | ad067e60_ae743a | 704512 B | +-----------------+----------+
Checking the WordPress Database Table Names & Sizes
# Checks and displays table names and sizes from the WordPress database. $ wp db size --tables +-----------------------+---------+ | Name | Size| +-----------------------+---------+ | wp_commentmeta | 49152 B | | wp_comments | 98304 B | | wp_links | 32768 B | | wp_options | 98304 B | | wp_postmeta | 49152 B | | wp_posts | 81920 B | | wp_term_relationships | 32768 B | | wp_term_taxonomy | 49152 B | | wp_termmeta | 49152 B | | wp_terms | 49152 B | | wp_usermeta | 49152 B | | wp_users | 65536 B | +-----------------------+---------+
Checking WordPress Database Field Names & Details
# Display information such as field name, data type, key, and other information about a given table.$ wp db columns <table>$ wp db columns wp_options+--------------+---------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------------+---------------------+------+-----+---------+----------------+| option_id| bigint(20) unsigned | NO | PRI | | auto_increment || option_name | varchar(191) | NO | UNI | | || option_value | longtext | NO | | | || autoload | varchar(20) | NO | MUL | yes | |+--------------+---------------------+------+-----+---------+----------------+
Common Questions
Common Question #1: Why am I getting a PHP Fatal Error while running wp commands?
This error mainly occurs when the variables or database credentials specified in the wp-config.php file are wrong. Check and update database credentials and any variables set.
Common Question #2: Why am I getting wrong output due to the plugins or themes being used?
Sometimes you may get different output than expected due to WordPress extensions (plugins, themes, must-use plugins, drop-ins).
You can resolve this by bypassing a single plugin or theme (--skip-plugins=akismet) or by skipping them entirely (wp --skip-plugins --skip-themes).
Common Question #3: Why am I have a Memory Exhausted Issue?
By default, sites hosted on Nexcess will already have very high PHP default memory values set and they should be enough.
You can check out the default values here. However, if you face Memory Exhaustion issues, you can increase the PHP values by updating values in the ‘.user.ini’ file to a certain limit. With regard to these fixes, you can always get help from our support team, the Helpful Humans at Nexcess!
Common Question #4: Why am I getting the "W3 Total Cache Error: Some files appear to be missing or out of place"?
W3 Total Cache object caching can cause this issue. Disabling object caching and optionally removing wp-content/object-cache.php should fix this.
Useful WordPress Links for Developers & Admins
- https://developer.wordpress.org/cli/commands/db/search/
- https://developer.wordpress.org/cli/commands/db/size/
- https://developer.wordpress.org/cli/commands/db/search/#options
- https://help.nexcess.net/74095-wordpress/locating-your-ssh-credentials-in-managed-wordpress-and-managed-woocommerce-hosting
- https://wp-cli.org/#using
