Home / Cheat Sheets / MySQL CLI Cheat Sheet

Database troubleshooting MySQL CLI Cheat Sheet banner

MySQL CLI Cheat Sheet

Use the MySQL command line to connect, inspect databases, run queries, export backups, import SQL files and check common WordPress settings.

MySQL for WordPress WP-CLI Cheat Sheet WP-CLI Guide
Start here

Connect to MySQL

Connect as user

mysql -u username -p

Connect to database

mysql -u username -p database_name

Connect to host

mysql -h hostname -u username -p

Run one query

mysql -u username -p -e "SHOW DATABASES;"

Inspect databases and tables

TaskSQL
Show databasesSHOW DATABASES;
Select databaseUSE database_name;
Show tablesSHOW TABLES;
Describe tableDESCRIBE wp_options;
Show table sizeSHOW TABLE STATUS LIKE 'wp_options'\G

Useful query examples

Count rows

SELECT COUNT(*) FROM wp_posts;

Show latest posts

SELECT ID, post_title, post_date FROM wp_posts ORDER BY post_date DESC LIMIT 10;

Find large options

SELECT option_name, LENGTH(option_value) AS size FROM wp_options ORDER BY size DESC LIMIT 10;

Check users

SELECT ID, user_login, user_email FROM wp_users;

Export databases with mysqldump

Export database

mysqldump -u username -p database_name > backup.sql

Compressed export

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

Export one table

mysqldump -u username -p database_name wp_options > wp_options.sql

Export schema only

mysqldump -u username -p --no-data database_name > schema.sql

Import SQL files

Import SQL file

mysql -u username -p database_name < backup.sql

Import compressed dump

gunzip < backup.sql.gz | mysql -u username -p database_name

Check file first

head -50 backup.sql

Show dump size

du -sh backup.sql

WordPress database checks

Check site URLs

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

Update siteurl

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

Update home

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

Check active plugins

SELECT option_value FROM wp_options WHERE option_name='active_plugins'\G
For safer WordPress URL changes, WP-CLI is usually better because it handles serialized data. See the WP-CLI Search Replace Guide.

Safety tips

  • Export a backup before running UPDATE or DELETE queries.
  • Use SELECT first to confirm the rows you will change.
  • Be careful with WordPress serialized data. Prefer WP-CLI search-replace where possible.
  • Do not paste database passwords into shared terminals or screenshots.
Database workflows

MySQL CLI workflows for WordPress and hosting

Connect

mysql -u dbuser -p database_name

List tables

SHOW TABLES;

Check WordPress URLs

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

Export database

mysqldump -u dbuser -p database_name > backup.sql
FAQ

Frequently Asked Questions

How do I connect to MySQL from the command line?

Use mysql -u USER -p DATABASE_NAME.

How do I list MySQL tables?

Run SHOW TABLES; after selecting a database.

How do I export a database?

Use mysqldump -u USER -p DATABASE_NAME > backup.sql.

Should I update WordPress URLs directly in MySQL?

Prefer WP-CLI search-replace for URL changes because it handles serialized data.