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 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
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 NexcessFaster 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 Base, including 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 WordPress, including 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
- Nexcess on YouTube - Home
- Nexcess on YouTube - Welcome to Your Nexcess Portal
- Nexcess on YouTube - Featured Videos
- Nexcess on YouTube - All Videos
- Nexcess on YouTube - All Playlists
Resources for More Information
Need more help? The Applications, WooCommerce, and 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:
- Managed WordPress by Nexcess - Video Playlist
- Managed WooCommerce by Nexcess - Video Playlist
- Managed Magento by Nexcess - Video Playlist
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
-
Migrating to Nexcess with managed WordPress and managed WooCommerce hosting
- Going live with your site in Managed WordPress and Managed WooCommerce hosting
- WordPress Database Size: Find WP DB Size with WP-CLI
- WordPress Database Search: WP DB Search with WP-CLI
- Tuning Your MySQL Database for Performance
