Select Page

Metal storage cubes
[toc]Although there are plenty of tools out there to help you create and manage mySQL databases, sometimes the fastest (or only) way is to use the mysql command line tool.¬†This article will explain how to create a database, create a user for that database and give them the privileges they’ll need to work with the database.

You can perform the following steps in the command line mySql tool via a Linux terminal or SSH connection or in a SQL command window if you’re using PHPMyAdmin or mySQL Workbench. That’s assuming your user has the right to create databases, of course.

What you will need to connect to a MySQL database

To connect to a MySQL database you need to know between two and four pieces of information:

  1. username – This is the username of the user
  2. password – The username’s password
  3. host – (optional) The IP address of the MySQL server
  4. port – (optional) The port number of the MySQL service on the server

If you’re working locally or in a virtual machine (such as VirtualBox or VMWare) you will often be working on the same server that is running the MySQL service. If that’s the case, you won’t need the host and port number.

How to connect to MySQL using the command line client

In a terminal window (or via a remote tool like SSH), you need to enter the command to start the MySQL client. For this you’ll need to know the username and password of a MySQL user and optionally, the database you’d like to work with. Here’s the command if you’re working locally:

mysql -u root -p fish

This example is starting the MySQL client tool as the root user (-u root) and will ask you for your password (because of the -p option) when you press return. The fish database will be used if you log in correctly.

$> mysql -u root -p fish

If you want to connect to a remove database server it’s a little bit more complicated – you will need to tell the command line client the IP address of the MySQL server. If the server was on the IP address 123.123.123.123, you’d connect with a command like this:

mysql -h 123.123.123.123 -u root -p fish

How to show the existing databases

Once you’re logged in, you’ll be able to see what databases already exist by typing the following command:

SHOW DATABASES;

This will show you a list of the databases that currently exist.

Let’s create a new database

Here’s the command to create a new database. I’m going to call the database, “fish”.

CREATE DATABASE fish;

If you type SHOW DATABASES; now, you should see your new database listed.
Before an application such as WordPress can use the database, you’ll need to create a user with the rights to work with the database. You don’t want to use your root user. That would be silly. I’ll be explaining how to create database users in my next article.

Choosing which database to work in

moving_dataMost of the time you’ll need to specify which database you want to work with. Probably not for what we’re going here, but it’s a good habit to get into. Let’s tell MySQL that we want to use the new database we’ve just created.

USE fish;

And that’s it. You’ve learned how to connect to a MySQL database using the command line client, how to create a database and how to find out what databases there are.