MySQL SHOW DATABASES

Quick answer

SHOW DATABASES;

That returns every database your current user can see. For more control, read on.

SHOW DATABASES

SHOW DATABASES is the standard way to list databases in the MySQL client.

SHOW DATABASES;

Output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| my_app             |
+--------------------+
5 rows in set (0.00 sec)

The four system databases (information_schema, mysql, performance_schema, sys) are always present. Your own databases appear alongside them.

Filtering with LIKE

You can narrow results with a LIKE pattern:

SHOW DATABASES LIKE 'my_%';

Output:

+-------------------+
| Database (my_%)   |
+-------------------+
| my_app            |
| my_other_db       |
+-------------------+

The % wildcard matches any sequence of characters. Use _ to match exactly one character. This is useful when you have many databases and want to find those belonging to a specific project or environment.

Querying information_schema

information_schema.schemata holds one row per database. Querying it directly gives you more flexibility than SHOW DATABASES.

SELECT schema_name
FROM information_schema.schemata
ORDER BY schema_name;

Excluding system databases

To see only your application databases, filter out the built-in ones:

SELECT schema_name
FROM information_schema.schemata
WHERE schema_name NOT IN (
  'information_schema',
  'performance_schema',
  'mysql',
  'sys'
)
ORDER BY schema_name;

Getting database size

information_schema.tables stores size data per table. Aggregate by table_schema to get the total size of each database in megabytes:

SELECT
  table_schema AS database_name,
  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;

This covers all tables the current user can see. Databases with no tables will not appear in this query. Combine it with a LEFT JOIN against schemata if you need to include empty databases.

mysqlshow from the shell

mysqlshow lists databases without opening an interactive MySQL session. Run it from your terminal:

mysqlshow -u root -p

Output:

+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| my_app             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

Pass a database name to list its tables:

mysqlshow -u root -p my_app

mysqlshow is convenient for quick checks in scripts or when you want to avoid entering the MySQL client.

Checking if a database exists

Query information_schema.schemata to test for a specific database before creating it or running a script:

SELECT COUNT(*)
FROM information_schema.schemata
WHERE schema_name = 'my_app';

A result of 1 means it exists; 0 means it does not.

In most cases, CREATE DATABASE IF NOT EXISTS is the cleaner option:

CREATE DATABASE IF NOT EXISTS my_app;

This creates the database if absent and does nothing if it already exists. No error, no side effects.

Connecting to a database

After listing your databases, switch to one with USE:

USE my_app;

All subsequent queries run against my_app until you switch again or disconnect.

You can also specify the database in your connection string so you start connected:

mysql -u root -p my_app

In application code, include the database in the DSN. For example, in Python:

conn = mysql.connector.connect(
    host="127.0.0.1",
    user="app_user",
    password="secret",
    database="my_app"
)

Required privileges

Without the SHOW DATABASES privilege, users only see databases they have been granted access to. A user with access to my_app but no SHOW DATABASES privilege will see only my_app in the results.

With SHOW DATABASES granted, the user sees all databases on the server, regardless of whether they have access to the data inside them.

-- Grant access to a specific database
GRANT ALL PRIVILEGES ON my_app.* TO 'app_user'@'localhost';

-- Grant the ability to see all databases
GRANT SHOW DATABASES ON *.* TO 'app_user'@'localhost';

In practice, application users should not have SHOW DATABASES. Grant access to specific databases only.

Quick reference

| Method | When to use | |--------|-------------| | SHOW DATABASES; | Quick list from the MySQL client | | SHOW DATABASES LIKE 'pattern'; | Filter by name pattern | | SELECT schema_name FROM information_schema.schemata | Custom filtering and ordering | | information_schema.tables aggregated by schema | Database sizes | | mysqlshow -u root -p | List from the shell without entering MySQL | | CREATE DATABASE IF NOT EXISTS name | Create only if it does not exist |