How to List Databases in MariaDB

We will now talk about a few practical MariaDB commands. Since MariaDB is merely a forked-out version of MySQL, these very basic commands will get you started using MariaDB. You can also use them with MySQL.

In this tutorial, I will walk you through exactly how to list databases in MariaDB. We will go beyond the basic commands and explore filtering, scripting automation, and the internal architecture of the Information Schema.

How to List Databases in MariaDB

Understanding the MariaDB Environment

Before we execute any commands, it is crucial to understand what we are actually asking the server to do. In the context of MariaDB (and MySQL), a “database” is essentially a namespace or a container that holds tables, views, stored procedures, and other objects.

Interestingly, in the SQL standard, these are often referred to as “schemas.” In MariaDB, the keywords DATABASE and SCHEMA are synonymous. When I ask the server to “list databases,” I am asking for a directory of these high-level containers.

Prerequisites for Listing Databases

To follow along with the concepts in this guide, you should have the following understanding:

• Access to a Terminal: Whether you are using PowerShell on Windows, Terminal on macOS, or a Linux shell.
• MariaDB Server: A running instance of MariaDB (local or remote).
• User Credentials: You need a username and password. Crucially, your user must have the SHOW DATABASES privilege.

Method 1: The Standard SQL Command

The most common, platform-agnostic way to view your databases is through the MariaDB command-line client using standard SQL syntax. This is the method I use 90% of the time because it works regardless of the operating system.

Connecting to the Server

First, you typically log in using the standard client command:

Bash

mariadb -u username -p

Once you are inside the MariaDB shell (indicated by the MariaDB [(none)]> prompt), you are ready to query.

The Basic Syntax

To list all databases available to your current user, the command is straightforward:

SQL

SHOW DATABASES;

After executing the above query, I got the expected output as shown in the screenshot below.

Alternatively, because of the synonymy I mentioned earlier, you can also use:

SQL

SHOW SCHEMAS;

Both commands return a table format listing the database names.

Filtering Results with Pattern Matching

In enterprise environments, I have worked on servers hosting hundreds of tenant databases (e.g., client_nyc_db, client_la_db, client_chicago_db). Running a raw SHOW DATABASES command can flood your screen.

To manage this, MariaDB allows us to use the LIKE clause with wildcards.

• The Percent Sign (%): Matches any sequence of characters.
• The Underscore (_): Matches exactly one character.

The syntax is given below.

SHOW DATABASES | SCHEMAS
    LIKE 'pattern' | WHERE expression

Where,

• SHOW DATABASES | SCHEMAS: It is the command to show all the databases of the MariaDB server.
• LIKE ‘pattern’: When used alone, the LIKE clause specifies which database names should be matched.
• WHERE expression: The WHERE and LIKE clauses can be used to pick rows using more general criteria.

For example, if I only want to see databases that start with the word “test”:

SQL

SHOW DATABASES
LIKE "c%";

This is incredibly useful for auditing specific subsets of data infrastructure.

The command SHOW DATABASES is used to view all the databases on the MariaDB server. A similar term to SHOW DATABASES is SHOW SCHEMAS.

Method 2: Querying the Information Schema

While SHOW DATABASES is great for humans, it is not always the best for application logic or complex reporting. For that, I prefer querying the information_schema directly.

The information_schema is a virtual database that stores metadata about your server. Think of it as the database that stores data about your databases.

The SCHEMATA Table

Within the information_schema, there is a table called SCHEMATA. This table contains the list of databases and specific details about them, such as the default character set and collation.

Here is how you query it:

SQL

SELECT SCHEMA_NAME 
FROM information_schema.SCHEMATA;

Why Use This Over SHOW DATABASES?

You might ask, “Why write a longer command for the same result?” Here is why I often prefer this method for documentation and scripts:

1. Filtering Power: You can use standard WHERE clauses, which are much more powerful than the LIKE operator.
2. Metadata: You can retrieve the character set (e.g., utf8mb4) alongside the database name.

Table: Comparison of Output Details

Feature	SHOW DATABASES	SELECT FROM SCHEMATA
Output Format	Simple List	Customizable Table
Filtering	LIKE (Pattern matching)	WHERE (Complex logic)
Metadata	Name only	Charset, Collation, Path
Standard Compliance	MariaDB/MySQL specific	SQL Standard

Read: MariaDB Vs SQL Server

Method 3: Command Line Utilities (No Login Required)

Sometimes, you do not want to log into the database shell interactively. Perhaps you are writing a Bash script to automate nightly backups, or you are a SysAdmin running a quick check.

MariaDB provides binary tools that can list databases directly from your OS terminal.

The mariadb-show Tool

Historically known as mysqlshow, this utility is part of the standard client package. It is a quick way to see the structure of your data.

To list databases without entering the shell:

Bash

mariadb-show -u username -p

This will prompt for a password and then immediately output the table of databases to your terminal standard output (stdout).

Executing via the -e Flag

Another technique I use heavily in CI/CD pipelines is the execute flag (-e). This allows you to pass a SQL string to the client, run it, and exit immediately.

Bash

mariadb -u username -p -e "SHOW DATABASES;"

This is particularly useful if you are using tools like grep to search for a database name within a script:

Bash

mariadb -u root -p -e "SHOW DATABASES;" | grep "production"

Understanding the Default Databases

When you first list databases on a fresh MariaDB installation, you will notice several entries that you did not create. I often see beginners try to delete these. Do not do that.

Here is a breakdown of what you will see and why they exist:

• information_schema: As discussed, this is the metadata dictionary. It is read-only.
• mysql: This is the heart of the system. It contains the grant tables (users and privileges). If you delete this, you lock yourself out.
• performance_schema: This is used for low-level monitoring of server execution. It helps in performance tuning.
• sys: A set of views that helps interpret the performance_schema data in a more human-readable format.

Safety Rules for Default Databases

• Never use DROP DATABASE on mysql or information_schema.
• Do not create tables inside information_schema.
• You can modify mysql user tables, but it is safer to use GRANT and CREATE USER commands rather than direct manipulation.

Graphical User Interfaces (GUIs)

Many developers prefer a visual representation. In the US market, several tools are dominant for managing MariaDB.

Workbench and DBeaver

If you are using a tool like MySQL Workbench or DBeaver, listing databases happens automatically in the “Schema Navigator” or “Database Explorer” panel usually located on the left side of the window.

Under the hood, these tools are simply running the SHOW DATABASES or querying information_schema periodically to refresh that view. If you hit “Refresh” in your GUI and a database doesn’t appear, the same troubleshooting rules regarding permissions apply.

Conclusion

Listing databases in MariaDB is a foundational skill, but as we have explored, it is not just about memorizing a single command. It is about understanding the architecture of the database server, how permissions govern visibility, and how to utilize the Information Schema for advanced metadata auditing.

Whether you are managing a small startup database or a massive distributed cluster, the ability to accurately view and filter your data repositories is the first step toward effective database administration.

Get comfortable with the information_schema. While SHOW DATABASES is quick and easy, the schema tables hold the true power for anyone looking to automate and professionalize their database workflows.

Frequently Asked Questions

Q: Is there a difference between SHOW SCHEMAS and SHOW DATABASES? A: No. In MariaDB, they are aliases for the exact same command. You can use them interchangeably.

Q: Can I see the size of the databases when listing them? A: The standard SHOW DATABASES command does not show size. To see the size, you must query the information_schema.TABLES table and sum up the data length and index length, grouping by the table schema.

Q: Does listing databases affect server performance? A: Negligible. Querying the list of databases is an extremely lightweight operation, even on servers with hundreds of schemas.

Q: I am using MariaDB on Azure. Is the process different? A: The SQL commands are identical. However, depending on the managed service tier, you might not see the underlying system databases that Microsoft manages for you.

You may also like to read the following SQL Server tutorials.

• MariaDB Set Auto Increment Value
• MariaDB Alter Table Add Index
• MariaDB Add Auto Increment Column