How to list all databases in MySQL

Preamble

​​

You will show you how to list all databases in MySQL. MySQL is an open-source relational database management system commonly used with web applications such as WordPress, Magento, etc. E. In this tutorial, we will show you how to list all databases in MySQL on Linux VPS .
Before you start listing all tables in MySQL, make sure that you have full access to your Linux server, or at least you have a system user with sudo privileges that you can use to connect to your server. After connecting to the server via SSH, run the following command to check if the MySQL database server is installed on your VPS and what the version is:

mysql -V

The result of the command should be similar to the output:

# mysql -V
mysql Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using EditLine wrapper

Then, to connect to the MySQL database server through the command line as the root user, execute the following command:

mysql -u root -p

MySQL will ask you to enter a password for the user root. Enter the password and press Enter. If you have not yet set a password for user root, you can use the following command:

mysql -u root

Of course, it is always recommended to keep your services secure, so if you haven’t already set a password for root in MySQL, you can do it now using the mysql_secure_installation command without arguments:

mysql_secure_installation

Then answer the security questions as follows:

Would you like to setup the VALIDATE PASSWORD plugin?
Press y|Y for Yes, any other key for No: y

Please set the password for root here.
New password:

Do you wish to continue with the password provided? (Press y|Y for Yes, any other key for No) : y

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y

All done!

Then connect to the MySQL database server with the user root and enter the new password root.

To list all databases in MySQL, execute the following command:

mysql> show databases;

This command will work for you whether you have Ubuntu VPS or CentOS VPS.

The result of the command should be similar to the output:

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)

If you have other databases created in MySQL, they will be listed here.
Then, if you want to use a particular database and list all its tables, you can use the following commands:

mysql> use mysql;

To list the tables, you can use the following command: mysql> use mysql:

mysql> show tables

The tables will be listed in the following format:

mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
|
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| innodb_index_stats |.
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
32 rows in set (0.00 sec)

If you want to learn about the structure of a particular table, you can use the DESCRIBE instruction in MySQL:

mysql> DESCRIBE user;

The output will show the information about each column in the table.

Enteros

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of RDBMS, NoSQL, and machine learning database platforms.