MySQL - listing installed databases

Now we have a root password set, let's see what databases are installed by default in MySQL.

Let's get stuck in straight away. SSH into your VPS and then log into MySQl:

mysql -u root -p

Enter your password (if you set one).

This series of MySQL tutorials will be using MySQL version 5.x. If you are using an earlier version some of these commands may not work as suggested. This is because MySQL v5.x introduces new features and new commands. However, I will try to ensure they are as compatible as possible.

There are two ways of listing the databases. Firstly:

SHOW databases;

Fairly simple but do note the semi-colon (;) at the end of the MySQL command. All commands in MySQL require a semi-colon at the end. This lets MySQL know that you have finished typing the command and to execute what you have typed.

That may seem odd but the commands can span several different lines and only when MySQL encounters the semi-colon will it execute the code.

The output on my test machine is as follows:

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

This tells me that I have two databases installed by default: information_schema and mysql.

We're not going to worry about those right now. We're only seeing what's there.

The second way of listing the databases in MySQL is very similar except it uses the word 'schemas' instead of 'databases'. This is simply another way of referring to a database.

SHOW schemas;

The output is exactly the same:

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

In general, we will be using the word 'database' instead of 'schema' as although they mean the same thing in MySQL, I have been reliably informed (thanks Ron) that 'schema' can, and does, mean slightly different things on other database software such as postgresql.

Just something to keep in mind if you do use other DB software.

If you are using a different OS than Debian Etch you may well see a third database named 'test'.

It is a very good idea to delete this straight away and the next article concentrates on creating and deleting schemas.

PickledOnion

Digg it | del.icio.us | reddit | StumbleUpon