How do I display a list of all databases under MySQL database server running on a Linux or Unix-like system?
You need to use the show databases SQL command. First you need to login as MySQL database root user using mysql command line client. Type the following command to login with a password at a shell prompt:
Adblock detected 😱
My website is made possible by displaying online advertisements to my visitors. I get it! Ads are annoying but they help keep this website running. It is hard to keep the site running and producing new content when so many people block ads. Please consider donating money to the nixCraft via PayPal/Bitcoin, or become a supporter using Patreon.
Advertisements
$ mysql -u USERNAME -h HOSTNAME -p $ mysql -u root -p At mysql prompt type the following command (show databases;): mysql> show databases; Sample output:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| wiki |
+--------------------+
3 rows in set (0.03 sec)
Of course your can use the following shell one liner to get the list of all databases too:
# Connect to the local server
mysql -u root -h localhost -p-e'show databases;'# Connect to the remove mysql server
mysql -u root -h 192.168.1.250 -p-e'show databases;'
# Connect to the local server
mysql -u root -h localhost -p -e 'show databases;' # Connect to the remove mysql server
mysql -u root -h 192.168.1.250 -p -e 'show databases;'
Sample outputs: Fig.01: mysql command in action For your shell script, you can just get a list of all of databases using the following syntax:
mysql -u root -h localhost -p'MyPasswordHere'-e'show databases;'|awk'{ print $1 }'# OR store it in $DBS shell variable DBS=$(mysql -u root -h localhost -p'MyPasswordHere'-e'show databases;'|awk'{ print $1 }')echo"List of database - $DBS"
mysql -u root -h localhost -p'MyPasswordHere' -e 'show databases;' | awk '{ print $1 }'
# OR store it in $DBS shell variable
DBS=$(mysql -u root -h localhost -p'MyPasswordHere' -e 'show databases;' | awk '{ print $1 }')
echo "List of database - $DBS"
The author is the creator of nixCraft and a seasoned sysadmin, DevOps engineer, and a trainer for the Linux operating system/Unix shell scripting. Get the latest tutorials on SysAdmin, Linux/Unix and open source topics via RSS/XML feed or weekly email newsletter.
5 comment
for me some of the databases are not listing wven though complete privilleges are given and i checked same on mysql.db table also .Please help me out
from script
mysql -u username -pxxxxxx -Bse ‘show databases’
thanks :)
Task: Develop a PHPMyAdmin Input: Hostname, Username, Password. Output: List all databases & table names which are associated with the given username & password.please guys,can anyone tell me how to do it?thanks in advance
5 comment
for me some of the databases are not listing wven though complete privilleges are given and i checked same on mysql.db table also .Please help me out
from script
mysql -u username -pxxxxxx -Bse ‘show databases’
thanks :)
Task: Develop a PHPMyAdmin Input: Hostname, Username, Password.
Output: List all databases & table names which are associated with the given username & password.please guys,can anyone tell me how to do it?thanks in advance
Thanks. It was really useful.