How to show a list of all MYSQL databases

In this post, I will show you how to list all databases on MySQL or MariaDB.

There are multiple cases where these commands will come in handy, for example listing all the MySQL databases you might need to backup. Once you know what is on the server, the complete mysqldump guide covers dumping one of them, or all of them at once.

One thing to know before you start: these commands only show the databases your user is allowed to see. A short list is often a permissions problem rather than an empty server, so check MySQL user permissions if a database you expect is missing.

🧑‍💻 All the code in this article is bundled in this Gist.

How to show a list MySQL databases with mysqlshow

mysqlshow is a little client that can be used to easily show databases, tables and even more.

mysqlshow provides a command-line interface to several SQL SHOW statements. See Section 13.7.7, “SHOW Statements”. The same information can be obtained by using those statements directly. For example, you can issue them from the mysql client program.

To show all databases using mysqlshow, run below command in your terminal:

mysqlshow -u YOUR_USER_NAME -p

You will see a list of databases similar to this:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wordpress_project  |
+--------------------+
5 rows in set (0.00 sec)

This comes in handy when you need to execute this from scripts and can easily be filtered from there.

How to show a list MySQL databases with SHOW DATABASES.

First, you'll need to access the MySQL database and then execute the SHOW DATABASES command.

To open the MySQL shell, run the following command:

mysql -u user -p

Then run the following command:

SHOW DATABASES

You will see a list of databases similar to this:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wordpress_project  |
+--------------------+
5 rows in set (0.00 sec)

Filter the list of databases

The SHOW DATABASES command can be filtered using the LIKE clause.

SHOW DATABASES LIKE 'wordpress%';

All databases with a name starting with 'wordpress' will be returned

+--------------------+
| Database           |
+--------------------+
| wordpress_project  |
+--------------------+
1 rows in set (0.00 sec)

How to show a list MySQL databases with SHOW SCHEMAS;

The SHOW SCHEMAS command is a synonym for SHOW DATABASES,

SHOW SCHEMAS;

The output will be the list of all databases, similar to this:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wordpress_project  |
+--------------------+
5 rows in set (0.00 sec)

How to show a list MySQL databases in one mysql command line

To display all MySQL databases in one single command line using the MySQL client, you can directly pass the SQL statement through the command line. Here's the command:

mysql -u username -p -e "SHOW DATABASES;"

You'll need to replace username with your MySQL username. After executing this command, you'll be prompted to enter your password, and then the list of databases will be displayed. If you want to avoid the password prompt and you're running this on a secure and private system, you can include the password in the command (although this is generally not recommended for security reasons):

mysql -u username -ppassword -e "SHOW DATABASES;"

Make sure there is no space between -p and password. This command will output the list of databases immediately.

How to show a list MySQL databases with information_schema

The INFORMATION_SCHEMA database (sometimes referred to as a data dictionary or a system catalogue), provides access to database metadata, such as the name of a database or table, the data type of a column, or access privileges.

This table can also be used to query the list of databases, with more flexibility than the SHOW DATABASES described above.

SELECT schema_name
FROM information_schema.schemata
WHERE schema_name LIKE 'wordpress%' OR
      schema_name LIKE 'wp%';

Which can also be ran in one single command line:

mysql -u username -p -e "SELECT SCHEMA_NAME FROM information_schema.SCHEMATA;"

List all MySQL databases using GUI

For those who prefer a graphical interface to manage MySQL databases, several top-tier GUI clients can simplify the process:

• phpMyAdmin: A widely-used, web-based tool that allows you to manage MySQL databases from your browser.
• MySQL Workbench: The official MySQL GUI, offering extensive tools for database management and development.
• TablePlus: A modern, native GUI client that supports multiple databases, including MySQL.

We compare these and a few others in our roundup of MySQL GUI tools.

Backing up what you just listed

Listing databases is usually step one of something larger. If that something is a backup, you have two routes: write and schedule a MySQL backup script yourself, or hand the schedule to a service. Our automated MySQL backups run the dump for every database on the server and send it to the cloud storage you pick.