List MySQL Databases: How to Use the SHOW DATABASES Command

Getting a list of MySQL databases that reside on a server may come in handy for various reasons. For example, developers will use it to explore the current environment, and DBAs will need it to carry out routine server maintenance.

In this article, you will get a comprehensive guide on how to show databases in MySQL and filter them according to certain criteria. Read on to find out about the following:

• The syntax of SHOW DATABASES in MySQL
• The SHOW SCHEMAS command to view MySQL databases
• Permissions required to list all databases in MySQL
• How to filter the database list
• How to list all databases in MySQL from the command line
• How to view databases in MySQL with a GUI tool

Common use cases for listing MySQL databases

Listing MySQL databases is a common administrative task with several practical use cases. Here are the most common ones.

1. Database exploration and auditing, used to check what databases exist on a server. It helps understand what's hosted on a server you have just connected to, audit resource usage, or validate environment setups.
2. Permission and access checks, used to verify which databases a user has access to. This is important for enforcing data security and confirming that access control policies are working correctly.
3. Backup and recovery, where you need to determine which databases need to be backed up or restored. Since backup tools may require specific database names, listing them helps automate and validate these operations.
4. Database development and testing may require developers to get lists of databases in order to prevent, for instance, accidental operations on production databases. Additionally, we can mention scripts that often include lists of databases to iterate over. With the said list always at hand, developers can easily apply schema updates across multiple databases or run consistency checks.
5. Maintenance is yet another vital operation, which requires DBAs to identify old, unused, or temporary databases, and clean them up to free up space and improve performance. A quick way to see all databases will definitely be useful here.

How to get a list of databases with the MySQL SHOW DATABASES command

Run the following query to show all databases in MySQL:

SHOW DATABASES;

You can run this statement from the MySQL Command Line Client, MySQL Shell, or any GUI tool that supports SQL (for example, dbForge Studio for MySQL).

MySQL returns the results in a table with one column - Database. The databases are ordered in alphabetical order. The number of rows in the set is the number of databases.

MySQL SHOW DATABASES Syntax & Options

How to view MySQL databases with SHOW SCHEMAS

The SHOW SCHEMAS is an alternative MySQL command that helps get a list of existing databases on a server.

SHOW SCHEMAS;

Similarly to the MySQL SHOW DATABASES statement, SHOW SCHEMAS can be run from the MySQL Command Line Client, MySQL Shell, and dbForge Studio for MySQL.

The outputs returned by MySQL will be identical.

Filtering the database list using SHOW DATABASES LIKE

There might be a lot of databases on the server, and the list can be quite large. In this case, you may benefit from using the LIKE expression along with the MySQL SHOW DATABASES command:

SHOW {DATABASES | SCHEMAS}
[LIKE 'pattern' | WHERE expression]

Suppose, we need to get the list of all databases that start with 'S'. The query will look as follows:

SHOW DATABASES LIKE 's%';

And if we need to list all databases whose names are more than 7 characters long, we can run the following query:

SHOW DATABASES WHERE LENGTH(`Database`) > 7;

How to get a list of all databases in MySQL from the command line

You can also list all databases using the command line. There are three common methods for this.

1. Open the Command Prompt and navigate to the bin folder of your MySQL Server installation directory. Then connect to the server using the mysql -u root -p command. Enter the password and run the SHOW DATABASES command.
2. Open the Command Prompt, navigate to the bin folder of your MySQL Server installation directory, and run the query:

mysql -h <host> -u <username> -p -e "SHOW DATABASES;"

In this syntax:

• -h is the IP address or domain of the remote MySQL server
• -u is your MySQL username
• -p will prompt you for the password
• -e will execute SHOW DATABASES directly from the command line

3. Alternatively, from the same bin folder of your MySQL Server installation directory, run the following query:

mysqlshow -u user -p

List databases in MySQL by information_schema

There is another popular way to show databases in MySQL - to query the required information directly from the schemata table of the MySQL information_schema database. To do that, execute a MySQL SELECT query.

For example, the following statement returns the same result as the SHOW DATABASES command.

SELECT schema_name
FROM information_schema.schemata;

And again, the query can be run from the MySQL Command Line Client, MySQL Shell, or dbForge Studio for MySQL.

Display and manage MySQL database lists with dbForge Studio

dbForge Studio for MySQL is a universal all-in-one GUI tool bound to solve all possible database development, management, and administration tasks. With the Studio, you can work with MySQL and MariaDB databases in an intuitive, well-designed interface.

After you connect to the server, the databases that are hosted on it will be displayed in Database Explorer. To view tables, just expand the database and table nodes.

All the abovementioned commands work well from the Studio's integrated SQL Editor equipped with context-sensitive code completion and instant syntax validation.

More than that, dbForge Studio comes with a Security Manager that helps set up user accounts, their roles and privileges (including access to specific databases).

SHOW DATABASE in MySQL: Useful tips and best practices

To find out which database is currently selected, use the following query:

SELECT DATABASE();

You can read more about this statement on the MySQL Select Database page. In dbForge Studio, the selected database will be shown in the menu ribbon. You can easily switch between the databases using the dropdown.

To see the tables from a selected database, run this query:

SHOW TABLES;

Also note that in dbForge Studio, database tables can be displayed by expanding nodes in Database Explorer.

Why SHOW DATABASES might not display all databases

There are several reasons why the SHOW DATABASES command might not display all databases on your server. These reasons are mostly related to permissions, configuration settings, or server issues.

Reason #1. Insufficient permissions

Users can only see databases for which they have permissions. Thus, if you can't see certain databases, this might happen because you don't have the required permissions or privileges. To check your privileges, you can run the following command:

SHOW GRANTS FOR 'username'@'host';

In this command, username refers to your MySQL user, and host refers to the hostname or IP address from which you connect.

You can grant more permissions to your current user from a user with broader privileges, e.g., a root user.

Here is a brief recap of key user types with the databases they are permitted to access.

mysql -h <host> -u <username> -p -e "SHOW DATABASES;"

#!/bin/bash

HOST="192.168.1.10"
USER="username"
PASS="password"

mysql -h "$HOST" -u "$USER" -p"$PASS" -e "SHOW DATABASES;"

chmod +x list_dbs.sh
./list_dbs.sh

pip install mysql-connector-python

import mysql.connector

conn = mysql.connector.connect(
    host='192.168.1.10',
    user='username',
    password='password'
) 

cursor = conn.cursor()
cursor.execute("SHOW DATABASES")

for db in cursor:
    print(db[0])

cursor.close()
conn.close()

mysql -h <remote_host> -u <username> -p -e "SHOW DATABASES;"

GRANT ALL ON *.* TO 'username'@'%' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;

[client]
user=<username>
password=<password>
host=<remote_host>

mysql --defaults-file=~/.my.cnf -e "SHOW DATABASES;"