MySQL - create and delete databases

Continuing with administering MySQL from the command line, we can now look at creating and deleting databases within MySQL.

Often, this is one of two sets of commands that are needed (the other being creating users) as many modern frameworks (such as Ruby on Rails) create the tables, etc for you.


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).

From the previous article on showing installed databases we 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)

Summary

Administering MySQL from the command line is not as difficult as you may think and can speed up development.

PickledOnion

Article Comments:

bob commented Tue Apr 28 20:01:30 UTC 2009:

Mr Pickle,

First id like to say, your articles are great!!. Im just about to the end of my first deploy ever.

I do have a question, have you ever come across a MySQL:ERROR Cant read dir of "my_db" (errno 13) when trying to rake the db the first time? i log into my app and try to run the migration and thats the error i get. i have searched everywhere, any ideas?

Thanks again

stephen commented Mon Jul 27 03:36:19 UTC 2009:

I am receiving a similar (errno 13) message when I attempt to create a database.

--

CREATE DATABASE failed; error: 'Can't create database 'mytestdb' (errno: 13)'

I have discovered it's some type of a permission-related issue, but I still have no idea what the solution is. I have essentially followed every Ubuntu Hardy article on here to get to this point. I have pulled up the mysql prompt as a root user, but that doesn't seem to help either.

Thanks.

Want to comment?


(not made public)

(optional)

(use plain text or Markdown syntax)