MySQL List Databases

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:

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;'

Sample outputs:

Fig.01: mysql command in action

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"
🐧 Get the latest tutorials on SysAdmin, Linux/Unix, Open Source/DevOps topics:
CategoryList of Unix and Linux commands
File Managementcat
FirewallAlpine Awall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network Utilitiesdig host ip nmap
OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Managerapk apt
Processes Managementbg chroot cron disown fg jobs killall kill pidof pstree pwdx time
Searchinggrep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNAlpine CentOS 8 Debian 10 Firewall Ubuntu 20.04

ADVERTISEMENTS
5 comments… add one
  • Ramanan Jul 4, 2008 @ 19:04

    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

  • budacsik Sep 20, 2008 @ 14:50

    from script

    mysql -u username -pxxxxxx -Bse ‘show databases’

  • seafangs Feb 7, 2012 @ 16:26

    thanks :)

  • deepak Aug 4, 2015 @ 11:29

    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

  • Makiko Jul 21, 2016 @ 12:05

    Thanks. It was really useful.

Leave a Reply

Your email address will not be published.

Use HTML <pre>...</pre>, <code>...</code> and <kbd>...</kbd> for code samples.