How to list all mySQL databases, tables and MySQL users on Linux Server

 
The below commands are very useful to find out the database name, database user name and tables of the database if you have lost those details. You must have server root access to list all databases on the linux server. The commands in this article will work only on localhost, you must use “-h serverIP” option with all commands if you are connecting remotely to mySQL server. Example :

mysql -u root -h 192.168.1.5

In the above command you must replace “192.168.1.5” with your server IP address to connect remotely to mySQL. Remote mySQL users must modify the below commands accordingly.

Download and install “putty” software on your PC to log into linux server via SSH.

1. Log into linux server via SSH as ‘root’ user

2. Just type “mysql” and hit enter to login to mySQL server

mysql

Try the below command if you have set mySQL root password on the server

mysql -u root -p

The above command will ask for MySQL root password and you must enter the mysql root password to login to mySQL server. Your mySQL root password might be stored in “/root/.my.cnf” file or you can reset it if you have forgotten the password.
 

 
mysql -u root -p’mysqlrootpassword’

Use the above command to login to mySQL server and it will not prompt for password.
 

 
3. Now you are in mysql prompt. Run the below command to list all databases.

Command : show databases;

“show databases” command will list all the databases managed by the server.

 

 
In the above server information_schema, cphulkd, eximstats etc are name of the databases.
 

 

You can also use the below command to show all mySQL databases on the server.

mysql -e ‘show databases;’
 

 
Use the below command if you have set mySQL root password.

mysql -u root -p -e ‘show databases;’

OR

mysql -u root -p’password’ -e ‘show databases;’
 

 

---

How to list all mySQL users on a linux server

 
The above steps are for listing the databases on the server. Do the below steps to list all database users on the server.

1. Log into sever via SSH as ‘root’

2. Type “mysql” and press enter to login to mySQL server

3. Run the command “SELECT User FROM mysql.user;” to list all mySQL users on a linux server

command : SELECT User FROM mysql.user;
 

 

 
OR use the below command

mysql -e ‘SELECT User FROM mysql.user;’

Try the below command if you have set mySQL root password.
 

 

---

How to list all tables in a MySQL database

 1. Login to linux server as ‘root’

2. Type “mysql” to log into mySQL server

3. Run “use dataaasename;” to select/access the database

Example :

mysql> use eximstats;
Database changed

Run the below command to list all tables in a database

4. “show tables;” command will list all tables in database.

Show tables command will show all the tables in the database you have selected.

Example :