Listing MySQL databases from the Command Line

Prerequisites

Before you begin, make sure that:

1. MySQL (or MariaDB) is installed on your system.
2. You have access to a MySQL user with the necessary permissions (e.g., root or a user with administrative privileges).

Step 1: Accessing MySQL from the Terminal

To access the MySQL terminal, open your terminal or command prompt and run:

mysql -u [username] -p

• Replace [username] with your MySQL username (commonly root).
• After running this command, the system will prompt you for your password. Enter it and press Enter.

💡Tip: By default, the system user root can connect without specifying a password. Use the following command to connect directly: sudo mysql -u root

If the connection is successful, you will see a MySQL prompt like this:

mysql>

Step 2: Listing Databases

To view all available databases, use the following SQL command in the MySQL interface:

SHOW DATABASES;

When executed, this command returns a list of all databases accessible to the connected user. For example:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| my_database        |
+--------------------+

• information_schema: A system database containing information about your MySQL server’s structure.
• mysql: Stores tables related to users and permissions.
• performance_schema: Provides tools for performance monitoring.
• Custom databases: Any databases you’ve created or used will also appear here.

Step 3: Exiting MySQL

Once you’re done, type the following command to exit MySQL:

EXIT;

Pro Tip: Use a One-Liner Command

You can list databases directly without entering the MySQL interactive interface. Use this command in your terminal:

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

This will immediately display the databases accessible to the user, without requiring an interactive session.