MySQL-Docker operations. - Part 1: Getting started with MySQL in Docker

Docker is one of the fastest growing trends in IT. It allows fast deployment of services and applications on a Linux machine (and, with some limits, on other operating systems). Compared to other methods of deploying databases, such as virtual machines or application isolation, it offers faster operations and better performance.
Many people, surprised by the sudden advance of this technology, keep asking What is Docker? And why you should use it?
I will write soon an article with a deep comparison of the three methods (VM, container, sandbox), but for now, we should be satisfied with a few basic facts:

• Docker is a Linux container. It deploys every application as a series of binary layers, containing just the minimum dependencies (libraries and applications) to make the service work;
• It stores images in a central registry, from where the docker client can download them quickly;
• By its definition, it is lightweight. If you have the images already in your system, deployment of the service happens in seconds.
• Unlike virtual machines, where you can deploy virtualized Windows and other non-Linux environment, Docker is Linux-only. You can virtualize every service, provided that it runs on Linux.
• Docker can run applications in various flavors of Linux at once. It actually makes the Linux flavor dependency transparent, to the point that the users barely realize that.

Installing Docker

Docker installation is pretty much straightforward. The Docker documentation covers the basics and the fine points of installing in any operating system. Rather than repeating the procedure here, I recommend looking the pages for Ubuntu, Mac OS X, or Windows.
Once the installation is complete, the commands shown in this article will apply to all platforms. When there are exceptions, it will be noted in the text.

Using Docker

The way you operate on docker is simple:

1. Search for the image containing the application or service you want (docker search image_name);
2. Get the image into your machine (docker pull image_name);
3. Run the image in a container (docker run --name mycontainer image_name);
4. Dispose of the container (docker stop mycontainer ; docker rm mycontainer).

There are ways of connecting images together, but we won’t cover that in this article. For now, we’ll concentrate on the task of deploying a MySQL image in a Docker container.

Caution with “official” images

One last digression before we begin. There are TWO official MySQL images. One is mysql, which is marked as “official”, but it is not packaged by the MySQL team. This image was prepared by the Docker team, and (at the time of this article) does not conform with the recommendations from the MySQL manual. Using it you may get slightly different behavior from what you expect.
Then there is the image named mysql/mysql-server, which is maintained by the MySQL team at Oracle, and is installed following the recommendations from the manual. This fact matters a lot for MySQL 5.7, where the installation has changed from using mysql_install_db to mysqld --initialize.

MySQL on Docker for the impatient

If you can’t wait to put your hands on a MySQL image, here’s the quick way.

$ docker pull mysql/mysql-server

This command downloads the “:latest” image of mysql-server from the Docker hub. The latest images was recently updated to match MySQL 5.7.9 GA. This is a one-off operation. Until there is a more updated version available, you won’t need to run it.
If the latest image mysql/mysql-server is not in your computer, it will be downloaded from the Docker hub. It’s about 300 MB, and depending on your connection speed, it will take from 30 seconds to 30 minutes to complete.

$ docker run --name boxedmysql \  
    -e MYSQL_ROOT_PASSWORD=secret \  
    -d mysql/mysql-server

This command will invoke the docker engine, asking to use the image mysql/mysql-server, using root password secret. It creates a container named boxedmysql. The “-d” in the command is very important, as it tells Docker to run the software as a daemon. Without it, the operation would terminate immediately after starting.
How do you use it? There are two ways:
The first method is using it as any other MySQL server. You will need a client in your computer (not inside the container), connect it to the port 3306 of the container, and send SQL commands. For this method, you need to use the IP of the container, which you can get from Docker itself. For example:

docker inspect --format '{{ .NetworkSettings.IPAddress}}' boxedmysql  
172.10.11.104  
$ mysql -h 172.10.11.104 -u root -p  

And you are in! Simple, and relatively painless. It is a real server, well isolated in its container, running the latest version of MySQL and answering your queries.
The second method will access the MySQL command line tool from the container itself. In fact, you may not even have a MySQL client in your computer, and this is the reason you were installing MySQL in the first place. For this method, we won’t need to know the container IP, since we will be accessing the tool from within the container itself:

$ docker exec -it boxedmysql mysql -u root -p
password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.9 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

(Note: the “exec” command requires docker client version 1.3 or newer.)
This command invokes the tool mysql from within the container boxedmysql. Depending on what you want to do, this may be better than using the mysql client in your computer, since yours could be of a version different from the server’s, and would prevent you from using some advanced features, such as authentication plugins.
If we can invoke mysql, we can also run a shell from the container. A docker container is in reality a minimalist Linux operating system, and you could do this:

$ docker exec -it boxedmysql bash  
af423ebea2 # mysql -u root -p

When you call bash via docker exec, you get immediately another shell prompt, although it is different from what you were using thus far. That’s because you are now inside the container, and you can issue regular commands.
Cool. However, in our impatience, we have got a server that is not easily manageable. We have the default settings, and our data is inside the container, and we have no idea of how to reach it in case of trouble. And if you delete the container, your data is gone. Clearly, this is a cool example, but we need to set things up better.
BTW, there is something here that does not stick out as it should. After we deploy the container (docker run ...) we get a prompt back immediately. But the server, inside the container, has not been initialized yet. In this example, just the time needed to figure out what was happening will give the server enough time to start, and by the time we run docker exec -it boxedmysql mysql, the server will be up and running. However, in the real world, things are not that smooth, especially when we deploy many servers at once. We'll see the handling of this issue in the next article. For now, it's left as a practicing exercise.

Some useful Docker commands

Before we go further, we need to get familiar with just a few Docker commands to allow us to run operations effectively.
With an active container you can do several things:

• get information with docker inspect boxedmysql. This command returns a JSON file containing all the details about the container.
• Stop the container. We started MySQL as a daemon, and if we want to stop it we can use docker stop boxedmysql;
• Remove the container. When we don’t want the container anymore, either because it is no longer needed or because it was made useless by some clever experimental app, we can delete is with docker rm boxedmysql.

We can also run commands that act outside a single container:

• docker pull image_name will download a given image from a hub, without running it.
• docker ps will show which containers are running.
• docker images shows the pool of available images in the current system
• docker rmi image_name will remove an image from the pool.

There are many others, but this is what we will need to continue our exercise.

Customizing MySQL: the naive attempt

With the goal of customizing the installation, we observe that, if we could enter the container to run the mysql client, we could also modify the configuration file and restart the server. Let’s try

$ docker exec -it boxedmysql bash  
af423ebea2 # vim /etc/my.cnf  
bash: vim: command not found

Oh! There is no vim editor. Perhaps nano? Nope. Or maybe pico? No way. The container is stripped to the bone [1], and does not include anything that is not needed to fulfil its main purpose. A clever reader would, at this point, think of fixing the problem by running apt-get install vim or yum install vim, depending on the nature of the container. This would certainly give you an editor, but you’d be solving the wrong problem. You may edit the configuration file, and get the optimal setup, but your changes would not outlive the container. If you remove and re-create the container, or if you install a second and a third one, you’ll be back to square one: installing an editor and changing the configuration manually is not a scalable model.

The Docker way: using volumes to customize MySQL

With containers that can be deployed and removed instantly, you need an equally quick way of interacting with them. Docker allows you to define volumes when you run an image. A volume is a file or a directory in your computer that is mapped to a resource inside the container.
Let’s remove the container, and start a new one

$ docker stop boxedmysql        # We can't remove a running container.  
                                # We need to stop it first  
$ docker rm boxedmysql

To use our first volume, we prepare a configuration file named local_my.cnf in the current directory:

[mysqld]  
user        = mysql  
server-id   = 12345

Then, we run the mysql/mysql-server image again, but with an additional twist

$ docker run --name boxedmysql \  
    -e MYSQL_ROOT_PASSWORD=secret \  
    -v $PWD/local_my.cnf:/etc/my.cnf \  
    -d mysql/mysql-server

This is the same command as before, with the additional volume (-v) of the file local_my.cnf which is mapped to the file /etc/my.cnf inside the container. The net result is that the MySQL server will start using the options defined in this file. We can prove it quite easily:

$ docker exec -it boxedmysql mysql -u root -psecret -e 'select @@server_id'  
+-------------+  
| @@server_id |  
+-------------+  
|       12345 |  
+-------------+

You can see that the server is now using the configuration file that we have defined locally.
Now that we know the method, we can extend it to make our data more durable. We want a data directory that outlives the MySQL container.
To achieve this goal, we create a directory and change its ownership to user mysql and group mysql.

$ mkdir -p /opt/docker/boxedmysql  
$ chown -R mysql.mysql /opt/docker/boxedmysql

Next, we run the image again, but with yet another volume.

$ docker stop boxedmysql  
$ docker rm boxedmysql

$ docker run --name boxedmysql \  
    -e MYSQL_ROOT_PASSWORD=secret \  
    -v $PWD/local_my.cnf:/etc/my.cnf \  
    -v /opt/docker/boxedmysql:/var/lib/mysql \  
    -d mysql/mysql-server

The image is now using the “volume” /opt/docker/boxedmysql for its data directory. Internally, it is using /var/lib/mysql, but the data really goes in the little nest that we have prepared for the database server to lay its eggs. The directory was empty a few seconds ago. But now it isn’t:

$ ls -l /opt/docker/boxedmysql  
total 245816  
-rw-r-----+   1 mysql  mysql        56 Oct 21 05:33 auto.cnf  
-rw-r-----+   1 mysql  mysql       407 Oct 21 05:33 ib_buffer_pool  
-rw-r-----+   1 mysql  mysql  50331648 Oct 21 05:33 ib_logfile0  
-rw-r-----+   1 mysql  mysql  50331648 Oct 21 05:33 ib_logfile1  
-rw-r-----+   1 mysql  mysql  12582912 Oct 21 05:33 ibdata1  
-rw-r-----+   1 mysql  mysql  12582912 Oct 21 05:33 ibtmp1  
-rw-r-----+   1 mysql  mysql      3964 Oct 21 05:33 my-master-error.log  
drwxr-x---+  77 mysql  mysql      2618 Oct 21 05:33 mysql  
-rw-r-----+   1 mysql  mysql       154 Oct 21 05:33 mysql-bin.000001  
-rw-r-----+   1 mysql  mysql        19 Oct 21 05:33 mysql-bin.index  
-rw-r-----+   1 mysql  mysql         6 Oct 21 05:33 mysql.pid  
-rw-rw----+   1 mysql  mysql         6 Oct 21 05:33 mysqld_safe.pid  
drwxr-x---+  90 mysql  mysql      3060 Oct 21 05:33 performance_schema  
drwxr-x---+ 108 mysql  mysql      3672 Oct 21 05:33 sys  
drwxr-x---+   3 mysql  mysql       102 Oct 21 05:33 test

CAUTION: This method only works on a Linux host. If you are using a Mac or Windows machine, the container will not be able to manipulate and use the volume, because on these operating systems docker is running inside a virtual machine.

What’s next

In this first installment, we have seen the absolute basics of MySQL on Docker. We learned how to deploy a container, how to use it, and how to perform some simple customization.
In Part 2, we’ll see more customization options. We will see how to deploy MySQL with GTID and other other enhancements that may fail when you try them using the methods explained here.
Moreover, we want to perform backups on dedicated space, share backups between container, have a different prompt for each container, and a few more subtleties.
In Part 3 we’ll deploy several containers in replication. (Spoiler: you can already do that using the scripts in mysql-replication-samples, but we will go through the details, and understand why every step is necessary and what are the best methods.
In Part 4 we will examine differences in performance and convenience between virtual machines, containers, and sandboxes.
In Part 5 we will cover enhanced operations of customized containers using orchestration tools.

---

1. This really depends on how the image was packaged. In some MySQL images you will find an editor, in some others, no.  ↩