Manage Mysql Database Using Wp Cli
How to Manage your MySQL/Mariadb Database Using WP-CLI
Wordpress depends on the mysql database to store content and deliver performance. The role of the database is central to the performance of Wordpress sites and a slow database can cause the site to load slower. In a previous post, we have discussed plugins and methods to manage, clean up and maintain as well as backup your Wordpress database. It is a good practice to care for your database and maintain it so that your Wordpress loads faster.
Wordpress is the favorite CMS of millions around the globe and powers a large part of the entire web. The WP CLI or the Wordpress Command Line Interface is a command line tool that allows you to perform several wordpress related tasks including those related to the mysql database.
When you install Wordpress, you also need to install a database (Mariadb or mysql) to run your website. If ever you need to check or maintain your Wordpress mysql database, you can do it using various methods like with the help of a plugin or by logging in to phpmyadmin or with the help of the CLI that allows you to carry out several tasks like optimizing and resetting your database as well as list the tables included and so on.
Several times when you need to carry out such tasks like a search and replace to replace a particular string inside the database or to optimize or repair tables, you will not need to use a plugin or do the tasks through phpmyadmin. If you can run terminal commands, you can use the WP CLI to manage the database related tasks and with the help of some very simple commands.
In this post, we will list the WP CLI commands that can help you carry out a large range of database related tasks with examples. If you have ever run sql queries from the PhpMyAdmin, you can use the same queries using the WP-CLI:
- List tables in the database
If you need to check out the tables included in your database, you can get a list that includes all the names using the WP CLI. You can obtain the list by running the command wp db tables.
However, you will also need to use a few flags in certain cases to get a detailed or filtered list of tables. For example, while this command will simply produce a list of tables, it will exclude the tables that are created by the plugins like Yoast or other plugins.
To list all the tables, you will need to add - -all-tables, which will yield and output that includes all the tables inside the database.
For example:
wp_actionscheduler_actions
wp_actionscheduler_claims
wp_actionscheduler_groups
wp_actionscheduler_logs
wp_aiowps_audit_log
wp_aiowps_debug_log
wp_aiowps_events
wp_aiowps_global_meta
wp_aiowps_logged_in_users
wp_aiowps_login_lockdown
wp_aiowps_message_store
wp_aiowps_permanent_block
wp_commentmeta
wp_comments
wp_defender_audit_log
wp_defender_email_log
wp_defender_lockout
wp_defender_lockout_log
wp_defender_scan
wp_defender_scan_item
wp_links
wp_options
wp_postmeta
wp_posts
wp_rank_math_internal_links
wp_rank_math_internal_meta
wp_term_relationships
wp_term_taxonomy
wp_termmeta
wp_terms
wp_usermeta
wp_users
wp_wpil_report_links
wp_yoast_indexable
wp_yoast_indexable_hierarchy
wp_yoast_migrations
wp_yoast_primary_term
wp_yoast_seo_links
Otherwise, the list you will receive will be shorter than the full list. If you only need a list of the core tables, run:
If you have a multisite installation, and you want to list all the tables included in it, you will need to run the following command:
wp db tables --network
The default output format is list, but you can change it to CSV using the - -format=csv flag, such as:
wp db tables --all-tables --format=csv
You will receive an output in CSV format like:
Wp_actionscheduler_actions,wp_actionscheduler_claims,wp_actionscheduler_groups,wp_actionscheduler_logs,wp_aiowps_audit_log,wp_aiowps_debug_log,wp_aiowps_events,wp_aiowps_global_meta,wp_aiowps_logged_in_users,wp_aiowps_login_lockdown,wp_aiowps_message_store,wp_aiowps_permanent_block,wp_commentmeta,wp_comments,wp_defender_audit_log,wp_defender_email_log,wp_defender_lockout,wp_defender_lockout_log,wp_defender_scan,wp_defender_scan_item,wp_links,wp_options,wp_postmeta,wp_posts,wp_rank_math_internal_links,wp_rank_math_internal_meta,wp_term_relationships,wp_term_taxonomy,wp_termmeta,wp_terms,wp_usermeta,wp_users,wp_wpil_report_links,wp_yoast_indexable,wp_yoast_indexable_hierarchy,wp_yoast_migrations,wp_yoast_primary_term,wp_yoast_seo_links
To list tables for a single site without the shared tables like ‘wp_users’, use the flag - -scop=blog:
wp db tables - -scope=blog
- Retrieve information about columns in a database table
Suppose you need to learn about all the columns in a particular table inside your wordpress database. The command required to achieve this is the wp db columns. You will need to add the table name whose details you want. For example, we want to learn about the columns in the table wp_posts.
Wp db columns wp_posts
It will provide you an output like the following:
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------------+------+-----+---------------------+----------------+
| ID | bigint(20) unsigned | NO | PRI | | auto_increment |
| post_author | bigint(20) unsigned | NO | MUL | 0 | |
| post_date | datetime | NO | | 0000-00-00 00:00:00 | |
| post_date_gmt | datetime | NO | | 0000-00-00 00:00:00 | |
| post_content | longtext | NO | MUL | | |
| post_title | text | NO | MUL | | |
| post_excerpt | text | NO | MUL | | |
| post_status | varchar(20) | NO | | publish | |
| comment_status | varchar(20) | NO | | open | |
| ping_status | varchar(20) | NO | | open | |
| post_password | varchar(255) | NO | | | |
| post_name | varchar(200) | NO | MUL | | |
| to_ping | text | NO | | | |
| pinged | text | NO | | | |
| post_modified | datetime | NO | | 0000-00-00 00:00:00 | |
| post_modified_gmt | datetime | NO | | 0000-00-00 00:00:00 | |
| post_content_filtered | longtext | NO | | | |
| post_parent | bigint(20) unsigned | NO | MUL | 0 | |
| guid | varchar(255) | NO | | | |
| menu_order | int(11) | NO | | 0 | |
| post_type | varchar(20) | NO | MUL | post | |
| post_mime_type | varchar(100) | NO | | | |
| comment_count | bigint(20) | NO | | 0 | |
+-----------------------+---------------------+------+-----+---------------------+----------------+
The above output is in a tabular format. However, if you want, you can get the output in csv format using the - - format=csv flag.
wp db columns wp_posts - -format=csv
The command also supports json and yaml formats. For example, here is the same output in yaml format.
-
Field: ID
Type: bigint(20) unsigned
Null: 'NO'
Key: PRI
Default: null
Extra: auto_increment
Field: post_author
Type: bigint(20) unsigned
Null: 'NO'
Key: MUL
Default: "0"
Extra: ""
-
Field: post_date
Type: datetime
Null: 'NO'
Key: ""
Default: 0000-00-00 00:00:00
Extra: ""
-
Field: post_date_gmt
Type: datetime
Null: 'NO'
Key: ""
Default: 0000-00-00 00:00:00
Extra: ""
-
Field: post_content
Type: longtext
Null: 'NO'
Key: MUL
Default: null
Extra: ""
-
Field: post_title
Type: text
Null: 'NO'
Key: MUL
Default: null
Extra: ""
- Check the status of tables in a database
Suppose you want to check the status of all the tables in a database. You can do so with the wp db check command.
$ wp db check
wp_db.wp_actionscheduler_actions OK
wp_db.wp_actionscheduler_claims OK
wp_db.wp_actionscheduler_groups OK
wp_db.wp_actionscheduler_logs OK
wp_db.wp_aiowps_audit_log OK
wp_db.wp_aiowps_debug_log OK
wp_db.wp_aiowps_events OK
wp_db.wp_aiowps_global_meta OK
wp_db.wp_aiowps_logged_in_users OK
wp_db.wp_aiowps_login_lockdown OK
wp_db.wp_aiowps_message_store OK
wp_db.wp_aiowps_permanent_block OK
wp_db.wp_commentmeta OK
wp_db.wp_comments OK
Success: Database checked.
- Delete all tables matching the current site prefix
If you want to delete all the tables that match the current site prefix listed inside the wp-config.php file, you can ruin the wp db clean command. Do not forget to take a backup before making such changes.
wp db clean
This will run DROP_TABLE for each table with the same prefix as the $table_prefix specified inside the wp-config.php. Suppose you have specified wp1 as the table prefix when installing wordpress, all the tables matching this prefix will be deleted when you run the above command.
- Open mysql console
Suppose you want to open the mysql console, then you have two ways to do it and one of them is the wp cli.
$ wp db cli
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3884
Server version: 10.6.16-MariaDB-0ubuntu0.22.04.1 Ubuntu 22.04
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [wpi_db]>
Now, you can run mysql commands. To exit the mysql console, just type exit; .
- Create database using WP CLI
If you want to create a database and you have not created it normally as you do through the mysql console. You can create one using the wp-cli. However, you will need to specify the DB_USER, DB_NAME, DB_HOST and DB_PASSWORD inside the wp-config.php file based on which the CLI will create a new database with the following command:
wp db create
- Delete database using WP-CLI
Suppose you want to delete the database. You will need to run the following command to delete the database:
wp db drop - -yes
The CLI uses the credentials provided inside the wp-config.php file to run DROP_DATABASE SQL statement. The - -yes flag is used to confirm that you want to run the command. However, do not forget to take a backup before you run the command since in case you want to revert the changes.
- Export and Import database
Managing the database is a lot easier with the WP-CLI. You can easily take a backup and restore from the backup with a single command using the CLI. Suppose, you are planning to maintain your database and before that you want to take a backup. All you need to do is to export the table and you can further select to include or exclude specific tables from the backup you want to keep. You can also export to Stdout or standard output. To export the entire database:
wp db export
Success: Exported to 'wordpress_db-2024-04-20-3eea30f.sql'
You will receive an output that the database has been successfully exported. However, you can manage more with this command such as including specific tables or excluding some.
For example, we can exclude two tables and export the rest of the database with the following command:
wp db export --exclude_tables=wp_options,wp_users
In the above command, we have excluded wp_options and wp_users tables. Now, we will exclude all the tables and include only two tables:
wp db export --tables=wp_options,wp_users
So, this was how you could export tables from the database. In some cases, you might need to import an existing backup of a database.
You can import a database from a file or from STDIN using the following command:
(Run wp db import followed by the name of the sql file.)
wp db import wordpress_dbase.sql
- Check out the database prefix
If you have forgotten the database prefix and want to check it out, all you need to do is to change the directory to your website’s root directory and then run wp db prefix, such as:
cd /var/www/html
wp db prefix
The above command will output the prefix you have used when creating the database.
- Run database queries
You can also run the database queries using WP-cLi just like you do from the mysql console. For example, you want to check the status of all tables inside your database. You can run the command wp db query followed by “mysql-query”, such as:
wp db query "CHECK TABLE $(wp db tables | paste -s -d, -);"
Instead of the typing the whole query, you can save the query in a file and run it from the CLI:
wp db query < debug.sql
In this way, you can use the WP CLI for executing mysql database queries without the need to enter the mysql console or phpmyadmin.