How To Show a List of All Databases in MySQL

July 23, 2019

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.

mysql list databases command line

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.

mysql gui displaying databases

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.

Was this article helpful?
YesNo
Vladimir Kaplarevic
Vladimir is a resident Tech Writer at phoenixNAP. He has more than 7 years of experience in implementing e-commerce and online payment solutions with various global IT services providers. His articles aim to instill a passion for innovative technologies in others by providing practical advice and using an engaging writing style.
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
  • © 2021 Copyright phoenixNAP | Global IT Services. All Rights Reserved.