How To Show a List of All Databases in MySQL

October 13, 2022

Introduction

MySQL is an open-source database management system. By using the Structured Query Language (SQL), you can easily perform various tasks on the database server. A common task in MySQL is to show all databases.

This guide will show you how to list all MySQL Databases via command-line or GUI.

How To List All Databases In MySQL

Prerequisites

  • A MySQL user account and password - MySQL root can be used
  • Access to a command line/terminal window (CTRL+ALT+T)

Show MySQL Databases

To show all databases in MySQL, follow the steps below:

1. Open a terminal window and enter the following command:

mysql -u username -p

Replace username with your username (or root). When prompted, enter the password for that username (Omit the -p if the user doesn’t have a password).

2. To show all available databases enter the following SQL command:

SHOW DATABASES;
MySQL SHOW DATABASES; output

The output lists all the database names in a table.

Note: Run the following command from the terminal to automatically connect and execute the SQL command:

mysql -u username -p password -e "show databases;"

Keep in mind the command exposes your password.

3. Alternatively, show the database schemas with:

SHOW SCHEMAS;
MySQL SHOW SCHEMAS; output

In MySQL, a schema serves the same function as database. In other database applications, though, a schema may be only a part of a database.

Filtering and Listing a MySQL Database With Pattern Match

If the list of databases is long, or you are looking for a specific database name, filter the result using the LIKE statement.

The general syntax is:

SHOW DATABASES LIKE "test_string";

Replace text_string with the characters you want to search for. For example:

SHOW DATABASES LIKE "mysql";
SHOW DATABASES LIKE "mysql"; output

The output lists all databases named mysql.

Alternatively, use the wildcard character (%) to do an approximate search. For example:

SHOW DATABASES LIKE "%schema";
SHOW DATABASES LIKE "%schema"; output

The output shows all the databases that end in schema.

Using a GUI to Display All MySQL Databases

If you use a remote server, the hosting company may offer phpMyAdmin for viewing your databases. Or, your local system may have phpMyAdmin installed (or another tool, like MySQL Administrator).

In that case, your account management control panel gives you the option to launch the GUI tool.

mysql gui displaying databases

In phpMyAdmin, the tools are graphical and labeled. The column on the left shows the list of databases. Clicking Databases in the top bar displays the tables in the right-hand pane.

Conclusion

After reading this tutorial, you now know how to list all databases using MySQL and the command line.

With a good foundation, you are now ready to build on and expand your knowledge of database management systems.

Was this article helpful?
YesNo
Milica Dancuk
Milica Dancuk is a technical writer at phoenixNAP who is passionate about programming. Her background in Electrical Engineering and Computing combined with her teaching experience give her the ability to easily explain complex technical concepts through her content.
Next you should read
How to List All Users in a MySQL Database
November 18, 2019

This simple tutorial analyses the commands used to list all user accounts in MySQL. Learn about additional ...
Read more
How to Check the MySQL Version In Linux
July 11, 2019

It is essential to know which version of MySQL you have installed. The version number helps to determine if a ...
Read more
How To Set Up MySQL Master Slave Replication
May 29, 2019

In cloud computing, master-slave data replication refers to storing the same information on multiple servers.
Read more
How to Connect to MySQL using PHP
May 6, 2019

To access and add content to a MySQL database, you must first establish a connection between the database and ...
Read more
  • © 2022 Copyright phoenixNAP | Global IT Services. All Rights Reserved.