MySQL CLI
for WordPress

1. Before using MySQL CLI

grep "DB_NAME\\|DB_USER\\|DB_PASSWORD\\|DB_HOST" wp-config.php

2. Connect to the WordPress database

Once you know the database name and user, connect using:

mysql -u database_user -p database_name

You will be prompted for the password. Example:

$ mysql -u wpuser -p wordpress_db
Enter password:

mysql>

If the database host is not localhost, use -h:

mysql -h database_host -u database_user -p database_name

3. Useful first MySQL commands

SHOW DATABASES;

USE wordpress_db;

SHOW TABLES;

EXIT;

4. Check the WordPress table prefix

Most examples use the default wp_ prefix, but many sites use a custom prefix. Check wp-config.php:

grep "table_prefix" wp-config.php

Example output:

$table_prefix = 'wp_';

If the prefix is different, adjust the examples below. For example, wp_options may be abc_options.

5. Check WordPress site URL values

WordPress stores the main site URLs in the options table.

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

Example output:

+-------------+----------------------+
| option_name | option_value         |
+-------------+----------------------+
| home        | https://example.com  |
| siteurl     | https://example.com  |
+-------------+----------------------+ 

This is useful when troubleshooting migrations, wrong-domain redirects or mixed HTTP/HTTPS values.

6. Update home and siteurl with MySQL

You can update the main WordPress URL values directly with SQL:

wp db export before-url-update.sql

UPDATE wp_options
SET option_value = 'https://newsite.com'
WHERE option_name = 'home';

UPDATE wp_options
SET option_value = 'https://newsite.com'
WHERE option_name = 'siteurl';

This updates only the main WordPress URL settings. It does not replace old URLs inside posts, pages, widgets or metadata. For that, use WP-CLI Search Replace.

7. Check WordPress users

The wp_users table stores user logins, email addresses and display names.

SELECT ID, user_login, user_email, user_registered
FROM wp_users
ORDER BY ID;

Example output:

+----+------------+-------------------+---------------------+
| ID | user_login | user_email        | user_registered     |
+----+------------+-------------------+---------------------+
| 1  | admin      | admin@example.com | 2024-01-12 09:20:00 |
| 2  | editor     | editor@example.com| 2024-03-03 15:18:00 |
+----+------------+-------------------+---------------------+

To view roles, you usually need to inspect wp_usermeta too, but WP-CLI is easier for user management:

wp user list

8. Check database table sizes

This query shows WordPress table sizes in MB:

SELECT
    table_name AS "Table",
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size_MB"
FROM information_schema.TABLES
WHERE table_schema = DATABASE()
ORDER BY (data_length + index_length) DESC;

This helps find large tables such as:

• wp_options
• wp_posts
• wp_postmeta
• WooCommerce action scheduler tables
• Security plugin log tables
• Backup or statistics plugin tables

9. Check autoloaded options

Large autoloaded options can slow WordPress down because they are loaded on many requests.

SELECT option_name, LENGTH(option_value) AS size_bytes
FROM wp_options
WHERE autoload = 'yes'
ORDER BY size_bytes DESC
LIMIT 20;

This can help identify bloated options from plugins, themes or old configuration data.

10. Count posts by status and type

Useful for quickly understanding content volume:

SELECT post_type, post_status, COUNT(*) AS total
FROM wp_posts
GROUP BY post_type, post_status
ORDER BY total DESC;

This can show counts for posts, pages, attachments, products, revisions, drafts and other custom post types.

11. Check for lots of revisions

Post revisions can build up over time.

SELECT COUNT(*) AS revisions
FROM wp_posts
WHERE post_type = 'revision';

If there are many revisions, use a proper cleanup process or plugin. Avoid manual deletes unless you understand the relationships involved.

12. Create a database backup from the command line

From outside the MySQL shell, use mysqldump:

mysqldump -u database_user -p database_name > wordpress-backup.sql

Compressed backup:

mysqldump -u database_user -p database_name | gzip > wordpress-backup.sql.gz

If WP-CLI is available, this is often simpler:

wp db export wordpress-backup.sql

13. Import a database backup

Importing replaces or adds data depending on the SQL file. Be careful.

mysql -u database_user -p database_name < wordpress-backup.sql

With WP-CLI:

wp db import wordpress-backup.sql

14. MySQL CLI vs WP-CLI

mysql -u user -p database

wp db export
wp search-replace
wp user list

Safe MySQL workflow for WordPress

# 1. Confirm the WordPress directory
pwd
ls wp-config.php

# 2. Check database credentials
grep "DB_NAME\\|DB_USER\\|DB_HOST" wp-config.php

# 3. Export backup
wp db export before-mysql-work.sql

# 4. Connect
mysql -u database_user -p database_name

# 5. Run read-only SELECT queries first
SHOW TABLES;
SELECT option_name, option_value FROM wp_options WHERE option_name IN ('home', 'siteurl');

Common mistakes

• Forgetting the table prefix: not every WordPress site uses wp_.
• Editing without backup: always export the database first.
• Updating only home/siteurl: old URLs may still exist in posts and metadata.
• Running queries against the wrong database: confirm with SELECT DATABASE();.
• Deleting options blindly: some options are critical to WordPress or plugins.
• Using raw SQL for serialized data: use WP-CLI search-replace instead.

FAQ

How do I connect to a WordPress database from MySQL CLI?

mysql -u database_user -p database_name

How do I find the WordPress database name?

grep "DB_NAME" wp-config.php

How do I check the WordPress site URL in MySQL?

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

How do I update the WordPress site URL in MySQL?

UPDATE wp_options
SET option_value = 'https://newsite.com'
WHERE option_name = 'home';

UPDATE wp_options
SET option_value = 'https://newsite.com'
WHERE option_name = 'siteurl';

Should I use MySQL or WP-CLI for search replace?

Use WP-CLI for search-replace. WordPress data can be serialized, and WP-CLI handles that safely.