How to List All Databases in MySQL

Sometimes you just need to know which databases you have created on your MySQL server. This guide will focus on how you can list all databases in MySQL.

List All Databases in MySQL

In order to list ALL databases that are present on your MySQL server, you need to login with an user that has access to all databases, the most common one is root. Otherwise, the user you login as will only show you the database that specific user has access to.

SHOW DATABASES;

First, login to your MySQL server: (replace user with your MySQL username)

mysql -u user -p

Then to list all the databases, input the following command on the MySQL shell:

SHOW DATABASES;

example:

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database       |
+--------------------+
| information_schema |
| local_dev_DJw8bUtm   |
+--------------------+
2 rows in set (0.004 sec)

MariaDB [(none)]>

SHOW SCHEMAS;

An alternative for the SHOW DATABASES command is the SHOW SCHEMAS command:

SHOW SCHEMAS;

output

MariaDB [(none)]> SHOW SCHEMAS;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| local_dev_DJw8bUtm    |
+--------------------+
2 rows in set (0.000 sec)

Filter results

LIKE

If you have many databases, you can filter out results with the help of the LIKE clause, followed by the pattern or name you are looking to filter. In the following example we will list all the databases that have the local name on them:

SHOW DATABASES LIKE filter;

SHOW DATABASES LIKE 'local%';

Do note that we use a percent sign % to represent zero, one or more than one characters. In our case, our search only included local instead of the full database name, meaning that if we did not include the percent sign it would only list databases called local. But because we included the % sign after the pattern or name, it will list all databases with the word local:

output

MariaDB [(none)]> SHOW DATABASES LIKE 'local%';
+------------------+
| Database (local%) |
+------------------+
| local_dev_DJw8bUtm |
+------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> 

Grep

Alternatively, you can use the grep command to filter your results from the command line itself:

linuxify@server:~# mysql -u root -p -e 'show databases;' | grep local

output

linuxify@server:~# mysql -u root -p -e 'show databases;' | grep local
Enter password: 
local_dev_DJw8bUtm
linuxify@server:~#

As we only have a single database that has the specified keyword, it only listed that one.

Summary

In conclusion, this guide aimed to show you how to list all databases in MySQL in different ways, the most commonly used one which is by using the SHOW DATABASES command, but as well we showed how to do it through the command line with the help of the grep command.

If you’d like to learn more MySQL commands, click here.