How to Show a List of All Databases in MySQL

A common first step in MySQL administration is checking which databases exist on the server. The SHOW DATABASES command lists all databases on a MySQL or MariaDB server that your user account has permission to see.

This guide explains how to list databases from the MySQL shell and the command line, filter results with LIKE and information_schema, and troubleshoot common access issues.

Before You Begin #

We assume you already have MySQL or MariaDB installed on your system. This guide applies to MySQL 8.0 and later and MariaDB 10.5 and later.

All examples in this guide are run from the MySQL shell. To access it, run the following command and enter your password when prompted:

mysql -u root -p

The databases you see depend on the privileges granted to the user you log in as. The root user can see all databases by default. Other users will only see databases they have been granted access to .

Syntax #

The general syntax of the SHOW DATABASES command is:

SHOW DATABASES [LIKE 'pattern'];

• Without LIKE, the command returns all databases visible to the current user.
• With LIKE, the command filters the list to databases matching the given pattern.

Listing All Databases #

To list all databases on the server, run the SHOW DATABASES command from the MySQL shell:

SHOW DATABASES;

The output includes both user-created and system databases:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| opencart           |
| performance_schema |
| sys                |
| wordpress          |
+--------------------+
6 rows in set (0.00 sec)

The SHOW SCHEMAS command is a synonym for SHOW DATABASES and returns the same result:

SHOW SCHEMAS;

Filtering Results with LIKE #

The LIKE clause filters the database list by a pattern. The percent sign (%) matches zero or more characters, and the underscore (_) matches exactly one character.

For example, to show only databases whose names start with “word”:

SHOW DATABASES LIKE 'word%';

+--------------------+
| Database (word%)   |
+--------------------+
| wordpress          |
+--------------------+
1 row in set (0.00 sec)

Querying information_schema #

For more complex filtering, query the schemata table in the information_schema database. This allows you to use full WHERE clauses with AND, OR, and other SQL expressions.

The following query returns all databases whose names start with either “open” or “word”:

SELECT schema_name FROM information_schema.schemata
WHERE schema_name LIKE 'open%'
   OR schema_name LIKE 'word%';

+-------------+
| SCHEMA_NAME |
+-------------+
| opencart    |
| wordpress   |
+-------------+
2 rows in set (0.00 sec)

Listing Databases from the Command Line #

You do not need to open the MySQL shell to list databases. Use the -e flag to execute a SQL statement directly from the terminal:

mysql -u root -p -e 'SHOW DATABASES;'

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| opencart           |
| performance_schema |
| sys                |
| wordpress          |
+--------------------+

The mysqlshow command is another way to list databases from the command line:

mysqlshow -u root -p

The output is similar to the previous command.

This approach is useful when you need to list databases inside shell scripts or combine the output with tools like grep .

Quick Reference #

Troubleshooting #

Access denied when running SHOW DATABASES
Your user account does not have the SHOW DATABASES privilege. Log in as root or ask an administrator to grant the privilege with GRANT SHOW DATABASES ON *.* TO 'user'@'host';.

Some databases are missing from the list
The SHOW DATABASES command only displays databases for which your user has some level of access. Log in as root to see all databases, or check your privileges with SHOW GRANTS;.

ERROR 2002: Cannot connect to local MySQL server
The database service is not running. Start it with sudo systemctl start mysql for MySQL installations, or sudo systemctl start mariadb for MariaDB installations, then try again.

FAQ #

What is the difference between SHOW DATABASES and SHOW SCHEMAS?
There is no difference. SHOW SCHEMAS is a synonym for SHOW DATABASES and produces the same output.

Can I see databases owned by other users?
Only if your account has the global SHOW DATABASES privilege or specific privileges on those databases. The root user can see all databases by default.

How do I check which database I am currently using?
Run SELECT DATABASE(); in the MySQL shell. It returns the name of the currently selected database, or NULL if no database is selected.

Does this work with MariaDB?
Yes. MariaDB supports the same SHOW DATABASES and SHOW SCHEMAS commands with identical syntax and behavior.

Conclusion #

The SHOW DATABASES command lists all databases visible to your user account on a MySQL or MariaDB server. Use LIKE for simple pattern filtering, or query information_schema.schemata for more advanced searches. Once you know your databases, you can list the tables inside them, manage databases and users , or query data across multiple tables .

If you have any questions, feel free to leave a comment below.

About the authors

Dejan Panovski

Dejan Panovski is the founder of Linuxize, an RHCSA-certified Linux system administrator and DevOps engineer based in Skopje, Macedonia. Author of 800+ Linux tutorials with 20+ years of experience turning complex Linux tasks into clear, reliable guides.

View author page