Performs basic database operations using credentials stored in wp-config.php.
wp-cli/db-command =================
Performs basic database operations using credentials stored in wp-config.php.
Quick links: Using | Installing | Contributing | Support
Using
This package implements the following commands:
wp db
Performs basic database operations using credentials stored in wp-config.php.
~~~ wp db ~~~
EXAMPLES
# Create a new database. $ wp db create Success: Database created.
# Drop an existing database. $ wp db drop --yes Success: Database dropped.
# Reset the current database. $ wp db reset --yes Success: Database reset.
# Execute a SQL query stored in a file. $ wp db query < debug.sql
wp db clean
Removes all tables with $table_prefix from the database.
~~~ wp db clean [--dbuser=
Runs DROPTABLE for each table that has a $tableprefix as specified in wp-config.php.
OPTIONS
[--dbuser=
[--dbpass=
[--yes] Answer yes to the confirmation message.
[--defaults] Loads the environment's MySQL option files. Default behavior is to skip loading them to avoid failures due to misconfiguration.
EXAMPLES
# Delete all tables that match the current site prefix. $ wp db clean --yes Success: Tables dropped.
wp db create
Creates a new database.
~~~ wp db create [--dbuser=
Runs CREATEDATABASE SQL statement using DBHOST, DB_NAME, DBUSER and DBPASSWORD database credentials specified in wp-config.php.
OPTIONS
[--dbuser=
[--dbpass=
[--defaults] Loads the environment's MySQL option files. Default behavior is to skip loading them to avoid failures due to misconfiguration.
EXAMPLES
$ wp db create Success: Database created.
wp db drop
Deletes the existing database.
~~~ wp db drop [--dbuser=
Runs DROPDATABASE SQL statement using DBHOST, DB_NAME, DBUSER and DBPASSWORD database credentials specified in wp-config.php.
OPTIONS
[--dbuser=
[--dbpass=
[--yes] Answer yes to the confirmation message.
[--defaults] Loads the environment's MySQL option files. Default behavior is to skip loading them to avoid failures due to misconfiguration.
EXAMPLES
$ wp db drop --yes Success: Database dropped.
wp db reset
Removes all tables from the database.
~~~ wp db reset [--dbuser=
Runs DROPDATABASE and CREATEDATABASE SQL statements using DBHOST, DBNAME, DBUSER and DBPASSWORD database credentials specified in wp-config.php.
OPTIONS
[--dbuser=
[--dbpass=
[--yes] Answer yes to the confirmation message.
[--defaults] Loads the environment's MySQL option files. Default behavior is to skip loading them to avoid failures due to misconfiguration.
EXAMPLES
$ wp db reset --yes Success: Database reset.
wp db check
Checks the current status of the database.
~~~ wp db check [--dbuser=
Runs mysqlcheck utility with --check using DB_HOST, DBNAME, DBUSER and DB_PASSWORD database credentials specified in wp-config.php.
See docs for more details on the CHECK TABLE statement.
This command does not check whether WordPress is installed; to do that run wp core is-installed.
OPTIONS
[--dbuser=
[--dbpass=
[--
[--defaults] Loads the environment's MySQL option files. Default behavior is to skip loading them to avoid failures due to misconfiguration.
EXAMPLES
$ wp db check Success: Database checked.
wp db optimize
Optimizes the database.
~~~ wp db optimize [--dbuser=
Runs mysqlcheck utility with --optimize=true using DB_HOST, DBNAME, DBUSER and DB_PASSWORD database credentials specified in wp-config.php.
See docs for more details on the OPTIMIZE TABLE statement.
OPTIONS
[--dbuser=
[--dbpass=
[--
[--defaults] Loads the environment's MySQL option files. Default behavior is to skip loading them to avoid failures due to misconfiguration.
EXAMPLES
$ wp db optimize Success: Database optimized.
wp db prefix
Displays the database table prefix.
~~~ wp db prefix ~~~
Display the database table prefix, as defined by the database handler's interpretation of the current site.
EXAMPLES
$ wp db prefix wp_
wp db repair
Repairs the database.
~~~ wp db repair [--dbuser=
Runs mysqlcheck utility with --repair=true using DB_HOST, DBNAME, DBUSER and DB_PASSWORD database credentials specified in wp-config.php.
See docs for more details on the REPAIR TABLE statement.
OPTIONS
[--dbuser=
[--dbpass=
[--
[--defaults] Loads the environment's MySQL option files. Default behavior is to skip loading them to avoid failures due to misconfiguration.
EXAMPLES
$ wp db repair Success: Database repaired.
wp db cli
Opens a MySQL console using credentials from wp-config.php
~~~ wp db cli [--database=
Alias: connect
OPTIONS
[--database=
[--default-character-set=
[--dbuser=
[--dbpass=
[--
[--defaults] Loads the environment's MySQL option files. Default behavior is to skip loading them to avoid failures due to misconfiguration.
EXAMPLES
# Open MySQL console $ wp db cli mysql>
wp db query
Executes a SQL query against the database.
~~~ wp db query [
Executes an arbitrary SQL query using DBHOST, DBNAME, DB_USER and DB_PASSWORD database credentials specified in wp-config.php.
Use the --skip-column-names MySQL argument to exclude the headers from a SELECT query. Pipe the output to remove the ASCII table entirely.
OPTIONS
[
[--dbuser=
[--dbpass=
[--
[--defaults] Loads the environment's MySQL option files. Default behavior is to skip loading them to avoid failures due to misconfiguration.
EXAMPLES
# Execute a query stored in a file $ wp db query < debug.sql
# Query for a specific value in the database (pipe the result to remove the ASCII table borders) $ wp db query 'SELECT optionvalue FROM wpoptions WHERE opti' --skip-column-names +---------------------+ | https://example.com | +---------------------+
# Check all tables in the database $ wp db query "CHECK TABLE $(wp db tables | paste -s -d, -);" +---------------------------------------+-------+----------+----------+ | Table | Op | Msgtype | Msgtext | +---------------------------------------+-------+----------+----------+ | wordpressdbase.wpusers | check | status | OK | | wordpressdbase.wpusermeta | check | status | OK | | wordpressdbase.wpposts | check | status | OK | | wordpressdbase.wpcomments | check | status | OK | | wordpressdbase.wplinks | check | status | OK | | wordpressdbase.wpoptions | check | status | OK | | wordpressdbase.wppostmeta | check | status | OK | | wordpressdbase.wpterms | check | status | OK | | wordpressdbase.wpterm_taxonomy | check | status | OK | | wordpressdbase.wpterm_relationships | check | status | OK | | wordpressdbase.wptermmeta | check | status | OK | | wordpressdbase.wpcommentmeta | check | status | OK | +---------------------------------------+-------+----------+----------+
# Pass extra arguments through to MySQL $ wp db query 'SELECT * FROM wp_options WHERE opti' --skip-column-names +---+------+------------------------------+-----+ | 2 | home | http://wordpress-develop.dev | yes | +---+------+------------------------------+-----+
MULTISITE USAGE
Please note that the global --url parameter will have no effect on this command. In order to query for data in a site other than your primary site, you will need to manually modify the table names to use the prefix that includes the site's ID.
For example, to get the home option for your second site, modify the example above like so:
$ wp db query 'SELECT optionvalue FROM wp2_options WHERE opti' --skip-column-names +----------------------+ | https://example2.com | +----------------------+
To confirm the ID for the site you want to query, you can use the wp site list command:
# wp site list --fields=blog_id,url +---------+-----------------------+ | blog_id | url | +---------+-----------------------+ | 1 | https://example1.com/ | | 2 | https://example2.com/ | +---------+-----------------------+
wp db export
Exports the database to a file or to STDOUT.
~~~ wp db export [
Alias: dump
Runs mysqldump utility using DBHOST, DBNAME, DB_USER and DBPASSWORD database credentials specified in wp-config.php. Accepts any valid mysqldump flags.
OPTIONS
[
[--dbuser=
[--dbpass=
[--
[--tables=
[--exclude_tables=
[--include-tablespaces] Skips adding the default --no-tablespaces option to mysqldump.
[--porcelain] Output filename for the exported database.
[--defaults] Loads the environment's MySQL option files. Default behavior is to skip loading them to avoid failures due to misconfiguration.
EXAMPLES
# Export database with --skip-opt and --add-drop-table mysqldump flags $ wp db export --skip-opt --add-drop-table Success: Exported to 'wordpress_dbase-db72bb5.sql'.
# Export certain tables $ wp db export --tables=wpoptions,wpusers Success: Exported to 'wordpress_dbase-db72bb5.sql'.
# Export all tables matching a wildcard $ wp db export --tables=$(wp db tables 'wp_user*' --format=csv) Success: Exported to 'wordpress_dbase-db72bb5.sql'.
# Export all tables matching prefix $ wp db export --tables=$(wp db tables --all-tables-with-prefix --format=csv) Success: Exported to 'wordpress_dbase-db72bb5.sql'.
# Export certain posts without create table statements $ wp db export --no-create-info=true --tables=wp_posts --where="ID in (100,101,102)" Success: Exported to 'wordpress_dbase-db72bb5.sql'.
# Export relating meta for certain posts without create table statements $ wp db export --no-create-info=true --tables=wppostmeta --where="postid in (100,101,102)" Success: Exported to 'wordpress_dbase-db72bb5.sql'.
# Skip certain tables from the exported database $ wp db export --excludetables=wpoptions,wp_users Success: Exported to 'wordpress_dbase-db72bb5.sql'.
# Skip all tables matching a wildcard from the exported database $ wp db export --excludetables=$(wp db tables 'wpuser*' --format=csv) Success: Exported to 'wordpress_dbase-db72bb5.sql'.
# Skip all tables matching prefix from the exported database $ wp db export --exclude_tables=$(wp db tables --all-tables-with-prefix --format=csv) Success: Exported to 'wordpress_dbase-db72bb5.sql'.
# Export database to STDOUT. $ wp db export - -- MySQL dump 10.13 Distrib 5.7.19, for osx10.12 (x86_64) -- -- Host: localhost Database: wpdev -- ------------------------------------------------------ -- Server version 5.7.19 ...
wp db import
Imports a database from a file or from STDIN.
~~~ wp db import [
Runs SQL queries using DBHOST, DBNAME, DB_USER and DB_PASSWORD database credentials specified in wp-config.php. This does not create database by itself and only performs whatever tasks are defined in the SQL.
OPTIONS
[
[--dbuser=
[--dbpass=
[--
[--skip-optimization] When using an SQL file, do not include speed optimization such as disabling auto-commit and key checks.
[--defaults] Loads the environment's MySQL option files. Default behavior is to skip loading them to avoid failures due to misconfiguration.
EXAMPLES
# Import MySQL from a file. $ wp db import wordpress_dbase.sql Success: Imported from 'wordpress_dbase.sql'.
wp db search
Finds a string in the database.
~~~ wp db search
Searches through all of the text columns in a selection of database tables for a given string, Outputs colorized references to the string.
Defaults to searching through all tables registered to $wpdb. On multisite, this default is limited to the tables for the current site.
OPTIONS
[
[--network] Search through all the tables registered to $wpdb in a multisite install.
[--all-tables-with-prefix] Search through all tables that match the registered table prefix, even if not registered on $wpdb. On one hand, sometimes plugins use tables without registering them to $wpdb. On another hand, this could return tables you don't expect. Overrides --network.
[--all-tables] Search through ALL tables in the database, regardless of the prefix, and even if not registered on $wpdb. Overrides --network and --all-tables-with-prefix.
[--before_context=
[--after_context=
[--regex] Runs the search as a regular expression (without delimiters). The search becomes case-sensitive (i.e. no PCRE flags are added). Delimiters must be escaped if they occur in the expression. Because the search is run on individual columns, you can use the ^ and $ tokens to mark the start and end of a match, respectively.
[--regex-flags=
[--regex-delimiter=chr(1).
[--tablecolumnonce] Output the 'table:column' line once before all matching row lines in the table column rather than before each matching row.
[--one_line] Place the 'table:column' output on the same line as the row id and match ('table:column:id:match'). Overrides --tablecolumnonce.
[--matches_only] Only output the string matches (including context). No 'table:column's or row ids are outputted.
[--stats] Output stats on the number of matches found, time taken, tables/columns/rows searched, tables skipped.
[--tablecolumncolor=
[--idcolor=
[--matchcolor=
[--fields=
[--format=
The percent color codes available are:
| Code | Color | ---- | ----- | %y | Yellow (dark) (mustard) | %g | Green (dark) | %b | Blue (dark) | %r | Red (dark) | %m | Magenta (dark) | %c | Cyan (dark) | %w | White (dark) (light gray) | %k | Black | %Y | Yellow (bright) | %G | Green (bright) | %B | Blue (bright) | %R | Red (bright) | %M | Magenta (bright) | %C | Cyan (bright) | %W | White | %K | Black (bright) (dark gray) | %3 | Yellow background (dark) (mustard) | %2 | Green background (dark) | %4 | Blue background (dark) | %1 | Red background (dark) | %5 | Magenta background (dark) | %6 | Cyan background (dark) | %7 | White background (dark) (light gray) | %0 | Black background | %8 | Reverse | %U | Underline | %F | Blink (unlikely to work)
They can be concatenated. For instance, the default match color of black on a mustard (dark yellow) background %3%k can be made black on a bright yellow background with %Y%0%8.
AVAILABLE FIELDS
These fields will be displayed by default for each result:
- table
- column
- match
- primarykeyname
- primarykeyvalue
# Search through the database for the 'wordpress-develop' string $ wp db search wordpress-develop wpoptions:optionvalue 1:http://wordpress-develop.dev wpoptions:optionvalue 1:https://example.com/foo ...
# Search through a multisite database on the subsite 'foo' for the 'example.com' string $ wp db search example.com --url=example.com/foo wp2comments:commentauthorurl 1:https://example.com/ wp2options:option_value ...
# Search through the database for the 'https?://' regular expression, printing stats. $ wp db search 'https?://' --regex --stats wpcomments:commentauthor_url 1:https://wordpress.org/ ... Success: Found 99146 matches in 10.752s (10.559s searching). Searched 12 tables, 53 columns, 1358907 rows. 1 table skipped: wptermrelationships.
# SQL search database table 'wpoptions' where 'optionname' match 'foo' wp db query 'SELECT * FROM wpoptions WHERE optionname like "%foo%"' --skip-column-names +----+--------------+--------------------------------+-----+ | 98 | foooptions | a:1:{s:12:"multiwidget";i:1;} | yes | | 99 | foo_settings | a:0:{} | yes | +----+--------------+--------------------------------+-----+
# SQL search and delete records from database table 'wpoptions' where 'optionname' match 'foo' wp db query "DELETE from wpoptions where optionid in ($(wp db query "SELECT GROUPCONCAT(optionid SEPARATOR ',') from wpoptions where optionname like '%foo%';" --silent --skip-column-names))"
# Search for a string and print the result as a table $ wp db search https://localhost:8889 --format=table --fields=table,column,match +------------+--------------+-----------------------------+ | table | column | match | +------------+--------------+-----------------------------+ | wpoptions | optionvalue | https://localhost:8889 | | wpoptions | optionvalue | https://localhost:8889 | | wp_posts | guid | https://localhost:8889/?p=1 | | wpusers | userurl | https://localhost:8889 | +------------+--------------+-----------------------------+
# Search for a string and get only the IDs (only works for a single table) $ wp db search https://localhost:8889 wp_options --format=ids 1 2
wp db tables
Lists the database tables.
~~~ wp db tables [
