How to List All Databases in Mysql

We will show you how to list all databases in MySQL, i.e how to have a list of all MySQL databases. MySQL is an open-source relational database management system commonly used with web-based applications like WordPress, Magento, etc. In this tutorial, we will show you how to list all databases in MySQL on a Linux VPS.

Before you start listing all tables in MySQL, make sure that you have full root 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. Once you connect to your server via SSH run the following command to check whether you have MySQL database server installed on your VPS and what is the version:

mysql -V

The output of the command should be similar to the one below:

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

Next, to connect to the MySQL database server through the command line as user root run the following command:

mysql -u root -p

MySQL will ask you to enter the password for the MySQL root user. Enter the password and press Enter. If you haven't already set up a password for the MySQL root user, 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 set up the MySQL root password yet, you can do that now using the mysql_secure_installation command without arguments:

mysql_secure_installation

Then, answer the security questions as follows:

Would you like to setup 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 using the MySQL root user and enter your new root password.

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

mysql> show databases;

This command will work for you no matter if you have an Ubuntu VPS or CentOS VPS.

The output of the command should be similar to the one below:

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 all listed here.
Next, if you want to use a specific database and list all tables in it, you can use the following commands:

mysql> use mysql;        

To list the tables, you can use the command below:

mysql> show tables

The tables will be listed in the following format:

mysql> show tables; +---------------------------+ | Tables_in_mysql           | +---------------------------+ | columns_priv              | | db                        | | engine_cost               | | event                     | | func                      | | 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 find out about the structure of a specific table you can use the DESCRIBE statement in MySQL:

mysql> DESCRIBE user;

The output will show you information about each of the columns in the table.

If you want to go a little further and learn more about how to use the MySQL database service you can also check our tutorials on how to show all users in MySQL or how to create a new user in MySQL and grant permissions to a MySQL database. Advanced topics include how to configure remote and secure connections for MySQL as well as how to optimize and repair MySQL databases with mysqlcheck.

---

Of course, you don't have to list all tables in MySQL,  if you are using one of our MySQL VPS Hosting services, in which case you can simply ask our expert Linux admins to help you. They are available 24×7 and will take care of your request immediately.

PS. If you liked this post on how to list all databases in MySQL on a Linux VPS, please share it with your friends on the social networks using the buttons on the left or simply leave a reply below. Thanks.

How to List All Databases in Mysql

Source: https://www.rosehosting.com/blog/how-to-list-all-databases-in-mysql/

Share this post