If you ask a couple of MySQL database admins to list down their most frequent activities, chances are that listing all available databases would be in the top three.
Even novice users should know how to show and list all databases in the MySQL environment. This is often the first action that gives the users an overview of how the system is set up and the available databases.
This comprehensive guide will discuss how to show and list all databases in a MySQL environment using the MySQL list databases statement at the command-line and graphical interface.
Table Of Contents
How to Show and List All Databases in MySQL
Let’s dive into the technical aspects of how to show and list all databases in MySQL. We’ll start with the prerequisites.
Prerequisites
Before you go ahead with the commands described below, make sure you have the following:
- A system running Linux and a MySQL server running
- A root user with superuser access to the MySQL database
- Access to a terminal and/or GUI
Show MySQL Databases
Launch the terminal and enter the following command to log into the MySQL shell:
# mysql -u username -p.
Remember to replace username with your user name. Alternatively, you can use the user root. Next, the command will prompt you for a password.
Now, use the following statement to see all the databases:
mysql> SHOW DATABASES;
The output shows a table with a list of database names.
Note: You can combine these two steps into the following single command that logs into the MySQL shell and displays the available databases.
# mysql -u username -p password -e "show databases;
Remember, using this command might reveal your password, so be careful when using it in the open.
You can also display the database schema with the following statement:
mysql> SHOW SCHEMAS;
It is important to note that In MySQL, a schema does the same job as a database. However, in other DBMSs, a schema might just be a section within a database.
MySQL Database Filtering and Listing With Pattern Match
If you have multiple databases, you might want to narrow down the results using the LIKE statement. The syntax of this statement is:
mysql> SHOW DATABASES LIKE "example_string";
Instead of “example_string,” put in the exact letters or words you’re looking for. For instance:
mysql> SHOW DATABASES LIKE "wordpress";
This statement displays databases that contain “wordpress; in their name.
You can also use the wildcard symbol (%) to broaden your search and find the right database. For instance, try out the following statement:
mysql> SHOW DATABASES LIKE "%schema";
The result displays all the databases with names ending with “schema.”
Display All MySQL Databases using GUI
While running the MySQL list databases is the fastest way, in most cases, servers hosting MySQL databases use phpMyAdmin for database management. You may find an alternative database management software such as MySQL Administrator.
If that’s the case with your server, you can launch the tool and use the GUI to view and manage MySQL databases.
Also Read: How To Rename a Database in MySQL: 3 Easy Methods
Also Read: 3 Simple Methods to Delete Duplicate Rows in MySQL Database
Also Read: How to Import a CSV File Into a MySQL Database in 2 Simple Methods
Conclusion
Whether you’re managing your databases for a project or just exploring, knowing how to list them is handy. We discussed running the MySQL list databases statements at the command-line and GUI-based methods of listing down all databases in a MySQL environment.
If you’re looking for a robust server for your business projects, RedSwitches offers the best dedicated server pricing and delivers instant dedicated servers, usually on the same day the order gets approved. Whether you need a dedicated server, a traffic-friendly 10Gbps dedicated server, or a powerful bare metal server, we are your trusted hosting partner.
FAQs
Q. What is the command to show all databases in MySQL?
The command to show all databases in MySQL is “SHOW DATABASES;”.
Q. How can I list all databases in MySQL using the command line?
You can list all databases in MySQL using the command “mysql -u [username] -p -e ‘SHOW DATABASES;'”.
Q. What is information_schema in the context of databases in MySQL?
In MySQL, information_schema is a virtual database that contains metadata about the server and databases.
Q. How can I list all MySQL databases using a query?
To list all MySQL databases using a query, you can use the SQL statement “SELECT schema_name FROM information_schema.schemata;”.
Q. What is the syntax for the “mysql show databases” command?
The syntax for the “mysql show databases” command is “SHOW DATABASES;”.
Q. How can I connect to the MySQL server and list all databases?
You can connect to the MySQL server using the command “mysql -u [username] -p” and then use the command “SHOW DATABASES;” to list all databases.
Q. What does “rows in set” mean when using the “mysql show databases” command?
The “rows in set” message indicates the number of rows (databases) returned by the “mysql show databases” command.
Q. What is the significance of MySQL’s information_schema and performance_schema databases?
The information_schema and performance_schema databases in MySQL contain metadata and performance-related information, which are crucial for database management and monitoring.
Q. What essential privileges are required to show a list of all databases in MySQL?
To show a list of all databases in MySQL, the user account must have the privilege to access and query the information_schema database, which is typically granted to the root user or users with appropriate permissions.
Q. How do you list databases that have schemata tables?
To list databases that contain the ‘schemata’ table in MySQL, you can use the following SQL query:
SELECT DISTINCT table_schema
FROM information_schema.tables
WHERE table_name = 'SCHEMATA';
This query selects the unique database names (table_schema) where the table name matches ‘SCHEMATA’ within the information schema tables.
