How to List All MySQL Databases with Command Line

MySQL, an extensively utilized open-source relational database management system (RDBMS), is favored by developers across the globe for its performance, dependability, and user-friendliness. In this article, we will examine a variety of approaches to display a comprehensive list of all MySQL databases, employing diverse tools and techniques.

Introduction

Prior to delving into the various methods, it is important to gain a fundamental understanding of databases within MySQL. A database is essentially a collection of tables that store data. Each database possesses a unique name, serving as an organizational tool for managing and housing different data types.

Method 1: Using the SHOW DATABASES Statement

1.1 Executing the SHOW DATABASES Command

The SHOW DATABASES command is a simple yet powerful SQL statement used to display a list of all databases on a MySQL server. To execute this command, first connect to the MySQL server using the mysql command line utility, and then enter the following command:

SHOW DATABASES;

Example Output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| your_database      |
+--------------------+

This command will return a list of all databases available on the server. It’s an efficient way to view the databases, especially when you need a quick overview of the server’s contents.

Method 2: Using the mysqlshow Command

2.1 Running the mysqlshow Utility

The mysqlshow command is a standalone utility that comes with the MySQL package. It allows you to display information about databases, tables, and columns directly from the command line without having to connect to the MySQL server using the mysql command line utility.

To list all databases on the server using the mysqlshow command, simply enter the following command in the terminal:

mysqlshow -u username -p

Example Output:

+--------------------+
| Databases          |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| your_database      |
+--------------------+

After providing your password, the command will display a list of all databases on the server.

Method 3: Using the INFORMATION_SCHEMA Database

3.1 Querying the INFORMATION_SCHEMA Database

The INFORMATION_SCHEMA database is a special MySQL database that contains metadata about other databases, tables, and columns. By querying this database, you can retrieve detailed information about the databases on your server.

To list all databases using the INFORMATION_SCHEMA database, first connect to the MySQL server using the mysql command line utility, and then run the following SQL query:

SELECT schema_name FROM information_schema.schemata;

Example Output:

+--------------------+
| SCHEMA_NAME        |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| your_database      |
+--------------------+

This query will return a list of all database names on the server, providing a more detailed perspective on the databases present.

Method 4: Using the Command Line Interface

4.1 Leveraging the Command Line for MySQL Management

The command line interface (CLI) is a versatile and powerful tool for managing MySQL databases. By using the CLI, you can execute various commands and scripts to create, modify, and delete databases, tables, and columns. This method is particularly useful when you need to automate tasks or manage your databases in a non-GUI environment.

To list all databases on the server using the CLI, you can combine the mysql command line utility with other shell commands or scripting languages like Bash or Python. For example, you can use the following command to list all databases:

mysql -u [username] -p -h [hostname] -e "SHOW DATABASES;"

Example Output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| your_database      |
+--------------------+

This command will prompt you for your password and then display a list of all databases on the server.

Conclusion

As a database administrator, it’s essential to know how to list all the databases present in your MySQL server. In this article, we’ve covered four different methods that you can use to accomplish this task. Whether you prefer using the command line interface or the MySQL client tool, these methods are simple and effective.