How to show databases in MySQL

Listing databases is the starting point for most MySQL work. Whether you need to check if a database exists before creating it, find out which database a WordPress site uses, or check how much space your databases consume, it starts with SHOW DATABASES. This guide covers every way to list, filter, inspect, and navigate databases in MySQL.

Listing all databases#

SHOW DATABASES

The basic command:

SHOW DATABASES;

Output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wordpress_blog     |
| wordpress_shop     |
+--------------------+

The first four are MySQL system databases that exist on every server:

• information_schema – read-only metadata about all databases, tables, columns, and privileges. This is where you query for database sizes, table structures, and server statistics.
• mysql – stores user accounts, privileges, and server configuration. The  mysql.user  table is where MySQL users and their authentication details live.
• performance_schema – collects server performance metrics. Useful for diagnosing slow queries and resource usage.
• sys – views and functions built on top of performance_schema that make the data easier to read. Added in MySQL 5.7.

Your application databases appear alongside these system databases.

SHOW SCHEMAS

SHOW SCHEMAS is an alias for SHOW DATABASES . They are identical:

SHOW SCHEMAS;

MySQL uses “schema” and “database” interchangeably. Some other database systems (PostgreSQL, SQL Server) distinguish between them, but in MySQL they mean the same thing.

Filtering the list#

Filter with LIKE

Use LIKE to filter by pattern:

SHOW DATABASES LIKE 'wordpress%';

Output:

+----------------------+
| Database (wordpress%) |
+----------------------+
| wordpress_blog       |
| wordpress_shop       |
+----------------------+

The % wildcard matches any sequence of characters. _ matches exactly one character:

-- Databases starting with "wp"
SHOW DATABASES LIKE 'wp%';

-- Databases with exactly 12 characters starting with "m"
SHOW DATABASES LIKE 'm___________';

-- Databases containing "test" anywhere in the name
SHOW DATABASES LIKE '%test%';

Filter with WHERE

For more complex filtering, use WHERE :

SHOW DATABASES WHERE `Database` LIKE '%shop%';

The column name in the output is Database , so that is what you reference in the WHERE clause.

Checking the current database#

SELECT DATABASE()

If you are working in the MySQL shell and forgot which database you switched to:

SELECT DATABASE();

Output:

+----------------+
| DATABASE()     |
+----------------+
| wordpress_blog |
+----------------+

If you have not selected a database yet, it returns NULL .

Switching databases#

USE

Switch to a different database:

USE wordpress_shop;

Output:

Database changed

All subsequent queries operate on wordpress_shop until you USE another database or disconnect. You can still query other databases by fully qualifying the table name:

-- Query a table in a different database without switching
SELECT * FROM wordpress_blog.wp_posts LIMIT 5;

This is useful when you need to compare data between databases or run cross-database queries.

Listing tables in a database#

Once you have switched to a database, list its tables:

SHOW TABLES;

For a WordPress database:

+--------------------------+
| Tables_in_wordpress_blog |
+--------------------------+
| wp_commentmeta           |
| wp_comments              |
| wp_links                 |
| wp_options               |
| wp_postmeta              |
| wp_posts                 |
| wp_term_relationships    |
| wp_term_taxonomy         |
| wp_termmeta              |
| wp_terms                 |
| wp_usermeta              |
| wp_users                 |
+--------------------------+

These 12 tables are the WordPress core tables. Plugins add their own tables (WooCommerce adds 20+, Yoast adds several, etc.).

Filter tables

SHOW TABLES LIKE 'wp_woo%';

This lists only WooCommerce tables, which is useful on large WordPress installations with dozens of plugin tables.

Show tables without switching databases

SHOW TABLES FROM wordpress_shop;

or equivalently:

SHOW TABLES IN wordpress_shop;

Checking database sizes#

MySQL does not have a built-in “show database sizes” command, but you can calculate it from information_schema :

Size of all databases

SELECT
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;

Output:

+--------------------+-----------+
| Database           | Size (MB) |
+--------------------+-----------+
| wordpress_shop     | 245.67    |
| wordpress_blog     | 34.12     |
| mysql              | 2.54      |
| performance_schema | 0.00      |
| sys                | 0.02      |
+--------------------+-----------+

Size of a specific database

SELECT
    table_name AS 'Table',
    ROUND(data_length / 1024 / 1024, 2) AS 'Data (MB)',
    ROUND(index_length / 1024 / 1024, 2) AS 'Index (MB)',
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS 'Total (MB)'
FROM information_schema.TABLES
WHERE table_schema = 'wordpress_blog'
ORDER BY (data_length + index_length) DESC;

This shows each table’s data size and index size separately. Useful for identifying which tables are consuming the most space. In WordPress, wp_posts , wp_postmeta , and wp_options are usually the largest. For WooCommerce, the order and product tables can grow very large.

For strategies on managing database size, see WordPress database optimization: how to clean up and speed up your database.

Row counts per table

SELECT
    table_name AS 'Table',
    table_rows AS 'Rows'
FROM information_schema.TABLES
WHERE table_schema = 'wordpress_blog'
ORDER BY table_rows DESC;

Note that table_rows is an estimate for InnoDB tables, not an exact count. For an exact count of a specific table:

SELECT COUNT(*) FROM wordpress_blog.wp_posts;

Running commands from outside MySQL#

You do not need to enter the MySQL shell to list databases. The -e flag executes a command and returns to bash:

mysql -u root -p -e "SHOW DATABASES"

Machine-readable output

For scripting, use --batch and --skip-column-names to get clean output without formatting:

mysql -u root -p --batch --skip-column-names -e "SHOW DATABASES"

Output (one database per line, no table formatting):

information_schema
mysql
performance_schema
sys
wordpress_blog
wordpress_shop

This is useful for scripts that need to iterate over databases:

for db in $(mysql -u root -p --batch --skip-column-names -e "SHOW DATABASES" | grep -v -E "^(information_schema|mysql|performance_schema|sys)$"); do
    echo "Processing database: $db"
    mysqldump -u root -p "$db" > "/backups/${db}.sql"
done

Quick size check from command line

mysql -u root -p -e "
SELECT table_schema AS db,
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY size_mb DESC;
"

Using WP-CLI for WordPress databases#

If you manage WordPress sites, WP-CLI provides database commands that are easier than raw SQL:

Check which database WordPress uses

wp config get DB_NAME

Check database size

wp db size --tables

This shows the size of every table in the WordPress database, formatted for human reading.

List tables

wp db tables

Run arbitrary SQL

wp db query "SHOW DATABASES"

WP-CLI uses the credentials from wp-config.php, so you do not need to know or type the MySQL password.

The information_schema database#

information_schema is the most useful system database for inspecting server state. It is read-only and does not correspond to actual files on disk – MySQL generates its contents dynamically from internal metadata.

Useful queries

List all databases with their character sets:

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA;

Count tables per database:

SELECT table_schema AS 'Database', COUNT(*) AS 'Tables'
FROM information_schema.TABLES
GROUP BY table_schema;

Find tables with a specific engine:

SELECT table_schema, table_name, engine
FROM information_schema.TABLES
WHERE engine = 'MyISAM' AND table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');

MyISAM tables in a WordPress database are a sign of an old installation that has not been fully migrated to InnoDB. WordPress has used InnoDB as the default since version 5.5.

Find the largest tables across all databases:

SELECT table_schema, table_name,
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.TABLES
ORDER BY (data_length + index_length) DESC
LIMIT 20;

Permissions and visibility#

You can only see databases you have permission to access. A MySQL user with privileges on wordpress_blog only sees that database (plus information_schema , which all users can see):

-- As wp_user (limited privileges)
SHOW DATABASES;

Output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| wordpress_blog     |
+--------------------+

The root user sees all databases. This is a security feature – users cannot discover the names of databases they do not have access to.

If you run SHOW DATABASES and your database is missing from the list, you do not have the SHOW DATABASES privilege. Ask the database administrator to grant access.

Checking database existence in scripts#

SQL approach

SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'wordpress_blog';

Returns one row if the database exists, zero rows if it does not. Useful in migration scripts and setup automation.

Bash approach

if mysql -u root -p -e "USE wordpress_blog" 2>/dev/null; then
    echo "Database exists"
else
    echo "Database does not exist"
fi

In WordPress

WordPress checks database connectivity during the wp-config.php loading process. If the database does not exist, you see “Error establishing a database connection” rather than a MySQL error. See Error establishing a database connection in WordPress: how to fix it for the full diagnostic process.

How Hostney handles database listing#

On Hostney, the Hosting > MySQL Databases page in the control panel lists all databases for the current hosting account. Each database shows its name, size, size limit, and read-only status. You do not need SSH or command-line access to see this information.

For direct database browsing, the control panel provides single sign-on access to phpMyAdmin. Click “Open phpMyAdmin” next to any database, and you are logged in directly without entering credentials. From phpMyAdmin you can browse tables, run queries, and export data.

The control panel only shows databases belonging to your account. Even if multiple accounts share the same MySQL server, each account sees only its own databases. This isolation is enforced at both the control panel level (the API scopes all queries to your customer ID) and the MySQL user level (your database users only have privileges on your databases).

Database sizes are monitored automatically every two minutes. If a database exceeds its size limit, it switches to read-only mode so the site can still serve existing content while you clean up data. The control panel shows the current size and limit for each database.

Summary#

SHOW DATABASES lists all databases you have access to. Filter with LIKE for pattern matching, use information_schema for detailed metadata like sizes and character sets, and use USE to switch between databases. From the command line, the -e flag lets you run queries without entering the interactive shell.

For WordPress sites, WP-CLI provides convenient wrappers ( wp db size , wp db tables , wp config get DB_NAME ) that are easier than raw SQL for everyday tasks. For creating new databases, see How to create a database in MySQL. For managing the users who access those databases, see How to show and manage MySQL users.