Introduction to MySQL on Ubuntu

This post was migrated from my Techblog.

Introduction | Installation | MySQL Basics | MySQL Permissions | Resources

For ages, I avoided — was even a little scared — of databases. I had heard all kinds of horror stories about databases being overused in commercial applications, and I got the impression that I should stay away from them if I didn’t absolutely need one. That wasn’t too hard: if you’re a hobbyist programmer with a decent knowledge of Bash, it’s a long time before you need a database.

But when it came to learning Wordpress development, it wasn’t my choice; that was just the way it was. So I grumbled a little, dragged my feet for a few days, and buckled down and learned some MySQL. In the end, as with most things, it turned out to be not nearly as difficult as I had imagined and sometimes even fun. :)

Introduction

Before we dive into working with MySQL, I want to explain a few things about MySQL and databases in general. If this is old news for you, feel free to skip on over.

First, you might be wondering what the difference is between SQL and MySQL. SQL (which stands for Structured Query Language) is a programming language designed specifically for working with databases. Database servers that want to use SQL will implement the language specification. MySQL is one such database server; others include SQLite, PostgreSQL, Oracle, and SQL Server.

It’s helpful to know how a database is structured. (It’s very simple, but it was by no means obvious to me when I first started out.) You can think of the MySQL server as a big container where everything is stored. Within it, we have a bunch of databases, and each database keeps its data in a collection of tables. Each database usually belongs to a different application, and each can organize their data into tables however is best for them.

The way MySQL is set up, there is a MySQL server and a MySQL client. What’s the difference? The server is where all of your data is stored. The client on the other hand is a program that allows you to access and manipulate that data. This separation of actual data from its representation buys you a lot of freedom. You can choose between several MySQL client applications for working with your data, and you can even do it from someone else’s computer.

This might seem confusing but actually, it’s a very familiar concept. Think of the the internet. You can view a webpage using whichever browser you please. You can also see the webpage from a different computer than the one where the actual files are stored (obviously, this site isn’t on your machine; it’s on Bluehost’s server somewhere far away). The file system also works this way. You can browse the same data using Nautilus or the terminal, and you can ssh into other computers to view their data.

(Hopefully, that makes sense. If not, try the explanation here.)

As I mentioned, there are a variety of MySQL client applications available. I’m not very familiar with the options — being a terminal junkie, I just use the command-line interface (and that’s what I’ll be going over in this tutorial). However, if that’s not your cup of tea, there are a variety of graphical applications available, the most notable of which is phpMyAdmin, which runs in the browser and is written in PHP.

Anyway, let’s go ahead and get started.

Installation

To install MySQL, run

$ sudo aptitude install mysql-server

This will get us a MySQL server and a command-line client for working with it. If you’d like a different client, such as phpMyAdmin, you’ll have to install it separately.

During the installation, MySQL will prompt you to enter a password. Remember it well: That’ll be the password for your MySQL root account.

Note that MySQL users and passwords have nothing whatsoever to do with your ubuntu users and passwords, despite being named similar things like root@localhost. I’ll touch on MySQL users and permissions later on.

MySQL Basics

Most of the time, you won’t be manipulating your databases by hand — in fact, it’s strongly discouraged. Usually, you’ll be working through PHP or Perl or some other API. However, I find going in and plugging commands by hand to be the most effective way to figure out how everything works. Here’s a whirlwind tour of the most common operations. Let’s get started!

Setup

The first thing you’ll need to do is to log in:

$ mysql -u root -p

It’ll prompt you for your password (the one you entered during installation), greet you with a standard message, and then you should see a prompt that looks like this:

mysql>

At this point, you can issue SQL commands to the database. A quick note: SQL keywords are case insensitive, but traditionally they’re written in all caps. A lot of programmers (myself included) find this really annoying and do everything in lowercase. It’s ultimately a matter of preference. In this tutorial, I went ahead and stuck to the uppercase convention because it distinguishes the keywords from the non-keywords. But if it annoys you, by all means switch.

By the way, it’s worth noting that you’re on the root account, which (similar to being root on your linux machine) means you could do quite a lot of damage. For now, just be careful about entering commands, and in a little bit, I’ll show you how to create a new user with limited privileges.

The first thing we’ll do is take a look at what databases are available.

mysql> SHOW DATABASES;
+-----------------------+
| Database              |
+-----------------------+
| information_schema    |
| mysql                 |
| phpmyadmin            |
| wordpress             |
+-----------------------+

If you’re on a fresh install, you’ll see information_schema and mysql. If your server is being used by other applications, like Wordpress, you’ll probably see a few more.

Obviously, we don’t want to mess up any existing databases, so let’s create our own to play with, named test:

mysql> CREATE DATABASE test;

If you do another SHOW DATABASES; you should see your new database on the list. Now we can create a new user with access to only our test database. I’ll call mine “test_user” and make the password simply “password”. You can, of course, use whatever values you like.

mysql> CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON test.* TO 'test_user'@'localhost';
mysql> FLUSH PRIVILEGES;

The first two statements creates the user and grants it all privileges on our test database; the FLUSH PRIVILEGES; simply tells MySQL to update the database permissions.

You now have a user with all privileges on our dummy database test, but none on any of the others. Go ahead and Ctrl-D your MySQL session and log in as your new user:

mysql> mysql -u test_user -p

Now, if you do a SHOW DATABASES;, the only databases you can see are test and information_schema.

To do anything with our new database, we need to tell MySQL to “enter” it:

mysql> USE test;

Now we’re ready to create some tables and enter some data.

Table Operations

To see a list of the tables in a database, you can say:

mysql> SHOW TABLES;

Obviously, we don’t have any yet. So let’s make one.

mysql> CREATE TABLE useful_info (
    -> first varchar(30),
    -> last varchar(30),
    -> age int,
    -> gender char(1),
    -> PRIMARY KEY (first, last)
    -> );

This creates a table called useful_info with four columns named first, last, age, and gender. Next to each is the datatype of the column. Finally, we use the columns first and last as the primary key. A primary key ensures that your rows are unique; when you specify more than one column as the primary key, the combination has to be unique. Let’s see it in action:

mysql> INSERT INTO useful_info (first, last, age, gender) VALUES ('John', 'Doe', 32, 'm');

This should produce a response like Query OK, 1 row affected (0.15 sec). Now, if I try to insert someone else named John Doe into the table, I’ll get an error:

mysql> INSERT INTO useful_info (first, last, age, gender) VALUES ('John', 'Doe', 21, 'm');
ERROR 1062 (23000): Duplicate entry 'John-Doe' for key 'PRIMARY'

As you can see, you have to know a lot about your table before you can do anything with it. If you need a quick refresher, you can get a summary of your columns by saying:

mysql> DESCRIBE useful_info;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| first  | varchar(30) | NO   | PRI |         |       |
| last   | varchar(30) | NO   | PRI |         |       |
| age    | int(11)     | YES  |     | NULL    |       |
| gender | char(1)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

Go ahead and populate your table. Play with it a little. :)

Once you have a decent roundup of people, you’ll eventually want to see your data. To get data from a table, we use the SELECT command. This will fetch the entire table:

mysql> SELECT * FROM useful_info;
+-------+-------+------+--------+
| first | last  | age  | gender |
+-------+-------+------+--------+
| John  | Doe   |   32 | m      |
| Jane  | Doe   |   28 | f      |
| Jenny | Lane  |   21 | f      |
| Aaron | Adams |   25 | m      |
+-------+-------+------+--------+

If you’d like to be more selective, you can restrict your search to certain columns …

mysql> SELECT first, last FROM useful_info;
+-------+-------+
| first | last  |
+-------+-------+
| Aaron | Adams |
| Jane  | Doe   |
| Jenny | Lane  |
| John  | Doe   |
+-------+-------+

… or certain rows …

mysql> SELECT * FROM useful_info WHERE first = 'John' AND last = 'Doe';
+-------+------+------+--------+
| first | last | age  | gender |
+-------+------+------+--------+
| John  | Doe  |   32 | m      |
+-------+------+------+--------+

… or both.

mysql> SELECT first, last FROM useful_info WHERE first = 'John' AND last = 'Doe';
+-------+------+
| first | last |
+-------+------+
| John  | Doe  |
+-------+------+

Lovely!

What if you want to change your table in any way? You could delete the whole table and recreate it, but that’s a bit of a hassle, seeing as you’d lose all of your data. Fortunately, there’s a command to alter the table. There’s a lot of things you can do with the alter keyword, but for simplicity, let’s just change the name of a column. Maybe you keep getting confused what first means and would like to make it clearer:

mysql> ALTER TABLE useful_info
    -> CHANGE COLUMN first first_name varchar(30);

Now if we describe our table, you’ll see that first has been changed to first_name:

mysql> DESCRIBE useful_info;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| first_name | varchar(30) | NO   | PRI |         |       |
| last       | varchar(30) | NO   | PRI |         |       |
| age        | int(11)     | YES  |     | NULL    |       |
| gender     | char(1)     | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

Deletion

Sometimes, you just need to get rid of something. Before you do though, realize that all of your data will be erased. Use these commands with caution: There is no going back!

To delete our table useful_info:

mysql> DROP TABLE useful_info;

To remove the whole test database:

mysql> DROP DATABASE test;

And to remove the user we created for our database (I had to be logged in as root to do this):

mysql> DROP USER 'test_user'@'localhost';

And you should be back to where you started.

MySQL Permissions

MySQL has a flexible and extensive users and permissions system. I’ll just touch on the basics here.

All the information for users and their permissions is kept in the mysql database. You’ll need to be root to access it. So after you’ve logged in, type

mysql> USE mysql;

Go ahead and check out what tables mysql keeps around:

mysql> SHOW TABLES;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+

The table we’re interested in is the user table, where (surprise!) the users are kept. Let’s take a look at what columns it has.

mysql> DESCRIBE user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field                 | Type                              | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host                  | char(60)                          | NO   | PRI |         |       |
| User                  | char(16)                          | NO   | PRI |         |       |
| Password              | char(41)                          | NO   |     |         |       |
| Select_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv          | enum('N','Y')                     | NO   |     | N       |       |
| File_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv            | enum('N','Y')                     | NO   |     | N       |       |
| References_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Event_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type              | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher            | blob                              | NO   |     | NULL    |       |
| x509_issuer           | blob                              | NO   |     | NULL    |       |
| x509_subject          | blob                              | NO   |     | NULL    |       |
| max_questions         | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates           | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections       | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections  | int(11) unsigned                  | NO   |     | 0       |       |
+-----------------------+-----------------------------------+------+-----+---------+-------+

The first thing of interest is the columns Host and User. Together, they specify an individual user (you’ll notice that they’re the primary key). Notice I emphasize the “together.” This is similar to users on different linux machines: darth@deathstar isn’t necessary the same guy as darth@thedarkside. If that fails to make sense, just think of them as first and last names: George Bush is a very different person than George Washington.

The other columns specify the different privileges that the user can have. Let’s use the select privilege as an example (the Select_priv column). The user table is the master table. Suppose you receive a Y for the select privilege. That means you can perform SELECT queries on every database on the server. However, suppose you get a N. That doesn’t mean you have no select privileges, just that you can’t do it anywhere you want. At that point, MySQL will inspect a succession of other tables to figure out where you have permission to do selects.

As an illustration, let’s take a look at the list of users and their select and insert privileges.

mysql> SELECT Host, User, Select_priv, Insert_priv FROM user;
+-----------+------------------+-------------+-------------+
| Host      | User             | Select_priv | Insert_priv |
+-----------+------------------+-------------+-------------+
| localhost | root             | Y           | Y           |
| rose      | root             | Y           | Y           |
| 127.0.0.1 | root             | Y           | Y           |
| localhost | debian-sys-maint | Y           | Y           |
| localhost | wordpress        | N           | N           |
| localhost | phpmyadmin       | N           | N           |
+-----------+------------------+-------------+-------------+

You should see several root users at hostnames that represent your computer (localhost, your machine name, your IP address). They should have Y under both Select_priv and Insert_priv. If you created a user for your test database above and you haven’t deleted it, you should also see it on the list. It should have an N in both privilege columns — that makes sense because it only has access to your test database.

Okay, that’s all I’m going to say about users and permissions. For more details, take a look at this article (where I learned all this to begin with).

Resources

Now that you’ve got a taste of MySQL, you’re probably itching to learn more. I’ve sprinkled links to helpful resources throughout the post, but for convenience, I’ll present them again here, along with some others.

First, learning the SQL language is undoubtedly helpful. Even though every implementation of SQL is slightly different, the majority of it will be the same. I went through the w3schools tutorial, but there are plenty of others as well.

There are also many tutorials on MySQL in particular. I’ve not really used any — most of what I know, I’ve learned by searching for specific tasks — but sometimes, it’s nice to sit down and fill in all the gaps.

Last but certainly not least, there’s the MySQL documentation. It’s definitely not for the faint of heart! But it’s also the most thorough and definitive source of information you’ll get.

Finally, here are articles on specific topics that I found to be very helpful. I highly recommend starting with this list of common commands; it’s a great place to get an overview before researching specific syntaxes.

Cheers! I hope this has been helpful!