How to List MySQL Databases via Command Line on Linux

Listing all MySQL databases from the command line is accomplished with a single SQL statement — SHOW DATABASES; — executed after authenticating to the MySQL server using the mysql client. This works on any Linux distribution running MySQL 5.7, MySQL 8.x, or a compatible MariaDB server, and requires either the SHOW DATABASES privilege or superuser access.

This guide goes beyond the basic command. It covers authentication options, privilege-scoped visibility, non-interactive scripting patterns, performance schema filtering, and common failure modes that trip up even experienced administrators.

Prerequisites

Before proceeding, confirm the following:

• MySQL Server or MariaDB is installed and the service is active.
• You have a user account with at least the SHOW DATABASES privilege, or you are authenticating as root.
• The mysql client binary is available in your shell's PATH (verify with which mysql).

If you are managing a remote server — for example, a VPS Hosting environment — also ensure that the MySQL port (default 3306) is reachable or that you are connecting over SSH.

Step 1: Verify the MySQL Service Is Running

Before attempting to connect, confirm the daemon is active:

sudo systemctl status mysql

For MariaDB installations, the service name differs:

sudo systemctl status mariadb

If the service is stopped, start it:

sudo systemctl start mysql

A failed start is almost always explained in journalctl -xe or in /var/log/mysql/error.log. Check those files first before assuming a configuration problem.

Step 2: Authenticate to the MySQL Server

Standard Interactive Login

mysql -u root -p

• -u root — specifies the MySQL username. Substitute root with any valid account.
• -p — prompts for the password interactively. Never pass the password directly on the command line in production (e.g., -pMyPassword), as it is visible in process listings and shell history.

After a correct password is entered, the MySQL prompt appears:

mysql>

Connecting to a Remote Host or Non-Default Port

mysql -u root -p -h 192.168.1.100 -P 3307

• -h — specifies the remote host IP or hostname.
• -P — specifies a non-default port number.

Using an Option File for Automation

For scripts and cron jobs, store credentials in a protected option file instead of embedding them in the command:

# ~/.my.cnf
[client]
user=root
password=YourSecurePassword

Restrict permissions immediately after creating the file:

chmod 600 ~/.my.cnf

With this file in place, mysql authenticates without any flags:

mysql

Step 3: List All Databases

Once inside the MySQL shell, run:

SHOW DATABASES;

Example output on a freshly installed MySQL 8 instance:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

These four system schemas are present on every standard MySQL installation:

User-created databases appear alongside these system schemas.

Step 4: Filter the Database List

Filter by Name Pattern

SHOW DATABASES accepts a LIKE clause, which is useful when a server hosts dozens of databases:

SHOW DATABASES LIKE 'wp_%';

This returns only databases whose names begin with wp_ — a common naming convention for WordPress installations.

Query information_schema for Advanced Filtering

For more precise filtering, query the information_schema.SCHEMATA table directly:

SELECT schema_name, default_character_set_name, default_collation_name
FROM information_schema.SCHEMATA
WHERE schema_name NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
ORDER BY schema_name;

This approach lets you filter by character set, collation, or any other metadata column — something SHOW DATABASES cannot do.

Step 5: Inspect a Specific Database

To examine the contents of a particular database, switch context with USE and then list its tables:

USE database_name;
SHOW TABLES;

To see table sizes, row counts, and storage engines in one query:

SELECT
  table_name,
  engine,
  table_rows,
  ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.TABLES
WHERE table_schema = 'database_name'
ORDER BY (data_length + index_length) DESC;

This is far more actionable than SHOW TABLES alone and is the kind of query a DBA runs before planning a migration or backup strategy.

Step 6: Exit the MySQL Shell

EXIT;

Or use the keyboard shortcut Ctrl+D.

Non-Interactive Usage: Listing Databases from the Shell

In automated pipelines — backup scripts, monitoring agents, deployment hooks — you need to retrieve the database list without entering an interactive session.

One-Liner with -e Flag

mysql -u root -p -e "SHOW DATABASES;"

Suppress the Header Row for Scripting

mysql -u root -p --skip-column-names -e "SHOW DATABASES;" 2>/dev/null

The --skip-column-names flag removes the Database header, producing clean output suitable for iteration in a shell loop:

for db in $(mysql -u root -p --skip-column-names -e "SHOW DATABASES;" 2>/dev/null); do
  echo "Processing: $db"
done

Using mysqlshow as an Alternative

The mysqlshow utility provides a quick overview without entering the MySQL shell:

mysqlshow -u root -p

It lists all databases the authenticated user can see, and accepts a database name argument to drill into table-level detail:

mysqlshow -u root -p database_name

Privilege Scoping: Why Some Databases Are Hidden

This is one of the most misunderstood behaviors in MySQL. When a user lacks the global SHOW DATABASES privilege, SHOW DATABASES only returns databases for which that user holds at least one privilege. This is by design — it is a security boundary, not a bug.

Practical implication: A restricted application user may run SHOW DATABASES; and see only one or two databases, even though the server hosts fifty. This is correct behavior. If a user needs to see the full list, grant the privilege explicitly:

GRANT SHOW DATABASES ON *.* TO 'username'@'host';
FLUSH PRIVILEGES;

Use this grant sparingly. In multi-tenant environments — such as a Shared Web Hosting setup or a multi-client Dedicated Servers deployment — exposing the full database list to application users is a security risk.

MySQL vs. MariaDB: Behavioral Differences

Both MySQL and MariaDB support SHOW DATABASES; and information_schema.SCHEMATA, but there are subtle differences worth knowing:

On newer MariaDB installations, the canonical binary may be mariadb rather than mysql, though a symlink typically preserves backward compatibility.

Common Errors and How to Fix Them

ERROR 1045 (28000): Access denied for user

This means authentication failed. Double-check the username, password, and host. For root on MySQL 8, the default authentication plugin is caching_sha2_password. If your client does not support it, connect via socket:

sudo mysql -u root

This bypasses password authentication when running as the Linux root user, relying on the auth_socket or unix_socket plugin instead.

ERROR 2002 (HY000): Can't connect to local MySQL server through socket

The MySQL daemon is not running, or the socket file path is wrong. Check:

sudo systemctl status mysql
ls -la /var/run/mysqld/mysqld.sock

ERROR 1044 (42000): Access denied for user to database

The user is authenticated but lacks privileges on the target database. Review grants with:

SHOW GRANTS FOR 'username'@'host';

Practical Decision Matrix

Use this reference to select the right approach for your situation:

Key Technical Takeaways

• SHOW DATABASES; is privilege-scoped. A user without global SHOW DATABASES sees only databases they have explicit access to — this is a security feature, not a misconfiguration.
• Never pass passwords as command-line arguments in production. Use -p for interactive sessions and ~/.my.cnf for automation.
• information_schema.SCHEMATA is strictly more powerful than SHOW DATABASES for scripted or filtered queries.
• The mysqlshow utility is underused and provides fast, non-interactive database and table inspection.
• On MySQL 8, socket-based authentication (sudo mysql) is often the fastest path to root access on a local server.
• When managing multiple databases on a VPS with cPanel or another control panel, the panel's database manager is a GUI wrapper around these same SQL commands — understanding the underlying queries gives you full control when the GUI falls short.
• For environments where database access is tied to SSL Certificates and encrypted connections, add --ssl-mode=REQUIRED to your mysql client invocations to enforce TLS in transit.

FAQ

Q: Why does SHOW DATABASES not show all databases on my server?

A: The user account you are authenticated as lacks the global SHOW DATABASES privilege. MySQL only returns databases for which that user holds at least one privilege. Authenticate as root or grant the SHOW DATABASES privilege to see the complete list.

Q: What is the difference between SHOW DATABASES and querying information_schema.SCHEMATA?

A: Both return the list of accessible schemas, but information_schema.SCHEMATA exposes additional metadata columns — default character set, default collation — and supports full SQL filtering with WHERE, ORDER BY, and JOIN. Use SHOW DATABASES for quick interactive checks and information_schema.SCHEMATA for scripted or analytical queries.

Q: How do I list databases on a remote MySQL server without opening an interactive session?

A: Use the -e flag combined with host and port options:

mysql -u root -p -h remote-host -P 3306 -e "SHOW DATABASES;" --skip-column-names

Q: Can I list MySQL databases without knowing the root password?

A: On a local server where you have Linux root access, yes. MySQL's auth_socket (or unix_socket on MariaDB) plugin authenticates based on the Linux OS user. Run sudo mysql and you will be granted access as the MySQL root user without a password prompt.

Q: Does SHOW DATABASES work the same way on MariaDB as on MySQL?

A: The syntax is identical and the privilege scoping rules are the same. The primary practical difference is that MariaDB may not include the sys schema by default, and on newer MariaDB versions the preferred client binary is mariadb rather than mysql, though the mysql alias is typically preserved for compatibility.