How To Show a List of All Databases in MySQL

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.

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;

The output lists all the database names in a table.

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

3. Alternatively, show the database schemas with:

SHOW SCHEMAS;

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";

The output lists all databases named mysql.

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

SHOW DATABASES LIKE "%schema";

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.

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.