We use cookies to understand how you interact with our site, to personalize and streamline your experience, and to tailor advertising. By continuing to use our site, you accept our use of cookies and accept our Privacy Policy.
Contact Us
Contact Us
Sign in
Sign in
Nexcess Logo

WP-CLI Commands for Your WordPress Database

February 16, 2022

Managing your WordPress database using the WordPress Command Line Interface (WP-CLI) tool is an efficient way to keep the site's database well maintained!

WordPress Command Line Interface (WP-CLI) Commands for Your WordPress Database

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 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 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 check
ad067e60_ae743a.wp_commentmeta                     OK
ad067e60_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

Success: Database repaired.

Optimizing Your WordPress Database Tables 

# Optimize WordPress database tables periodically for better efficiency.
$ wp db optimize
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
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

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

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

Consider Hosting with Nexcess

Hosting optimized for the industry's leading platforms, including Managed WordPress Hosting, Managed WooCommerce Hosting, and Managed Magento Hosting:

Build Better Sites and Stores With Fully Managed Hosting from Nexcess 

Faster Speeds, Stronger Security, Inherent Scalability and Trusted Support.

Our range of hosting plans caters to any business scale. We do all the heavy lifting for you to focus on growing your business. In addition, we automatically update ore components and plugins to the latest version and ensure your hosting environment is properly optimized, secured, and updated.

Nexcess infrastructure was specially designed to keep up the best speed and performance in the industry. No traffic surge can threaten your website to go down thanks to our autoscaling technology which adds more resources to your server to handle the load. In addition, we offer always-on security monitoring and support from web hosting experts 24/7/365 and a built-in CDN with 22 locations and advanced caching for ultra-fast loading.

All hosting plans include The Nexcess 30-Day Web Hosting Money Back Guarantee.

Need a No-Code Website Building Solution? 

Are you on an accelerated schedule and already ready to move forward? If you need to get started with your Nexcess StoreBuilder Solution sooner rather than later, check out these resources:

Next Steps with a Nexcess Cloud Hosting Solution?

Read more about the Fully Managed Cloud Environment by Nexcess and its benefits for your business.

A Cloud Hosting Solution That Lets You Do Business Your Way 

We believe in the promise of cloud: scalability, security, performance, and ease of use. Together with our team, clients, and partners, we’ve built something better.

Choose From Multiple Applications 

Whether you’re a small business or a high-traffic eCommerce store, our cloud hosting solutions are designed around your needs: auto scaling, PCI compliance, and comprehensive development tools provide you with maximum dynamic flexibility in a fully managed cloud environment.

We make applications easy with environment optimizations for:

And there are many more!

We also have a variety of Nexcess support articles about how best to implement our product offerings housed in the Nexcess Knowledge Baseincluding how to get your site going with a number of different configuration options. These resources include a great article on setting this up for Migrating to Nexcess with managed WordPress and managed WooCommerce hosting

Next Steps with Fully Managed WordPress Hosting?

Read more about the Fully Managed WordPress Hosting and its benefits for your business.

Build Better Websites with Fully Managed WordPress Hosting 

It’s hosting optimized for WordPress. That means a faster, more secure and scalable website. Smart monitoring tools are built-in to help you keep it that way. 

It’s why WordPress Users Trust Nexcess Hosting.

We also have a variety of Nexcess support articles about WordPressincluding how to get your site going with a number of different configuration options. These resources include a great article on setting this up for Migrating to Nexcess with managed WordPress and managed WooCommerce hosting

24-Hour Assistance

If you need any assistance with the above-mentioned, don't hesitate to reach out. For 24-hour assistance any day of the year, Nexcess customers can contact our support team by email or through your Client Portal.

Why Choose Nexcess?

Because we're different! Chris Lema captures "the why" in his passionate and stirring recount of a Nexcess support-related story.

Useful YouTube > Nexcess Channel Links

Resources for More Information

Need more help? The Applications, WooCommerceand WordPress sections within the Nexcess Knowledge Base are important resources for those seeking additional knowledge. 

The Applications section also contains valuable insights for those seeking additional knowledge about our other various hosted applications and platforms. Check out our related video playlists and articles below:

New Customers: Fully Managed Hosting Solutions

Not a Nexcess customer yet? Check out our fully managed hosting solutions. The option to chat with an expert is also available.

Related Articles

Rosna Sudhakaran