MySQL - Creating and Deleting Databases


This article will walk you through learning how to create and delete database on your MySQL server.

Login

First thing is to log into your Server 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 Server root password).

If you've read our article on showing installed databases you know that there are two on a default Ubuntu Hardy install (there may be a third named 'test' on other distribution) called 'information_schema' and 'mysql'.

Creating a database

Let's start by creating a new database called 'mytestdb':

CREATE DATABASE mytestdb;

The output is as follows:

mysql> CREATE DATABASE mytestdb;
Query OK, 1 row affected (0.00 sec)

Let's have a quick look:

mysql> SHOW databases;

+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
| mytestdb           | 
+--------------------+

3 rows in set (0.00 sec)

You can see the new database we just created.

Dropping a database

Seems easy enough and dropping (deleting) a database is just as easy.

Let's delete the 'mytestdb' database we just created:

DROP DATABASE mytestdb;

Done.

Always nice to check though:

mysql> SHOW databases;

+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
+--------------------+

2 rows in set (0.00 sec)

Cool.

Quicker!

As a sysadmin, you probably want quicker ways of doing things. In this case we had to log into MySQL and then issue the create and drop commands.

Wouldn't it be cool if we could do that all in one command?

No problem.

Start off by logging out of MySQL:

exit;

Now you will be back in the normal terminal.

To create a database in one command is done like so:

mysqladmin -u root -p create mytestdb

As we are now in the normal terminal, we don't need the semi-colon (;) to end the command - we only need that when logged into MySQL.

You will be asked for your MySQL root password and... not much else happens. However, the database has been created (log into MySQl to check).

Dropping a database is just as easy from the command line:

mysqladmin -u root -p drop mytestdb

Again, you will be asked for you MySQL root password.

As we are attempting to execute a destructive command you may see a notice like this:

Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'mytestdb' database [y/N]  y

Database "mytestdb" dropped

In this case, I entered 'y' and was informed 'mytestdb' was dropped.

Of course, the suspicious amongst us can log into MySQL to check and, indeed, the 'mytestdb' has been dropped:

mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
+--------------------+

2 rows in set (0.00 sec)


Was this content helpful?




© 2014 Rackspace US, Inc.

Except where otherwise noted, content on this site is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License


See license specifics and DISCLAIMER