#database

17 one-liners with this tag.

Back up the WordPress database as a gzip file

$wp db export - --path=/var/www/vhosts/example.com/httpdocs | gzip > /var/www/vhosts/example.com/db-backup.sql.gz

Check the Plesk database for inconsistencies

#plesk repair db -n

Create a MySQL database with its own user

#mysql -e "CREATE DATABASE wp_example CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE USER 'wp_example_user'@'localhost' IDENTIFIED BY 'SecurePassword'; GRANT ALL PRIVILEGES ON wp_example.* TO 'wp_example_user'@'localhost';"

cautionMySQL and MariaDB

Delete all post revisions

$wp post list --post_type=revision --format=ids --path=/var/www/vhosts/example.com/httpdocs | xargs -r wp post delete --force --path=/var/www/vhosts/example.com/httpdocs

destructiveWP-CLI

Delete expired transients from the WordPress database

$wp transient delete --expired --path=/var/www/vhosts/example.com/httpdocs

Find the largest autoloaded options in WordPress

$wp option list --autoload=on --orderby=size_bytes --order=desc --fields=option_name,size_bytes --path=/var/www/vhosts/example.com/httpdocs | head -n 20

Find the system user of each domain in Plesk

#plesk db "SELECT d.name, s.login FROM domains d JOIN hosting h ON h.dom_id = d.id JOIN sys_users s ON s.id = h.sys_user_id ORDER BY d.name"

Import a gzipped dump into the WordPress database

$gunzip -c /var/www/vhosts/example.com/db-backup.sql.gz | wp db import - --path=/var/www/vhosts/example.com/httpdocs

destructiveWP-CLI

List all databases with their domain in Plesk

#plesk db "SELECT d.name AS domain, db.name AS datenbank, db.type FROM data_bases db JOIN domains d ON d.id = db.dom_id ORDER BY d.name, db.name"

List all email addresses on a Plesk server

#plesk db "SELECT CONCAT(m.mail_name, '@', d.name) AS adresse, m.postbox FROM mail m JOIN domains d ON d.id = m.dom_id ORDER BY d.name, m.mail_name"

Optimize the WordPress database

$wp db optimize --path=/var/www/vhosts/example.com/httpdocs

Permanently delete a MySQL database

#mysql -e 'DROP DATABASE wp_old;'

destructiveMySQL and MariaDB

Permanently delete all spam comments

$wp comment list --status=spam --format=ids --path=/var/www/vhosts/example.com/httpdocs | xargs -r wp comment delete --force --path=/var/www/vhosts/example.com/httpdocs

destructiveWP-CLI

Read hosting type and status of all domains from the Plesk database

#plesk db "SELECT name, htype, status FROM domains ORDER BY name"

Show the document root of all domains in Plesk

#plesk db "SELECT d.name, h.www_root FROM domains d JOIN hosting h ON h.dom_id = d.id ORDER BY d.name"

Show the size of WordPress database tables

$wp db size --tables --path=/var/www/vhosts/example.com/httpdocs

Test search and replace in the database

$wp search-replace 'http://example.com' 'https://example.com' --all-tables --dry-run --report-changed-only --path=/var/www/vhosts/example.com/httpdocs