MySQL 8: 3 ways to see all databases in the server

Introduction

When managing a MySQL server, it is often necessary to list all databases that exist on the server. Several methods can achieve this, each with its own use case and benefits. This post explores multiple ways to view all databases in MySQL 8, providing step-by-step solutions and insights into their advantages and limitations.

Solution 1: SHOW DATABASES Statement

The SHOW DATABASES statement is the most straightforward way to list all databases on a MySQL server. It is a simple command that can be executed within the MySQL shell.

1. Open the MySQL shell and authenticate as a user with sufficient privileges to see the databases.
2. Type the command SHOW DATABASES; and press Enter.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| myDatabase1        |
| myDatabase2        |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

Notes: This command shows all databases that the current user has the SHOW DATABASES or any global privileges on. Users without these privileges will only see databases they have specific permissions to access. The command is straightforward but limits visibility based on permissions.

Solution 2: mysqlshow Command

The mysqlshow utility is another quick method to list databases from the command line outside the MySQL shell.

1. Open your operating system’s command line interface.
2. Run the mysqlshow command with the necessary authentication flags.

$ mysqlshow -u root -p
Enter password: **********
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| myDatabase1        |
| myDatabase2        |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

Notes: The mysqlshow command is user-friendly for those who favor working outside of the MySQL shell. It has the same permission restrictions as the SHOW DATABASES SQL command and is particularly useful for quick checks. A limitation is that it requires knowing the user’s credentials available through the command line, which might pose security concerns.

Solution 3: Information Schema Query

Querying the information_schema databases table provides a more flexible and detailed approach to listing databases.

1. Open the MySQL shell.
2. Execute a SELECT statement on the SCHEMATA table.

mysql> SELECT schema_name FROM information_schema.schemata;
+--------------------+
| schema_name        |
+--------------------+
| information_schema |
| myDatabase1        |
| myDatabase2        |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

Notes: The information schema provides extensive metadata and can be filtered for custom outputs, such as only showing databases with specific properties. However, it has the same visibility restrictions based on permissions as the other methods. Since it’s a more complex query, it may not be suitable for casual looks but is powerful for in-depth database server analysis.

Conclusion

In MySQL 8, there are several methods to list all databases, each with its unique advantages and use cases. The SHOW DATABASES statement is quick and easy for immediate results. The mysqlshow command offers a simple alternative outside the MySQL shell, while querying the information_schema provides a more detailed and programmatically versatile solution. In any scenario, it’s essential to remember that the access to database listings is governed by the user’s permissions, and as a best practice, passwords should not be exposed in clear text on the command line. By understanding these various methods, database administrators and developers can effectively manage and explore their database environment.