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.
- 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
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:
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:
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
The general syntax is:
SHOW DATABASES LIKE "test_string";
text_string with the characters you want to search for. For example:
SHOW DATABASES LIKE "mysql";
The output lists all databases named
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
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.
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.