How to List MySQL Databases via Command Line on Linux

When working with MySQL databases on a Linux system, it’s common to need to quickly view all the databases available in your MySQL instance. You can easily achieve this via the command line interface (CLI). In this article, we’ll walk you through the steps to list MySQL databases using MySQL commands on Linux.

Prerequisites

Before proceeding, ensure that:

1. MySQL Server is installed and running on your Linux machine.
2. You have access to the MySQL root user or a user with sufficient privileges to view the database list.

Step-by-Step Guide

1. Open the Terminal

Start by opening a terminal window on your Linux system.

2. Login to MySQL

To log into MySQL from the command line, you will use the mysql command followed by the -u option for the username and -p option to provide the password.

• -u root: This option specifies the MySQL username. Replace root with your username if it’s different.
• -p: This tells MySQL that you want to enter a password for the user. Once you press Enter, you will be prompted to enter the password.

After entering your password, you should see the MySQL prompt, which looks like this:

3. Show All Databases

Once you’re logged in, to list all the available databases, type the following command at the MySQL prompt:

This will output a list of all the databases available on your MySQL server:

4. Exit MySQL

Once you’ve finished viewing the databases, you can exit the MySQL prompt by typing:

This will return you to the Linux terminal.

Troubleshooting

1. Permission Issues: If you’re using a user account without sufficient privileges, you may not be able to see all the databases. Ensure that the user has either the SHOW DATABASES privilege or is a superuser.
2. MySQL Service Not Running: If you encounter connection errors, verify that the MySQL service is running by using the following command:
   sudo systemctl status mysql


   If it’s not running, start the service using:

   sudo systemctl start mysql


Additional Tips

• Using a Different MySQL Port: If MySQL is running on a non-default port, you can specify the port number using the -P option like this:
  mysql -u root -p -P 3307

• List Specific Database Details: If you want to check the details of a particular database, you can switch to that database using the USE command:
  USE database_name;
SHOW TABLES;


This will list all the tables in that database.

Conclusion

Listing MySQL databases from the command line on a Linux system is a simple process that requires just a few commands. By logging in with the correct privileges and using the SHOW DATABASES; command, you can quickly view the databases hosted on your MySQL server. This is an essential task when managing MySQL servers, especially in multi-database environments.