How to Show a List of All Databases in MySQL

When administering MySQL database servers, one of the most common tasks you’ll have to do is to get familiar with the environment. This involves tasks such as listing databases that reside on the server, displaying the tables of a particular database, or getting information about user accounts and their privileges.

This tutorial explains how to show all databases in a MySQL or MariaDB server through the command line.

Show MySQL Databases

The most common way to get a list of the MySQL databases is by using the mysql client to connect to the MySQL server and run the SHOW DATABASES command.

Access the MySQL server using the following command and enter your MySQL user password when prompted:

mysql -u user -p

Note: If you haven’t set a password for your MySQL user you can omit the -p switch.

From within the MySQL shell execute the following command:

mysql> SHOW DATABASES;

The command will print a list of all the databases for which the user have some kind of a privilege granted to . The output will be similar to this:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| opencart           |
+--------------------+
2 rows in set (0.00 sec)

Another command that you can use to list the databases is SHOW SCHEMAS which is a synonym for the SHOW DATABASES command:

mysql> SHOW SCHEMAS;

The output will be the same as when using the SHOW DATABASES command:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| opencart           |
+--------------------+
2 rows in set (0.00 sec)

Show All MySQL Databases

To list all the databases on the MySQL server you’ll need to login as a user that can access all databases, by default that is the MySQL root user, or set a global SHOW DATABASES privilege.

Log in a MySQL root user:

mysql -u user -p

Run the SHOW DATABASES command:

mysql> SHOW DATABASES;

You’ll see a list of all the databases on the MySQL server:

+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| database_name      |
| mysql              |
| opencart           |
| wordpress          |
| performance_schema |
| sys                |
+--------------------+

7 rows in set (0.00 sec)

Filtering the Result

The LIKE clause can be used to filter the output of the SHOW DATABASES command according to a specific pattern.

mysql> SHOW DATABASES LIKE pattern;

For example, the following statement will return all databases whose names starts with ‘open’:

mysql> SHOW DATABASES LIKE 'open%';

+--------------------+
| Database           |
+--------------------+
| opencart           |
+--------------------+
1 rows in set (0.00 sec)

Note: The percent sign (%) means zero, one, or multiple characters.

If you want to make a more complex search you query the schemata table from the information_schema database which contains information about all databases.

The following statement will give you a list of all databases that starts with either “open” or “word”:

mysql> SELECT schema_nameFROM information_schema.schemataWHERE schema_name LIKE 'open%' OR schema_name LIKE 'word%';

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

Show MySQL Databases from the Command Line

To get a list of the databases without logging in to the MySQL shell you can use either the mysql command with the -e option which stands for executing or the mysqlshow that displays databases and table information.

This is especially useful when you want to work with your MySQL databases using shell scripts.

Run the following command on your terminal to show a list of all databases:

mysql -u user -p -e 'show databases;'

+--------------------+
| Database           |
+--------------------+
| information_schema |
| opencart           |
+--------------------+

Here is an example using the mysqlshow command:

mysqlshow -u user -p

The output will be identical to the one from the previous command.

If you want to filter the output you can use the grep command .

Conclusion

You have learned how to get a list of all databases in your MySQL server.

Feel free to leave a comment if you have any questions.