How to List MySQL Databases in the Linux Terminal

Listing MySQL databases via the terminal is a key skill for managing Linux-based hosting environments like VPS or dedicated servers. This guide simplifies the process, showing you how to view all databases, filter results, and troubleshoot issues. With practical examples and tips, it’s perfect for sysadmins or website owners working in SSH environments.

Why List MySQL Databases?

You might need to:

  • Manage multiple websites with separate databases.

  • Verify database creation.

  • Troubleshoot or clean up unused databases.

  • Prepare for backups or migrations.

Step 1: Access the MySQL CLI

To interact with MySQL via the command line, first log in to the MySQL shell:

mysql -u root -p
  • -u root — Specifies the MySQL user. Replace root with another user if needed.
  • -p — Prompts for the password (don’t include your password directly in the command for security reasons).

💡 Tip: If your MySQL root user doesn’t have a password set (not recommended for production), you can skip -p.

 Step 2: List All Databases

Once you’re inside the MySQL shell, simply run:

SHOW DATABASES;

You’ll see output similar to:

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

Each row represents a database stored on the MySQL server. Some of these (like information_schema, performance_schema) are system databases and should not be modified.

Listing Databases Without Entering MySQL

If you prefer to list databases directly from the shell without opening the MySQL prompt, use:

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

This is useful for scripting and automation tasks.

 Filter or Search Specific Database Names

To filter output and find specific database names, you can combine the command with grep:

mysql -u root -p -e 'SHOW DATABASES;' | grep your_keyword

Replace your_keyword with part of the database name. This trick is handy when managing multiple clients or applications.

 Where Are MySQL Databases Stored on Linux?

If you’re curious about physical storage, MySQL databases are typically stored in:

/var/lib/mysql/

Each folder inside corresponds to a database name. Do not modify or delete anything here manually unless you know exactly what you’re doing — always use SQL commands or admin tools.

Common Errors & Fixes

Error: Access denied for user ‘root’@’localhost’

  • Double-check the username and password.
  • Ensure the MySQL service is running:
sudo systemctl status mysql

Error: Command ‘mysql’ not found

  • Install the MySQL client tools:
    sudo apt install mysql-client

Conclusion

Listing MySQL databases in the terminal is quick and essential for managing Linux hosting environments. Using SHOW DATABASES;, filtering with grep, or scripting as shown in the examples, you can efficiently verify and manage databases. With AvaHost’s reliable VPS or dedicated servers, these techniques ensure your database tasks are secure, fast, and streamlined.