Docker is quickly becoming mainstream, as a method to package and deploy self-sufficient applications in primarily stateless Linux containers. But for a stateful service like a database, this might be bit of a headache. How do we best configure MySQL in a container environment? What can go wrong? Should we even run our databases in a container environment? How does performance compare with e.g. running on virtual machines or bare-metal servers? How do we manage replicated or clustered setups, where multiple containers need to be created, upgraded and made highly available?
So, welcome to our new blog series - “MySQL on Docker”. We will touch upon swarms, shared volumes, data-only-containers, security and configuration management, multi-host networking, service discovery and implications on monitoring when we move from host-centric to role-centric services with shorter life cycles.
In our first blog post, we are going to cover some basics around running MySQL in a container. We are going to use the term ‘Docker’ as the container platform throughout the blog series.
MySQL Docker Containers
Think about a container as a “lightweight virtual machine”. Unlike virtual machines though, containers do not require an entire operating system, all required libraries and the actual application binaries. The same Linux kernel and libraries can be shared between multiple containers running on the host. Docker makes it easy to package Linux software in self-contained images, where all software dependencies are bundled and deployed in a repeatable manner. An image will have exactly the same software installed, whether we run it on a laptop or on a server. The key benefit of Docker is that it allows users to package an application with all of its dependencies into a standardized unit (container). Running many containers allows each one to focus on a specific task; multiple containers then work in concert to implement a distributed system.
The traditional way to run a MySQL database is to install the MySQL packages on a host (bare-metal, virtual machine, cloud instance), and applications would just have to connect to the listening port. Most of the management tasks, for example, configuration tuning, backup, restore, database upgrade, performance tweaking, troubleshooting and so on have to be executed on the database host itself. You would expect to have several ports accessible for connection, for example port TCP 22 for SSH, TCP 3306 for MySQL or UDP 514 for syslog.
In a container, think of MySQL as one single unit that only serve MySQL related stuff on port 3306. Most of the operation should be performed under this single channel. Docker works great in packaging your application/software into one single unit, which you can then deploy anywhere as long as Docker engine is installed. It expects the package, or image to be run as a single process per container. With Docker, the flow would be you (or someone) build a MySQL image using a specific version and vendor, package the image and distribute to anybody who wants to quickly fire a MySQL instance.
Let’s get it running
Let’s familiarize ourselves with a MySQL container running on Docker. We’ll take a ‘break/fix’ approach, so expect to see some errors pop up here and there. We’ll look at the errors and see why they happen. We are going to use the official MySQL image created and maintained by Docker.
To begin with, we must have a host. It can be any type of hosts (physical or virtual) running on Linux, Mac OS X or Windows. Please refer to Docker’s installation guide for details. You can also use docker-machine to provision hosts on a supported cloud provider like DigitalOcean and AWS EC2 Container Service, but we will cover that in another blog post. Here, we are going to use Ubuntu 14.04 as our machine host and use standard command line for deployment and management.
Next, find a container image that you want from the Docker registry. It can be a public registry like Docker Hub or a private registry, where you host the containers’ image on-premises, within your own network. If you can’t find the image that fits you, you can build your own.
There are many MySQL container images available in the Docker Hub registry. The following screenshot shows some examples:
Firing up a MySQL container
First, you have to install Docker. In the Linux box:
Then, use the following basic command to run a MySQL container:
Yeap, that’s it. Just two steps. Here is what the second command line does:
run - Run a command in a new container.
--name - Give a name to the container. If you don’t specify this, Docker will generate a random name.
mysql - The image name as stated on the Docker Hub page. This is the simplest image name. The standard is “username/image_name:tag”, for example “severalnines/mysql:5.6”. In this case, we specified “mysql”, which means it has no username (the image is built and maintained by Docker, therefore no username), the image name is “mysql” and the tag is latest (default). If the image does not exist, it will pull it first from Docker Hub into the host, and then run the container.
You should then see the following lines:
It looks like the container deployment failed. Let’s verify with the following command if there is any running container:
There is no running container. Let’s show all containers (including the non-running ones):
Under the ‘STATUS’ column, you can see the status was “Exited (1) 6 minutes ago”. If a program ended while returning a non-zero value, it means that the program was terminated with some kind of error. So, what happened? The MySQL image was successfully downloaded but Docker failed to run it as container because the environment is not properly set up. This is stated in the error lines.
Let’s try to fix this by specifying one of the environment variables:
Oops, another error occurred. We were trying to run a new container with the same name as an existing container. Let’s remove the created container and run the command again:
You should see lots of lines appear. That’s MySQL initialization when starting up as newly installed software. You should see MySQL is ready to accept connections:
Looks good. Our MySQL container is now running. However, you are now stuck in the terminal and can’t do anything because the container is running in attach mode (running in foreground). This is so inconvenient. We would expect MySQL to run as a service instead. Let’s consider this as a failed deployment and stop the current container. In another terminal, stop the running container and run it again in detach mode (running as background):
You will get an output of the container ID, indicating the container is successfully running in the background. Let’s verify the status of the container:
MySQL container is now running and accessible on port 3306 of that container. Since it was running in the background, we could not see what was happening during the MySQL startup. Use the following command to see what happened during the container startup:
Connecting to the Container
Next, we retrieve the IP address of that container in order to access it. Run the inspect command:
We can see lots of low-level information of that container. Lookup the “IPAddress” line:
From the physical host, we can now access the MySQL server. Ensure the MySQL client package is installed beforehand:
Voila! We now have a MySQL instance running in a container. However, this port is only accessible within the Docker network. If you have another Docker container for your application, you can connect with them directly via IP address 172.17.0.20 on port 3306, as illustrated in the following diagram:
Docker allocates a dynamic IP address on every running container. Whenever a container is restarted, you will get a new IP address. You can get the IP address range from the Docker network interface in the Linux box. Run the following command to see Docker’s network range:
Our container’s IP address is 172.17.0.20 which is in the range of 172.17.42.1/16. Let’s restart the container, and you should get a new IP address being assigned by Docker:
Our IP address just changed to 172.17.0.21. If you had an application that connects to this container via the old IP address, the application would not get connected anymore. Docker introduces another way to link your container with another container, to ensure whatever IP address assigned to it will get updated in the linked container. Let’s say we deploy a Wordpress application (which has no MySQL installed on that image), and want to link with our existing MySQL container, test-mysql. Here is what you should do:
In a couple of minutes, the container “test-wordpress” will be up and running and linked to our test-mysql container:
To verify if it’s linked correctly, enter the test-wordpress container and look at the content of /etc/hosts:
The application can now see an entry with IP address and hostname related to the linked MySQL container. If you restart the MySQL container and get another IP address, the entry will be updated by Docker accordingly.
You can also expose the MySQL container to the outside world by mapping the container’s MySQL port to the host machine port using the publish flag (as illustrated in the above diagram). Let’s re-initiate our container and run it again with an exposed port:
Verify if the container is correctly mapped:
At this point, we can now access the MySQL container directly from the machine’s port 6603.
Configuration management
The container comes with a standard MySQL 5.7 configuration options inside /etc/mysql/my.cnf. Let’s say our application that connects to this MySQL server requires more max_connections (default is 151) during startup, so we need to update the MySQL configuration file. The best way to do this in a container is to create alternative configuration files in a directory on the host machine and then mount that directory location as /etc/mysql/conf.d inside the mysql container.
On the host machine, create a directory and a custom MySQL configuration file:
And add the following lines:
Then, we have to re-initiate the MySQL container (remove and run) by mapping the volume path as shown in the following command (the long command is trimmed to make it more readable):
This will start a new container test-mysql where the MySQL instance uses the combined startup settings from the default /etc/mysql/my.cnf and /etc/mysql/conf.d/my-custom.cnf, with settings from the latter taking precedence.
Verify the new setting is loaded from the machine host:
Data Storage
There are several ways to store data used by MySQL that run in Docker containers. Docker can manage the storage of your database’s data by writing the database files to disk on the host system, using its own internal volume management. If you run the inspect command, look at the “Volumes” directive and you should notice by default MySQL data directory (/var/lib/mysql) is mounted into Docker’s internal volume:
This is the easiest way and fairly transparent to the user. The downside is that the files may be hard to locate for tools and applications that run directly on the host system, i.e. outside containers.
The other way is to create a data directory on the host system (outside the container) and mount this to a directory visible from inside the container. This places the database files in a known location on the host system, and makes it easy for tools and applications on the host system to access the files. The downside is that the user needs to make sure that the directory exists, and that e.g. directory permissions and other security mechanisms on the host system are correctly set up.
Create a data directory on a suitable volume on your host system, e.g. /storage/docker/mysql-datadir:
Start your mysql container like this:
The ”--volume=/storage/docker/mysql-datadir:/var/lib/mysql“ part of the command mounts the /storage/docker/mysql-datadir directory from the underlying host system as /var/lib/mysql inside the container, where MySQL by default will write its data files, as illustrated in the following diagram:
When inspecting the container, you should see the following lines:
Which is now clearer for you to see the directory and files on the machine host created by this container:
Note that restarting or removing the container does not remove the MySQL data directory. When you restart a MySQL container by using “stop” and “start” command, it would be similar to restarting the MySQL service in a standard installation:
If you remove the MySQL container, the data in the mounted volumes will still be intact and you can run a new instance, mounting the same volume as data directory:
*If a MySQL container runs on top of an existing MySQL datadir, the $MYSQL_ROOT_PASSWORD variable should be omitted from the “run” command line; it will in any case be ignored, and the pre-existing database will not be changed in any way.
However, only one running (active) container is allowed to access the MySQL data directory at a time. Running another container mounting the same datadir volume will cause MySQL startup error on the later container:
This is expected since a MySQL process must exclusively own the MySQL data directory to avoid any potential conflicts. Having more than one mysqld process connecting to the same data directory is just not feasible. That’s why MySQL horizontal scaling can only be achieved via replication.
That concludes this blog. Do share your experience in managing MySQL containers in the comments section below.
Tags:
docker
containers
MySQL
Database
introduction
PlanetMySQL Voting: Vote UP / Vote DOWN