How To Show a List of All Databases in MySQL

Learn how to manage content in MySQL, an open-source database management system. By using the Structured Query Language (SQL), you can easily access and manage content in all your 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 or Ctrl-Alt-F2)

Show MySQL Databases

To show databases in MySQL, use the MySQL Client.

1. Log into your MySQL client. If necessary, connect to a remote server using an SSL connection.

Open a terminal window and enter the following command:

mysql –u username –p

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

The prompt changes to the following:

mysql>

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

SHOW DATABASES;

Make sure to include the semicolon at the end.

You can also use:

SHOW SCHEMA;

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.

Filter Database List

You can display a partial list of the databases. This is helpful if you have a long list, or are looking for a specific database name. Enter the following:

SHOW DATABASES text_string;

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

SHOW DATABASE CUSTOMER;

Shows all databases named CUSTOMER

SHOW DATABASE CUST%;

Show all the databases that start with the letters CUST

How to Show all MySQL Databases From Command Line

You can do all the work in a single line, as follows:

mysql –u username –p password –e “show databases;”

• mysql – This launches the MySQL shell
• –u username – Replace username with the actual username for the database
• –p password – Replace password with the actual password for the user
• e – Executes the following statement, then exits MySQL shell
• “show databases;” – Specifies the command to execute

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 on Databases will display the tables in the right-hand pane. A list of commands appears next to each table, replacing terminal commands. A menu bar (between the list of tables and your browser’s address bar) gives a list of main commands.

Conclusion

In 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.