MySQL is a free, open-source and one of the most popular database system in the world – its used in virtually every Linux system and powers some of the most popular web apps, including WordPress CMS and many others.
It is known for its quick processing, ease of use and reliability and scalability! It is mainly used with any PHP-based applications to store data within databases.
In this tutorials, we’ll go over several ways to make MYSQL List or Show Databases in Linux-based operating systems.
Below, we will give you several examples and commands to run to successfully have MYSQL List DB’s!
Requirements
- A system running Linux with MySQL/MariaDB installed.
- A root password is configured on your system.
List MySQL DB’s
You can list MySQL databases based on the user have some kind of a privilege granted to.
Show All MySQL Databases
To list all databases available in your system, you will need to log in to MySQL shell with root user as shown below:
mysql -u root -p
Provide your root password to log in the MySQL shell as shown below:
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.64-MariaDB-1ubuntu0.14.04.1 (Ubuntu)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
You can also use the following command to log in to the MySQL shell:
mysql -u root -pyour-password
Next, list all databases with the following command:
MariaDB [(none)]> show databases;
Output:
+--------------------+
| Database |
+--------------------+
| information_schema |
| drupal |
| magento |
| mysql |
| nextcloud |
| performance_schema |
| wordpressdb |
+--------------------+
7 rows in set (0.01 sec)
Example:
MYSQL List All Tables in DB
If you want to list available tables inside any databases (in this case, mysql), run the following commands:
First, change the database to mysql with the following command:
MariaDB [(none)]> use mysql;
Output:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Next, list available tables with the following command:
MariaDB [mysql]> show tables;
Output:
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
Example:
List Specific User’s MySQL Databases
You can list specific user’s databases based on the user privileges for that database.
Firt, log in to the MySQL shell with a specific user as shown below:
mysql -u wordpressuser -pwordpresspassword
After login, run the following command to list all databases:
MariaDB [(none)]> show databases;
You should see the following output:
+--------------------+
| Database |
+--------------------+
| information_schema |
| wordpressdb |
+--------------------+
Example:
List MySQL Databases From Command Line
You can also list all the databases without login to the MySQL shell.
To do so, open your terminal and run the following command:
mysql -u root -pvyom@123 -e 'show databases;'
Output:
+--------------------+
| Database |
+--------------------+
| information_schema |
| drupal |
| magento |
| mysql |
| nextcloud |
| performance_schema |
| wordpressdb |
+--------------------+
Example:
You can also list all databases using mysqlshow command as shown below:
mysqlshow -u root -pvyom@123
Output:
+--------------------+
| Databases |
+--------------------+
| information_schema |
| drupal |
| magento |
| mysql |
| nextcloud |
| performance_schema |
| wordpressdb |
+--------------------+
Example:
List All MySQL Databases using PhpmyAdmin
You can also list all MySQL databases with the PhpMyAdmin GUI tool.
First, you will need to install PhpMyAdmin package on your system.
You can install it with the following command:
apt-get install phpmyadmin
Once the installation has been completed, open your web browser and type the URL http://your-server-ip/phpmyadmin. You should see the PhpMyAdmin login page:
Provide your MySQL root username and password, and click on the Go button to log in to the PhpMyAdmin web interface as shown below:
From here, you should see all MySQL databases in the left pane.
Conclusion
In the above tutorial, you’ve learned how to List All MySQL Databases using command line and web-based interface, PhpMyAdmin. We hope you have enough understanding of how to list all databases of a specific user and tables of a DB as well!
Feel free to ask any questions or comments below!