MySQL command to show list of databases on server

by on November 28, 2006 · 39 comments· LAST UPDATED March 12, 2008

in , ,

Q. I am new to MySQL database server. How do I show the list of databases on my server? Is there any good GUI frontend exists for the same?

A. You can use mysql command to connect to mysql server and list available databases.

Task: Mysql list databases

mysql is a simple command-line tool. mysql is command line and it is very easy to use. Invoke it from the prompt of your command interpreter as follows:
$ mysql
Output:

mysql>

You may need to provide mysql username, password and hostname, use:
$ mysql --user=your-user-name --password=your-password
mysql>

To list database type the following command

mysql> show databases;
Output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
2 rows in set (0.00 sec)

information_schema and mysql are name of databases. To use these database and to list available tables type the following two commands:
mysql> use mysql;Output:

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

Now list tables:
mysql> show tables;Output:

+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| func                      |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| proc                      |
| procs_priv                |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
17 rows in set (0.00 sec)
mysql>

GUI tools

Since you are new to MySQL, it is better to use GUI tools. Please refer previous article about "GUI Tools for managing MySQL databases server"

TwitterFacebookGoogle+PDF versionFound an error/typo on this page? Help us!

{ 39 comments… read them below or add one }

1 sumudu sanjeewa March 5, 2008 at 9:44 am

gud site for learn

Reply

2 Dog March 25, 2008 at 3:23 am

you can use the -e option to execute some queries on the command line, EG, to list databases:
mysql -e “show databases” -u -p

Reply

3 Michael Sharman August 22, 2008 at 5:47 am

Nice tips, very helpful.

Thanks

Reply

4 lady February 13, 2009 at 1:38 am

how can i know if there is a database server on my pc?
i tried this command:
-e “show databases” -u -p
and i got this error:
ERROR 2006 (HYOOO):MySQL server has gone away
No connection. Trying to reconnect. . .
Connection id: 19
Current database: TCPDUMP

ERROR 1064 (42000): You have and error in your SQL syntax;check the manual to your corresponds to your MySQL server version for the right syntax to use near ‘-e “show databases” -u -p’ at line 1

can anyone help me?

Reply

5 siva October 1, 2012 at 7:23 am

In this above error indicates Mysql error state.so ,u can check to identify correcly connections are established or not,and once u check your transcational data.

Reply

6 Lepe March 3, 2009 at 4:47 am

Hi lady,

I suppose you are using …. Linux? or Windows? if you are using Windows, just go to your control panel and check in “Add/Remove Programs” if you see something like “mysql”. Also you can check in services under Administrative tools.
Now, if you are in Linux,
type “mysql” and then press “TAB” twice and you will see a list of commands like:

mysql mysqlimport
mysqlaccess mysql_install_db
mysqladmin mysqlmanager

then mysql is installed. To know if it is running:

nmap localhost

and you will see a “3306/tcp open mysql”.

Also, if you copy+paste some codes or example from web pages remember that sometimes the quotation mark is changed (looks similar but they are not)

I hope this is helpful

Reply

7 video x ware March 7, 2009 at 4:49 am

Very helpful tips, thanks

Reply

8 video x ware March 7, 2009 at 4:54 am

Another question, man.
How can I delete a database or table under a database such as TEST?

Reply

9 Santanu June 4, 2009 at 7:20 am

i m new to mysql and when i m trying to mysqlcheck -u root –analyze mysql then i m getting error ERROR 1064(42000). is there anybody who will help me to solve this problem? Please help me at the earliest.

Reply

10 joy October 2, 2009 at 12:21 pm

How to setup a working database named ‘RCC’ in a new server. Please send me ur commands

Reply

11 Zaid November 19, 2009 at 2:10 pm

thank you so much really beneficial.

Reply

12 Raja December 4, 2009 at 10:23 am

Its nice to learn

Reply

13 jeevan February 25, 2010 at 6:55 am

At present i am working in one database . i want to know current working database name.give command plz.

Reply

14 Paul Nesbitt July 8, 2010 at 3:17 pm

Hi
@jeevan use

SELECT DATABASE();

Paul

Reply

15 kristof September 16, 2010 at 1:57 pm

Or just do: mysql_show -u yourusername -p

Reply

16 kristof September 16, 2010 at 1:59 pm

sorry that should be: mysqlshow -u yourusernamehere -p (so without underscore)

Reply

17 tejas patel October 26, 2010 at 5:48 am

i am new in mysql. i tried to connection to mysql through php. i think problem in mysql server.
i am use window base, xampp server for this. how to find out my server name, database name,username and password. when u use with servername with localhost they are not running
successfully. i tried for this. i doing this activity last 2 days. but i am fail in this. so u advice me
for above. i want to connect mysql to php. if u can example then best.

Reply

18 abc October 27, 2010 at 12:49 am

This error comes when you write
# mysql
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)

plz help

Reply

19 Ali Abid January 19, 2011 at 10:08 am

Nice flow defined.

Reply

20 Student July 5, 2011 at 1:24 pm

Hello

When you do the command SHOW DATABASE, i would like to know which condition is used for the sort of the output of this command ?

Reply

21 surendra October 19, 2011 at 1:01 pm

how to import database through command line in mysql if sql file is store in other drive

Reply

22 Arun January 3, 2012 at 6:24 am

Loading your data from a file. ( for windows )

mysql> source ‘enter file name with including path’ ;

eg: mysql> source d:/world.sql ; ( use forwards slash )

Reply

23 spedi April 25, 2012 at 1:36 am

Very good way to display all databases. Now I know I have lot of test databases on my server.

Reply

24 Mike May 3, 2012 at 2:15 am

Nice Tutor,,
how to create a simple databases bro ?

Reply

25 subbu May 21, 2012 at 10:21 am

very good

Reply

26 subbu May 21, 2012 at 10:21 am

its very nice

Reply

27 pappu June 24, 2012 at 6:06 pm

ya nice 2 learn….

Reply

28 manoj kumar July 19, 2012 at 10:02 am

its very nice and simple problem are solve

Reply

29 Gilbert July 28, 2012 at 7:22 pm

i’m try to put mysql syntax but it is still error 1064 i dont know why?
Is write a message You have ana error in your sql syntax; check the manual corresponds to your Mysql server version for the right syntax to use near ‘database’ line 1

Reply

30 Prasad August 16, 2012 at 5:17 pm

Cool !!

it makes learning easy :)

Reply

31 suneel jain August 17, 2012 at 10:26 am

how to solve problem MySQL database with j boss AS 7 to Linux OS

Reply

32 Shandeep August 19, 2012 at 7:17 pm

The results which I am getting after executing a query are too messed-up. How do I outline it in a readable or may be in a clear view, I mean in a table format?

Reply

33 al rashid October 16, 2012 at 4:39 pm

helped a lot.

Reply

34 Ali Amjad October 28, 2012 at 10:03 pm

thanks for helping.

Reply

35 mathews February 26, 2013 at 6:02 am

mysql> use mysql;
ERROR 1044 (42000): Access denied for user ”@’localhost’ to database ‘mysql’
mysql> mysql –user=mlpc4 –password=mlpc4
-> i got this error smbdy plz hlp me..

Reply

36 sasa February 28, 2013 at 4:19 pm

You haven’t a permission to use this database

Reply

37 krishna April 19, 2013 at 4:49 am

good, very usefull

Reply

38 ajay gadhavana June 12, 2013 at 9:41 am

really very useful content

Reply

39 ektos October 21, 2013 at 7:06 pm

why test database is there in mysql eventhough it is empty and at the mysql_secure_installation we can remove it.. then what is the use of this..??

Reply

Leave a Comment

Tagged as: , , , , , , , , , , , , , ,

Previous Faq:

Next Faq: