Summary: in this tutorial, you will learn how to list all the available databases in the MySQL server using the command line and MySQL Command Line tool.
As a database developer or administrator, the first thing you want to do after logging into the MySQL server is to list all the databases in the MySQL server. Also, you may want to list all available tables in a particular database or schema and user accounts and their privileges.
SHOW DATABASES in MySQL
The easiest way to list all the databases available in MySQL is by logging into the MySQL server using mysql client and issuing SHOW DATABASES command.
SHOW DATABASES;
To list all databases in the MySQL database server, first log in to the database server as follows:
mysql -u root -p
It will ask you for the root password.
Enter password:
You give the correct root password of the MySQL server and press enter. If everything goes well then you will see mysql> on the screen. Now, use the SHOW DATABASES command:
mysql> show databases; +--------------------+ | Database | +--------------------+ | companydb | | information_schema | | mysql | | performance_schema | | sys | | world | +--------------------+ 6 rows in set (0.03 sec)
In MySQL, the schema is the synonym of the database. So, you can use the SHOW SCHEMAS command as well to list all databases in MySQL server.
SHOW SCHEMAS;
The output will be the same as when using the SHOW DATABASES command:
mysql> show schemas; +--------------------+ | Database | +--------------------+ | companydb | | information_schema | | mysql | | performance_schema | | sys | | world | +--------------------+ 6 rows in set (0.01 sec)
Filtering the result
Suppose, you don’t want to list all the available databases in the MySQL server. But you are more interested in listing only those databases which have certain patterns. In that case, you use the LIKE clause to filter the output of the SHOW DATABASES command to a specific pattern.
SHOW DATABASES LIKE pattern;
For example, the following command returns database(s) that ends with the string ‘db‘;
mysql> show databases like '%db'; +----------------+ | Database (%db) | +----------------+ | companydb | | customerdb | +----------------+ 2 rows in set (0.00 sec)
If you want to make a more complex search you query the schemata table from the information_schema database which contains information about all databases.
For example, the following statement returns database(s) that end with either ‘db‘ or ‘schema‘.
mysql> select schema_name -> from information_schema.schemata -> where schema_name like '%db'OR -> schema_name like '%schema';
This will show the output as below:
+--------------------+ | SCHEMA_NAME | +--------------------+ | information_schema | | performance_schema | | companydb | | customerdb | +--------------------+ 4 rows in set (0.00 sec)
Show MySQL Databases from the Command Line
You can list all the databases available in the MySQL server without logging into the MySQL server from the command line itself using the -e flag followed by the SHOW DATABASES command.
This is especially useful when you want to work with your MySQL databases using shell scripts.
Run the following command on your command line terminal to show a list of all databases:
mysql -u user -p -e "show databases;"
The above command gives the following output:
+--------------------+ | Database | +--------------------+ | companydb | | customerdb | | information_schema | | mysql | | performance_schema | | sys | | world | +--------------------+
You can also use mysqlshow command to show the list of databases.
mysqlshow -u root -p"password"
The output will be the same as above.
+--------------------+ | Database | +--------------------+ | companydb | | customerdb | | information_schema | | mysql | | performance_schema | | sys | | world | +--------------------+