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.

🧑‍💻 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.