MySQL Show/List Databases

Introduction

If you're working with MySQL databases, you'll need to be able to show or list databases, tables, and information of user accounts and their privileges at some point. Whether you're new to MySQL or have been working with it for a while, it's essential to know how to show or list databases.

In this tutorial, you will list or show databases present on the server.

What is MySQL?

MySQL is a relational database management system (RDBMS) that uses SQL (Structured Query Language) to manage and manipulate data. It is one of the most popular open-source databases in use today, with a wide range of applications, including web applications, content management systems, and more.

Show/List MySQL Databases

One of the most common methods of getting the list of MySQL Databases is using the mysql client and connecting to the MySQL server.

1) Use the following command to connect to the MySQL server and then enter your user password:

mysql -u root -p

👉

In case you have not set any password for MySQL then you can use omit -p.

2) Now, execute the following command:

SHOW DATABASES;

3) You will get the output like below:

Output

+--------------------+
| Database           |
+--------------------+
| gitlab             |
| sylius             |
+--------------------+
2 rows in set (0.00 sec)

4) You can also use the SHOW SCHEMAS command. It is a synonym for the SHOW DATABASES command:

SHOW SCHEMAS;

You will get the similar output, you got above.

Show all MySQL Databases

If you want to get a list of all the databases present on the server, either you will have to log in as a user who has access to all the databases (root user) or you can also set the global SHOW DATABASES privilege.

1) Log in as a MySQL root user:

mysql -u user -p

2) Execute the following command:

SHOW DATABASES;

You will get the desired output with the above command.

Filtering the Result

You can filter out the results using LIKE and WHERE clause. With LIKE, you can match a specific pattern, and with WHERE you will get more flexibility to list the database which matches the particular condition in the SQL statement.

You can use the below commands:

For LIKE

SHOW DATABASES LIKE pattern;

For WHERE

SHOW DATABASES WHERE expression;

If you have a complex search requirement, then query the schemata table from the information_schema database. It contains information about all the databases.

Go through the below example to have a clear understanding:

SELECT schema_name
FROM information_schema.schemata
WHERE schema_name LIKE 'mall%' OR 
      schema_name LIKE 'cart%';

The above command will give you a list of all the databases starting with either "mall" or "cart".

Show MySQL Databases via Command Line

You can also get the database details from the Linux shell using the mysql -e or the mysqlshow command. This method can come in handy when you want to work with MySQL databases using shell scripts.

1) Use the following command to get a list of all the databases:

mysql -u user -p -e 'show databases;'

2) You can also use mysqlshow command like below:

mysqlshow -u user -p

Both the commands will have identical output.

Moreover, if you want to filter the output, use the grep command.

FAQs to Show/List MySQL Databases

What is the command to list all MySQL databases?

To list all MySQL databases, you can use the command SHOW DATABASES; in the MySQL shell. This command will display a list of all the databases present in your MySQL server.

How can I check the size of a MySQL database?

To check the size of a MySQL database, you can use the following command in the MySQL shell: SELECT table_schema AS 'Database Name', sum(data_length + index_length) / 1024 / 1024 AS 'Size (MB)' FROM information_schema.TABLES GROUP BY table_schema;. This command will display the size of all databases in your MySQL server in MB.

How do I show a specific MySQL database?

To show a specific MySQL database, you can use the command USE database_name; in the MySQL shell. This command will switch to the specified database and all the commands you execute after this will be executed in the specified database.

Can I list MySQL databases from a MySQL client application?

Yes, you can list MySQL databases from a MySQL client application such as MySQL Workbench or phpMyAdmin. These applications provide a graphical user interface that allows you to manage your MySQL databases easily.

How do I grant a user permission to view a specific MySQL database?

To grant a user permission to view a specific MySQL database, you can use the following command in the MySQL shell: GRANT SELECT ON database_name.* TO 'user_name'@'localhost';. This command will grant the user select permissions on all tables in the specified database. You can replace the SELECT keyword with other MySQL keywords such as INSERT, UPDATE, and DELETE to grant other permissions.