MySQL and MariaDB Cheat Sheet for WordPress

WordPress keeps everything except files in one MySQL or MariaDB database: posts, pages, settings, users, menus and plugin data. This sheet covers the queries you need when the dashboard is unreachable or slow, and the server tools for backup, import and repair. MariaDB began as a fork of MySQL and the commands here work on both unless marked.

PREFIX_ stands for your table prefix. Find it in wp-config.php as $table_prefix. The default is wp_, so PREFIX_options usually means wp_options, but many hosts and installers use a random prefix such as wp8x3_. Other placeholders: DBNAME, DBUSER and DBHOST are the values of DB_NAME, DB_USER and DB_HOST in wp-config.php; NEWPASSWORD, USERNAME and EXAMPLE.COM are yours to fill in.

Before any UPDATE or DELETE: take a backup (wp db export or mysqldump, below), run the matching SELECT first to see exactly which rows will change, and prefer WP-CLI where a command exists, because it runs WordPress hooks and handles serialized data correctly.

Connecting

Method	Command	Notes
Through WordPress	wp db cli	Reads the credentials from wp-config.php. The simplest way in.
One query through WordPress	wp db query "SELECT COUNT(*) FROM PREFIX_posts"	No interactive session needed. wp db prefix prints the prefix.
Local socket	mysql -u DBUSER -p DBNAME	With no host, or -h localhost, the client uses the Unix socket, not TCP. -p with nothing after it prompts for the password.
Named socket	mysql -u DBUSER -p --socket=/PATH/TO/mysqld.sock DBNAME	When the socket is not where the client expects. DB_HOST can also hold localhost:/PATH/TO/mysqld.sock.
TCP	mysql -u DBUSER -p -h 127.0.0.1 -P 3306 DBNAME	127.0.0.1 forces TCP. Database grants can differ between localhost (socket) and an IP, so one may work when the other is refused.
Remote host	mysql -u DBUSER -p -h DBHOST DBNAME	Managed databases and some hosts use a separate database server.
MariaDB client name	mariadb -u DBUSER -p DBNAME	Newer MariaDB releases name the tools mariadb, mariadb-dump, mariadb-check. The mysql names may be missing or print a deprecation warning.

Credentials file: ~/.my.cnf

Saves typing the password, and keeps it out of shell history and the process list (a password typed as -pSECRET is visible to other users in ps).

[client]
user=DBUSER
password="DBPASSWORD"
host=localhost

[mysqldump]
user=DBUSER
password="DBPASSWORD"

Protect it with chmod 600 ~/.my.cnf. To use a different file, put --defaults-extra-file=/PATH/TO/file.cnf as the very first option.

Inside the client

Command	Does
SHOW DATABASES;	Databases this user can see
USE DBNAME;	Switch database
SHOW TABLES;	Tables in the current database (shows the prefix)
DESCRIBE PREFIX_options;	Columns, types and keys
SHOW CREATE TABLE PREFIX_options\G	Full definition including engine and collation. \G prints rows vertically.
SELECT VERSION();	Server version (MariaDB includes “MariaDB” in the string)
SHOW GRANTS;	What the current user may do
source /PATH/TO/dump.sql	Run a SQL file from inside the client

The WordPress table map

Table	Holds	Key columns
PREFIX_posts	Posts, pages, attachments, revisions, menu items, reusable blocks, templates, and custom post types (products, orders on older WooCommerce)	ID, post_author, post_date, post_content, post_title, post_status, post_name (slug), post_type, post_parent, guid, post_mime_type
PREFIX_postmeta	Custom fields for posts: page builder data, SEO fields, featured image ID (_thumbnail_id)	meta_id, post_id, meta_key, meta_value
PREFIX_options	Site settings, plugin and theme settings, widgets, cron schedule, transients	option_id, option_name, option_value, autoload
PREFIX_users	User accounts	ID, user_login, user_pass (hash), user_nicename, user_email, user_registered, user_activation_key, display_name
PREFIX_usermeta	Roles, capabilities, profile fields, session tokens, per-user settings	umeta_id, user_id, meta_key, meta_value. Roles live in meta_key = 'PREFIX_capabilities'.
PREFIX_terms	Category, tag and custom taxonomy terms	term_id, name, slug, term_group
PREFIX_term_taxonomy	Which taxonomy each term belongs to, its parent and post count	term_taxonomy_id, term_id, taxonomy, description, parent, count
PREFIX_term_relationships	Links posts (and menu items, links) to terms	object_id, term_taxonomy_id, term_order
PREFIX_termmeta	Extra data for terms	meta_id, term_id, meta_key, meta_value
PREFIX_comments	Comments, pingbacks, trackbacks, and some plugin notes (for example WooCommerce order notes)	comment_ID, comment_post_ID, comment_author_email, comment_author_IP, comment_date, comment_content, comment_approved (0, 1, spam, trash), comment_type, user_id
PREFIX_commentmeta	Extra data for comments, such as spam-check results	meta_id, comment_id, meta_key, meta_value
PREFIX_links	The old Links Manager (blogroll). Hidden in new installs since WordPress 3.5, still created.	link_id, link_url, link_name

Multisite additions

Table	Holds
PREFIX_blogs	One row per site: blog_id, domain, path, status flags
PREFIX_blogmeta	Per-site metadata
PREFIX_site and PREFIX_sitemeta	The network and its settings (network admins in site_admins)
PREFIX_signups, PREFIX_registration_log	Pending and logged registrations
PREFIX_2_posts, PREFIX_2_options and so on	Each extra site gets its own content tables with its ID in the name. Users and usermeta stay shared.

Plugins add their own tables (for example WooCommerce, form plugins and security plugins). Tables with a different prefix than $table_prefix are usually left over from an old install or another application.

Diagnostic queries

Autoloaded options (a common cause of slow sites)

Autoloaded options are loaded on every request. WordPress 6.6 added new autoload values, so the queries below match both the old (yes) and new (on, auto, auto-on) values. Site Health in 6.6 and later warns when the total reaches 800,000 bytes (roughly 800 KB; filterable with site_status_autoloaded_options_size_limit).

-- total autoloaded size in KB
SELECT ROUND(SUM(LENGTH(option_value)) / 1024) AS autoload_kb
FROM PREFIX_options
WHERE autoload IN ('yes', 'on', 'auto', 'auto-on');

-- the 20 largest autoloaded options
SELECT option_name, LENGTH(option_value) AS bytes, autoload
FROM PREFIX_options
WHERE autoload IN ('yes', 'on', 'auto', 'auto-on')
ORDER BY bytes DESC
LIMIT 20;

The option name usually reveals the plugin that owns it. Stop autoloading a large option with wp option set-autoload OPTION_NAME off (the command exists since WP-CLI 2.8; the on and off values that match WordPress 6.6 need WP-CLI 2.11 or later, where no also works), or remove leftovers from deleted plugins with wp option delete OPTION_NAME after checking nothing uses them.

Revisions, transients and orphans

Question	Query
How many revisions?	SELECT COUNT(*) FROM PREFIX_posts WHERE post_type = 'revision';
Revisions per post	SELECT post_parent, COUNT(*) AS revs FROM PREFIX_posts WHERE post_type = 'revision' GROUP BY post_parent ORDER BY revs DESC LIMIT 20;
Rows by post type and status	SELECT post_type, post_status, COUNT(*) FROM PREFIX_posts GROUP BY post_type, post_status ORDER BY 3 DESC;
Transients in the options table	SELECT COUNT(*) FROM PREFIX_options WHERE option_name LIKE '\_transient\_%' OR option_name LIKE '\_site\_transient\_%'; (the backslash stops _ acting as a one-character wildcard)
Orphaned postmeta (post no longer exists)	SELECT COUNT(*) FROM PREFIX_postmeta pm LEFT JOIN PREFIX_posts p ON p.ID = pm.post_id WHERE p.ID IS NULL;
Orphaned term relationships	SELECT COUNT(*) FROM PREFIX_term_relationships tr LEFT JOIN PREFIX_posts p ON p.ID = tr.object_id LEFT JOIN PREFIX_term_taxonomy tt ON tt.term_taxonomy_id = tr.term_taxonomy_id WHERE p.ID IS NULL AND tt.taxonomy NOT IN ('link_category');
Biggest meta keys	SELECT meta_key, COUNT(*) AS n, ROUND(SUM(LENGTH(meta_value))/1024) AS kb FROM PREFIX_postmeta GROUP BY meta_key ORDER BY kb DESC LIMIT 20;
Comments by status	SELECT comment_approved, COUNT(*) FROM PREFIX_comments GROUP BY comment_approved;

Clean up with WP-CLI rather than DELETE where you can: wp transient delete --expired, wp comment delete $(wp comment list --status=spam --format=ids) --force, and wp post delete $(wp post list --post_type=revision --format=ids) --force (on big sites, batch it). Limit future revisions with define( 'WP_POST_REVISIONS', 10 ); in wp-config.php.

If you must delete orphaned postmeta in SQL, after a backup and after the SELECT count above:

DELETE pm FROM PREFIX_postmeta pm
LEFT JOIN PREFIX_posts p ON p.ID = pm.post_id
WHERE p.ID IS NULL;

Users, passwords and site URLs

Find administrators

SELECT u.ID, u.user_login, u.user_email, u.user_registered
FROM PREFIX_users u
JOIN PREFIX_usermeta m ON m.user_id = u.ID
WHERE m.meta_key = 'PREFIX_capabilities'
  AND m.meta_value LIKE '%"administrator"%'
ORDER BY u.user_registered DESC;

The meta key includes the table prefix, so with the prefix wp8x3_ it is wp8x3_capabilities. The value is a serialized PHP array such as a:1:{s:13:"administrator";b:1;}. An admin you do not recognize, especially one registered recently, is a strong sign of compromise. WP-CLI equivalent: wp user list --role=administrator.

Reset a password

Method	Command	Notes
Preferred: WP-CLI	wp user update USERNAME --user_pass='NEWPASSWORD'	Uses WordPress’s own hashing. wp user reset-password USERNAME --show-password sets a random password instead and prints it.
Last resort: SQL	UPDATE PREFIX_users SET user_pass = MD5('NEWPASSWORD') WHERE user_login = 'USERNAME';	Works because WordPress still accepts a plain MD5 hash, then replaces it with a modern hash the first time that user logs in.

Why the MD5 trick is a last resort: MD5 is fast to crack, so the account is weak until the next login, and the plain password can end up in the client history file (~/.mysql_history or ~/.mariadb_history) and in server logs. Log in straight away so WordPress rehashes it, then clear the history. WordPress 6.8 and later hash new passwords with bcrypt; older versions use phpass. Both accept and upgrade MD5 on login.

Change siteurl and home

SELECT option_name, option_value FROM PREFIX_options
WHERE option_name IN ('siteurl', 'home');

UPDATE PREFIX_options SET option_value = 'https://EXAMPLE.COM'
WHERE option_name IN ('siteurl', 'home');

No trailing slash. If WP_HOME or WP_SITEURL is defined in wp-config.php, those win and the database values are ignored. For multisite, also update domain and path in PREFIX_blogs and PREFIX_site, and each site’s own options table.

Never change the domain everywhere with a plain SQL REPLACE(). Serialized PHP data stores string lengths, and changing the length breaks the data (widgets and builder layouts disappear). Use wp search-replace 'http://OLD.EXAMPLE.COM' 'https://EXAMPLE.COM' --all-tables --dry-run, check the counts, then run it without --dry-run.

Finding spam and injected scripts

Look for	Query
Script tags in content	SELECT ID, post_title, post_type FROM PREFIX_posts WHERE post_content LIKE '%<script%';
Hidden iframes	SELECT ID, post_title FROM PREFIX_posts WHERE post_content LIKE '%<iframe%';
Obfuscated code in options	SELECT option_id, option_name FROM PREFIX_options WHERE option_value LIKE '%eval(%' OR option_value LIKE '%base64_decode%' OR option_value LIKE '%<script%';
Suspicious domains	SELECT ID, post_title FROM PREFIX_posts WHERE post_content LIKE '%SUSPICIOUS-DOMAIN%';
Recently created users	SELECT ID, user_login, user_email, user_registered FROM PREFIX_users ORDER BY user_registered DESC LIMIT 10;
Recently modified content	SELECT ID, post_title, post_type, post_modified FROM PREFIX_posts ORDER BY post_modified DESC LIMIT 20;
New posts in the last week, by type (spam floods)	SELECT post_type, COUNT(*) FROM PREFIX_posts WHERE post_date > NOW() - INTERVAL 7 DAY GROUP BY post_type;
Changed site URL (redirect hacks)	SELECT option_value FROM PREFIX_options WHERE option_name IN ('siteurl','home');

Legitimate content can match these (analytics snippets, embeds, some builders store scripts), so review each hit before changing anything. Search tables added by plugins too, and clean files as well as the database, or the injection returns.

Table sizes and fragmentation

SELECT table_name,
       engine,
       ROUND((data_length + index_length) / 1024 / 1024, 1) AS size_mb,
       ROUND(data_free / 1024 / 1024, 1) AS free_mb,
       table_rows
FROM information_schema.TABLES
WHERE table_schema = 'DBNAME'
ORDER BY (data_length + index_length) DESC;

Column	Meaning
data_length, index_length	Bytes used by rows and indexes
data_free	Allocated but unused space, a rough sign of fragmentation. With InnoDB shared tablespaces it reports the free space of the whole shared file, not just this table.
table_rows	An estimate for InnoDB. Use COUNT(*) for an exact number.
engine	InnoDB, MyISAM or Aria. Anything still on MyISAM is worth converting.

Total database size: SELECT ROUND(SUM(data_length + index_length)/1024/1024) AS mb FROM information_schema.TABLES WHERE table_schema = 'DBNAME'; or wp db size --tables.

Slow queries: EXPLAIN, PROCESSLIST and the slow log

EXPLAIN basics

Put EXPLAIN in front of a SELECT to see how the server will run it, without running it.

Column	Good	Bad
type	const, eq_ref, ref, range	ALL (full table scan), index (full index scan)
key	The name of an index	NULL: no index used
rows	Small estimate	Close to the table’s total row count
Extra	Using index (answered from the index alone)	Using filesort, Using temporary on large tables

Queries on meta_value are a frequent culprit, because core indexes meta_key (its first 191 characters) but not meta_value. A leading wildcard (LIKE '%term') cannot use an index either.

What is running right now

Command	Does
SHOW FULL PROCESSLIST;	Every connection: Id, user, database, Command, Time (seconds), State, full query text. Without FULL the query is cut to 100 characters.
SELECT id, user, time, state, LEFT(info, 120) FROM information_schema.PROCESSLIST WHERE command != 'Sleep' ORDER BY time DESC;	Filterable version: active queries, longest first.
KILL QUERY 12345;	Stops the running statement but keeps the connection.
KILL 12345;	Closes the connection. Use the Id from the process list.
SHOW GLOBAL STATUS LIKE 'Threads_connected'; and SHOW VARIABLES LIKE 'max_connections';	Compare to diagnose “Too many connections”.

Non-admin users only see and kill their own connections. Killing a large write may trigger a long rollback, so check what it is first.

Slow query log

Setting (MySQL and older MariaDB)	MariaDB 10.11+ name	Purpose
slow_query_log = 1	log_slow_query = 1	Turn the log on
long_query_time = 1	log_slow_query_time = 1	Seconds a query must take to be logged (default 10; decimals allowed)
slow_query_log_file = /PATH/TO/slow.log	log_slow_query_file	Where the log goes
log_queries_not_using_indexes = 1	same	Also log queries without an index (noisy)

MariaDB keeps the old names as aliases. Set them under [mysqld] (or [mariadbd]) in the server config and restart, or at runtime with SET GLOBAL slow_query_log = 1; (needs admin privileges, and a new long_query_time only applies to new connections). Summarize the log with mysqldumpslow -s t /PATH/TO/slow.log | head (mariadb-dumpslow on newer MariaDB). Shared hosting does not allow this; ask the host, or use the Query Monitor plugin to see slow queries per page.

Backup and import with mysqldump

mysqldump --single-transaction --quick --routines --triggers \
  --default-character-set=utf8mb4 -u DBUSER -p DBNAME \
  | gzip > DBNAME-$(date +%F).sql.gz

Flag	Why
--single-transaction	Takes a consistent snapshot of InnoDB tables without locking them, so the site stays up during the dump. Does not protect MyISAM tables, and other sessions must not run ALTER TABLE during the dump.
--quick	Streams rows one at a time instead of buffering whole tables in memory. On by default as part of --opt, but harmless to state.
--routines	Includes stored procedures and functions (off by default).
--triggers	Includes triggers (on by default).
--events	Includes scheduled events (off by default).
--default-character-set=utf8mb4	Keeps emoji and other four-byte characters intact.
--no-tablespaces	Avoids the “Access denied; you need (at least one of) the PROCESS privilege(s)” error on MySQL 8.0.21 and 5.7.31 and later for users without that privilege. wp db export adds it automatically (use --include-tablespaces to turn that off).
--add-drop-table	Adds DROP TABLE before each CREATE (on by default via --opt). An import will overwrite existing tables of the same name.
--skip-lock-tables	Pair with --single-transaction if a host’s dump still tries to lock tables.

Importing

Situation	Command
Plain dump	mysql -u DBUSER -p DBNAME < dump.sql
Compressed dump	gunzip -c dump.sql.gz | mysql -u DBUSER -p DBNAME
Progress bar	pv dump.sql.gz | gunzip | mysql -u DBUSER -p DBNAME (if pv is installed)
Through WordPress	wp db import dump.sql
“MySQL server has gone away” or packet errors	Raise max_allowed_packet on the server and add --max_allowed_packet=256M to the client.
phpMyAdmin upload limit	Upload the file by SFTP and import from the command line instead. Long imports also hit PHP time limits.
Different prefix after import	Update $table_prefix in wp-config.php to match the imported tables.

Before overwriting a live database, export the current one first. An import cannot be undone.

Character sets, collations and engines

Term	Meaning
utf8 / utf8mb3	Old three-byte UTF-8. Cannot store emoji or some CJK characters; they become ? or the query fails.
utf8mb4	Full UTF-8. WordPress uses it on new installs and upgrades tables when it can.
utf8mb4_unicode_520_ci / utf8mb4_unicode_ci	Collations WordPress picks, depending on server support. A collation controls sorting and comparison; _ci means case-insensitive.
utf8mb4_0900_ai_ci	MySQL 8 default. MariaDB before 11.4.5 does not recognize it (newer releases accept it as an alias), so importing a MySQL 8 dump into an older MariaDB fails with “Unknown collation”. Replace it in the dump (with a tool like sed) with utf8mb4_unicode_ci.
Mojibake (é instead of é)	Text was exported or imported with the wrong character set. Re-export with --default-character-set=utf8mb4; check DB_CHARSET in wp-config.php.

InnoDB	MyISAM
Row-level locking: writes do not block the whole table	Table-level locking: one slow write blocks readers
Transactions and crash recovery	No transactions; tables can be marked as crashed after an unclean shutdown
Default engine in current MySQL and MariaDB	Found on old installs
Consistent dumps with --single-transaction	Needs table locks for a consistent dump

List MyISAM tables: SELECT table_name FROM information_schema.TABLES WHERE table_schema = 'DBNAME' AND engine = 'MyISAM'; Convert one: ALTER TABLE PREFIX_options ENGINE=InnoDB; (back up first; it rebuilds the table).

Check, repair, optimize and analyze

Statement	InnoDB	MyISAM / Aria
CHECK TABLE PREFIX_options;	Checks for errors	Checks for errors
REPAIR TABLE PREFIX_options;	Not supported. InnoDB recovers itself on restart; severe corruption needs a restore or innodb_force_recovery by an administrator.	Repairs a crashed table
OPTIMIZE TABLE PREFIX_options;	Rebuilds the table and updates statistics (the message “doing recreate + analyze instead” is normal). Locks or slows the table while running.	Defragments and reclaims space
ANALYZE TABLE PREFIX_options;	Refreshes index statistics so the optimizer picks better plans. Fast.	Same

Tool	Command
WP-CLI	wp db check, wp db repair, wp db optimize
mysqlcheck	mysqlcheck -u DBUSER -p --auto-repair --check DBNAME, mysqlcheck -u DBUSER -p --optimize DBNAME
WordPress repair screen	Add define( 'WP_ALLOW_REPAIR', true ); to wp-config.php, visit /wp-admin/maint/repair.php, then remove the line: the page works without logging in.

Common recipes

Task	Command
Make an existing user an administrator	wp user set-role USERNAME administrator
Create an emergency admin	wp user create USERNAME you@example.com --role=administrator --prompt=user_pass
Switch to a default theme	wp theme activate twentytwentyfive, or in SQL: UPDATE PREFIX_options SET option_value = 'twentytwentyfive' WHERE option_name IN ('template','stylesheet'); (the theme must be installed)
Deactivate all plugins	wp plugin deactivate --all, or in SQL: UPDATE PREFIX_options SET option_value = 'a:0:{}' WHERE option_name = 'active_plugins'; (note the old value first, to restore it)
Find the table prefix from SQL	SHOW TABLES LIKE '%options';
Export one table	mysqldump --single-transaction -u DBUSER -p DBNAME PREFIX_options > options.sql
Delete expired transients	wp transient delete --expired
Stuck cron jobs	wp cron event list; the schedule lives in the cron option

Gotchas

Problem	Explanation
Query works in phpMyAdmin but WordPress cannot connect	DB_HOST differs (socket vs IP), or the user’s grant is tied to a different host.
Capabilities query returns nothing	You used wp_capabilities but the prefix is different. The meta key follows the prefix.
Changed $table_prefix and lost admin access	Option names PREFIX_user_roles and usermeta keys PREFIX_capabilities and PREFIX_user_level must be renamed too.
Widgets or builder layouts vanished after a URL change	A plain SQL replace broke serialized data. Restore and use wp search-replace.
UPDATE without WHERE	Changes every row. Start client sessions with --safe-updates to block UPDATE and DELETE without a key in the WHERE clause.
OPTIMIZE TABLE during traffic	Rebuilding a large table can lock it or slow the site. Run it in a quiet period, after a backup, and only when data_free is large.
Deleting revisions does not shrink the database file	InnoDB keeps the space for reuse. Only a rebuild (OPTIMIZE) returns it, and only with per-table tablespaces.
LIKE '_transient_%' matches too much	_ is a wildcard in LIKE. Escape it as \_.
Dump looks fine but restores with errors	Version or collation differences (MySQL 8 to MariaDB), or DEFINER clauses naming a user that does not exist on the new server.