How to show list of MySQL databases

If you are a database administrator, you should familiar with MySQL environment and know how to manage MySQL from command-line. One of the most common task is to show a list of MySQL databases available in the server. There are several to show list of MySQL databases in Linux operating systems.

In this tutorial, we will explain how to show list of MySQL databases in Linux.

List all MySQL Databases From Command Line

1- You can list all databases for a specific user which have some privilege granted to using the following syntax:

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

For example, list all databases for MySQL root user with the following command:

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

Provide your root MySQL password and hit Enter to list all databases as shown below:

 Enter password:  
 +--------------------+
 | Database           |
 +--------------------+
 | booker             |
 | bookman            |
 | books              |
 | guest              |
 | information_schema |
 | movies             |
 | mysql              |
 | performance_schema |
 | sys                |
 +--------------------+

2- Next, list all databases for user hitesh with the following command:

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

Provide your hitesh user MySQL password and hit Enter to list all databases as shown below:

 Enter password:  
 +--------------------+
 | Database           |
 +--------------------+
 | guest              |
 | information_schema |
 | movies             |
 +--------------------+ 

3- You can also list all MySQL databases using mysqlshow command as shown below:

mysqlshow -u root -p

You should get the following output:

 Enter password:  
 +--------------------+
 |     Databases      |
 +--------------------+
 | booker             |
 | bookman            |
 | books              |
 | guest              |
 | information_schema |
 | movies             |
 | mysql              |
 | performance_schema |
 | sys                |
 +--------------------+ 

List all MySQL Databases From MySQL Shell

You can also list all MySQL databases after log in to MySQL shell.

First, log in to MySQL shell with root user as shown below:

mysql -u root -p

Provide your root MySQL password and hit Enter to log in the MySQL shell:

 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 54
 Server version: 8.0.19 MySQL Community Server - GPL
 
 
 Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
 
 
 Oracle is a registered trademark of Oracle Corporation and/or its
 affiliates. Other names may be trademarks of their respective
 owners.
 
 
 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 mysql>

Next, run the following command to list all databases of MySQL root user:

mysql> show databases;

You should see all the databases in the following output:

 +--------------------+
 | Database           |
 +--------------------+
 | booker             |
 | bookman            |
 | books              |
 | guest              |
 | information_schema |
 | movies             |
 | mysql              |
 | performance_schema |
 | sys                |
 +--------------------+
 9 rows in set (0.00 sec)

If you have a long list of databases and want to list a specific database use the following syntax:

show databases like 'string%';

For example, list all databases that start with the letters book using the following command:

show databases like 'book%';

You should see all the databases that start with the letters book in the following output:

 +------------------+
 | Database (book%) |
 +------------------+
 | booker           |
 | bookman          |
 | books            |
 +------------------+
 3 rows in set (0.00 sec) 

List all MySQL Databases Using PHPMyAdmin

If your server is hosted on the remote hosting company and they do not allow command-line access then you can list all databases using the PHPMyAdmin web interface.

1- First, open your web browser and type the PHPMyAdmin URL. After login, you should see the following screen:

2- Now, click on the Databases, you should see all the databases in the following screen:

Conclusion

In the above guide, you learned how to list all databases using command-line, MySQL shell and PHPMyAdmin. You are now ready to expand your knowledge of database management systems.