How to show all MYSQL databases

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

There are multiple cases where theses commands will come in handy, for example listing all the MySQL databases you might need to backup.

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

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 tables similar to this:

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

Filter SHOW 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)

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%';