How To Show a List of All Databases in MySQL

Introduction

When managing a MySQL server, you often need to interact with multiple databases. Whether you’re troubleshooting, organizing data, or maintaining server health, quickly accessing a list of all available databases is essential. Fortunately, MySQL provides a simple way to view this list through the MySQL command line interface (CLI). By using specific commands, you can easily retrieve the database names, making your work more efficient. In this guide, we will explore the most effective methods to list databases in MySQL using the MySQL command line and ensure you have a clear understanding of how to navigate your database environment.

Also Read: How to List all MySQL Databases?

Show MySQL Databases

To display a list of databases in MySQL, you’ll first need to access the MySQL command line. If you haven’t already, connect to your MySQL server by using a command like:

mysql -u username -p

Once you’re connected, you can use the SHOW DATABASES command to list all available databases. Simply enter the following:

SHOW DATABASES;

This command will quickly provide a MySQL list of databases, allowing you to view all the databases currently stored on the server. It’s a highly efficient way to see what databases are available and works in virtually any MySQL CLI environment.

For those using mysqladmin, you can also list databases from the command line by typing:

mysqladmin -u username -p show databases

This will give you a similar output as the SHOW DATABASES command. Both methods are simple and effective for managing your database environment.

In addition to showing all databases, you can filter the list by querying system tables if you need more detailed views of specific databases or user permissions. With these basic yet powerful commands, navigating the MySQL console to manage databases becomes straightforward and efficient.

Filtering and Listing a MySQL Database With Pattern Match

In some cases, displaying all databases at once isn’t necessary. Instead, you may want to filter and list MySQL databases that match a specific pattern. This method is especially useful when dealing with numerous databases or searching for specific ones. Using pattern matching in the MySQL command line helps you narrow down the results, making database management more efficient.

To list MySQL databases that match a pattern, you can use the LIKE clause along with the SHOW DATABASES command. Here’s a step-by-step guide to filtering MySQL databases based on a pattern:

1. Login to MySQL Command Line Interface (CLI)

First, log in to the MySQL command line using:

mysql -u username -p

Enter your password when prompted, and you’ll be connected to the MySQL console.

2. Use Pattern Matching with SHOW DATABASES Command

Once connected, you can list databases using a pattern with the SHOW DATABASES LIKE command. For instance, if you’re looking for databases that start with “test”, the command will be:

SHOW DATABASES LIKE 'test%';

• test% is the pattern, where the % acts as a wildcard representing any number of characters that follow.
• This command will display all MySQL databases whose names begin with “test.”

3. Advanced Pattern Matching

You can refine your searches further using more advanced patterns:

• To match a specific prefix, use % after the pattern. For example, SHOW DATABASES LIKE ‘prod%’ will display all MySQL databases starting with “prod.”
• To match a single character, use _. For example, SHOW DATABASES LIKE ‘db_’ will list all MySQL databases whose names are three characters long and start with “db.”

4. Using mysqladmin for Pattern Filtering

For those who prefer using mysqladmin, you’ll still need to connect via the command line. You can then use the SHOW DATABASES command with pattern matching for similar results. However, mysqladmin doesn’t have built-in pattern filtering, so most prefer using the MySQL CLI.

5. Benefits of Pattern Matching

Filtering databases using patterns helps you avoid sifting through a long list of databases. It’s especially beneficial in large environments where you manage multiple instances:

• Speeds up the process of finding specific databases.
• Reduces visual clutter, allowing you to focus only on the relevant MySQL databases.
• Offers flexibility to search by prefixes, suffixes, or specific characters.

6. Examples of Pattern Matching in MySQL

Here are a few practical examples:

Show all databases starting with “dev”:

SHOW DATABASES LIKE 'dev%';

List all databases ending with “prod”:

SHOW DATABASES LIKE '%prod';

Find databases containing “sales” anywhere in their name:

SHOW DATABASES LIKE '%sales%';

Using pattern matching to list MySQL databases offers a powerful way to filter results. Instead of getting a list of all databases, you can focus on just the ones you need, saving time and making your MySQL management more efficient.

Using a GUI to Display All MySQL Databases

While the MySQL command line is a powerful and flexible tool, many users find a graphical user interface (GUI) more convenient for managing databases. GUIs allow you to list MySQL databases without typing commands, providing an easier, visual approach to database management. Some popular tools for this include phpMyAdmin, MySQL Workbench, and HeidiSQL. These interfaces enable you to quickly view databases and perform database operations with just a few clicks, reducing the need to memorize the MySQL CLI commands.

Here’s a breakdown of how each GUI tool works:

1. MySQL Workbench

MySQL Workbench is a widely used GUI for MySQL database management. After connecting to your server, you’ll find a complete list of databases on the left sidebar under the “Schemas” tab. This tab provides an organized view of all databases, schemas, and tables. You can quickly browse through them and perform tasks like querying, modifying, or creating new databases.

• It’s a great alternative to typing the MySQL command line list databases command manually.
• It also supports drag-and-drop features for database management, making it more intuitive for users unfamiliar with the MySQL CLI.
• The ability to list all databases and navigate them easily can save time, especially for developers working with multiple databases.

2. phpMyAdmin

phpMyAdmin is a popular web-based interface used to manage MySQL databases. After logging in, you can show all databases by clicking on the “Databases” tab at the top of the interface. This tool is particularly favored by users managing web hosting accounts because of its integration with many hosting services.

• phpMyAdmin allows you to list MySQL databases without needing to install any software, as long as you have a browser.
• It’s ideal for users who prefer working in a browser-based environment rather than using the MySQL console.
• The interface is highly customizable and also provides database export and import functions.

3. HeidiSQL

HeidiSQL is another powerful GUI for managing MySQL databases. Once you connect to the MySQL server, the list of available databases is displayed in the left sidebar. From there, you can manage, query, and modify your databases easily.

• HeidiSQL supports a range of databases beyond MySQL, such as MariaDB, making it a versatile choice.
• It allows users to list databases in MySQL and perform actions like filtering or querying specific databases, making it a great tool for both beginners and experienced users.
• The interface is lightweight, making it quick to load and responsive, even when dealing with large datasets.

Why Use a GUI for MySQL Database Management?

For users who are less comfortable with the MySQL command line, GUIs offer several advantages:

• No commands to memorize: With a GUI, there’s no need to remember specific MySQL command line show databases or other commands to interact with databases.
• Faster navigation: GUIs provide a structured and visual representation, allowing users to see and list all MySQL databases at a glance.
• Built-in query builders: Many GUIs include query builders that simplify writing SQL queries, eliminating errors that might occur in the MySQL CLI.
• Backup and restore options: GUI tools often come with easy-to-use features for database backup and restoration, without having to run complex MySQL command line operations.

Also Read: How to Check the MySQL Version with Command in Linux?

Final Words

Managing databases in MySQL requires knowing how to quickly and efficiently list all databases. Whether you’re using the MySQL command line, a GUI tool like phpMyAdmin or MySQL Workbench, or filtering with pattern matching, there are multiple ways to view your databases. The SHOW DATABASES command is a fundamental tool that every MySQL user should know, offering a simple method to display databases directly from the command line.

For those who prefer a graphical interface, GUIs provide an alternative for easier database management. No matter your preference, mastering these methods to list MySQL databases ensures smooth navigation and effective management of your MySQL server. Understanding these basics helps you save time and makes your database operations more organized.

Whether you’re an administrator or developer, having these techniques in your toolkit enhances your efficiency. Make use of the most suitable method for your workflow and keep your MySQL databases at your fingertips!