Accessing the Database CLI
The MySQL/MariaDB command-line client (mysql) is the fastest way to inspect a WordPress database, validate credentials, and run targeted administrative queries. Use it for read-only checks first (status, table lists, simple SELECTs), then progress to changes only when you have a backup and you are certain you're on the right server and database.
Use mysql -u wpuser -p -D wordpress to connect to the WordPress database. Use mysql -e "SQL" for one-off commands in scripts.
Mental Model
Prerequisites
- MySQL or MariaDB server installed and running.
- The
mysqlclient installed (usually installed with the server package). - A username + password with appropriate privileges.
Verify the client is available:
mysql --version
Core Syntax
mysql [OPTIONS] [DATABASE]
Common connection flags:
-u <user>username-pprompt for password-h <host>host (use127.0.0.1for TCP to local host)-P <port>port (default 3306)-S <socket>Unix socket path-D <db>default database to use
Key Options
| Option | What it does | Example | WordPress / VPS use case |
|---|---|---|---|
-u <user> | Set username | mysql -u wpuser -p | Use least-privilege DB user |
-p | Prompt for password | mysql -u wpuser -p | Avoid putting passwords in command history |
-D <db> | Select database | mysql -u wpuser -p -D wordpress | Connect directly to the WP DB |
-e "SQL" | Execute one command | mysql -u wpuser -p -D wordpress -e "SHOW TABLES;" | Automation / quick checks |
-h <host> | Connect to a host | mysql -h 127.0.0.1 ... | Force TCP instead of socket |
-S <path> | Use Unix socket | mysql -S /var/run/mysqld/mysqld.sock ... | Fix socket-path issues |
Examples (Commands + Expected Output)
Prompts and output formatting vary by version. Use the patterns and flags.
Check the client version
mysql --version
Expected output:
mysql Ver 8.0.xx for Linux on x86_64 (MySQL Community Server)
Use case: Confirm the client exists and record the version.
Connect locally as root (interactive)
mysql -u root -p
Expected output:
Enter password:
Welcome to the MySQL monitor.
mysql>
Use case: Admin access for troubleshooting (use with care).
Connect directly to the WordPress database
mysql -u wpuser -p -D wordpress
Expected output:
Enter password:
mysql>
Use case: Run queries against the right database without extra USE commands.
Run a one-off query (non-interactive)
mysql -u wpuser -p -D wordpress -e "SHOW TABLES;"
Expected output:
Tables_in_wordpress
wp_options
wp_posts
wp_postmeta
...
Use case: Quick validation that credentials and database selection are correct.
Run a query and print a single value
mysql -u wpuser -p -D wordpress -e "SELECT COUNT(*) AS posts FROM wp_posts;"
Expected output:
posts
1234
Use case: Sanity-check database size/content during migrations.
Connect via a specific socket
mysql -u root -p -S /var/run/mysqld/mysqld.sock
Expected output:
Enter password:
mysql>
Use case: Fix ERROR 2002 when the client is using the wrong socket path.
Avoid exposing MySQL/MariaDB to the public internet. If you must connect remotely, restrict by firewall/VPN and use TLS.
WordPress VPS Use Cases
| Task | Command pattern | Notes |
|---|---|---|
| Verify credentials | mysql -u wpuser -p -D wordpress -e "SHOW TABLES;" | Fast smoke test |
| Confirm DB host volume | df -h /var/lib/mysql | Capacity planning |
| Quick read-only checks | SELECT ... | Do read-only first |
Troubleshooting
| Error | Likely cause | Fix |
|---|---|---|
Access denied for user | Wrong password or privileges | Verify user and grants; check wp-config settings |
ERROR 2002 (HY000) | Cannot connect (socket or service down) | Check service status and socket path; try -h 127.0.0.1 |
Unknown database | DB name mismatch | List databases with SHOW DATABASES; |
Best Practices
- Use a dedicated least-privilege DB user for WordPress.
- Keep passwords out of shell history (use
-pprompt). - Take a backup before making changes.
Cheat Sheet
mysql --version
mysql -u root -p
mysql -u wpuser -p -D wordpress
mysql -u wpuser -p -D wordpress -e "SHOW TABLES;"
mysql -u root -p -S /var/run/mysqld/mysqld.sock