MySQL - show installed databases

The next few articles are aimed at showing the basics terminal commands for controlling and investigating MySQL.

This article starts at the beginning (always a good start point) and explains how to list the MySQL databases.


Login

First thing is to log into your Slice via the terminal or PuTTY, etc and then log into MySQL:

mysql -u root -p

You will be prompted for your MySQL root password (note this is not the same as the Slice root password).

Show databases

Do note that all MySQL commands must end in a semi-colon (;) so that it knows that is the end of the command and to execute it.

We want to show the databases and the command is simply:

SHOW databases;

The command does not have to be in capitals - I use capitals for clarity.

On my test Slice running Ubuntu Hardy the output is as follows:

mysql> SHOW databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
+--------------------+
2 rows in set (0.00 sec)

As you can see, the default installation on Ubuntu Hardy has two databases: information_schema and mysql.

Both are required for the base running of MySQL and should not be adjusted by hand.

If you are using a different distribution you may see a third database called 'test'.

This should be deleted as soon as possible and the next article deals with creating and deleting databases.

Summary

MySQL is actually very easy to control via the command line and, once used to the commands, can save the overhead of running something like PHPMyAdmin to administer.

PickledOnion

Article Comments:

Jon commented Thu Aug 28 03:52:15 UTC 2008:

PickledOnion, I appreciate your clear writing style and I'm sure if I put in the effort, I could administer MySQL from the command prompt. But I don't want to put in the effort when PHPMyAdmin makes it easy. I have enough learning curves to ascend and things to remember without having to needlessly add more. I couldn't find PHPMyAdmin in Aptitude. How do I install it?

Jon commented Thu Aug 28 06:18:23 UTC 2008:

OK I found and installed phpMyAdmin - aptitude install phpmyadmin (duh).

Then I configured it:

ln -s /usr/shared/phpmyadmin /var/www/phpmyadmin cd /usr/share/phpMyAdmin cp libraries/config.default.php config.inc.php nano -w config.inc.php Set the user and password to same as MySQL & saved.

But when I go to My_IP_Address/phpmyadmin I get a 403 Forbidden. Any ideas?

Eric commented Thu Jan 22 00:01:14 UTC 2009:

Thanks for the article, quick question, why should we drop the 'test' database? Security, space saving, ??

robert commented Thu Mar 26 23:20:33 UTC 2009:

for you user(s) running centos or fedora10, here is a great article to help you set up mySQL. go >> url http://fedorasolved.org/Members/opsec/installing-configuring-mysql-server

Riko commented Thu Oct 22 20:17:36 UTC 2009:

Alternatively, from command line can use mysqlshow. Eg:

mysqlshow -p

Want to comment?


(not made public)

(optional)

(use plain text or Markdown syntax)