Docker MySQL Persistence

Arun GuptaApril 16th, 2015Last Updated: April 15th, 2015

0 44 7 minutes read

One of the recipes in 9 Docker recipes for Java developers is using MySQL container with WildFly. Docker containers are ephemeral, and so any state stored in them is gone after they are terminated and removed. So even though MySQL container can be used as explained in the recipe, DDL/DML commands can be used to persist data, but that state is lost, or at least not accessible, after the container is terminated and removed.

This blog shows different approaches of Docker MySQL Persistence – across container restarts and accessible from multiple containers.

Default Data Location of MySQL Docker Container

Lets see the default location where MySQL Docker container stores the data.

Start a MySQL container as:

docker run --name mysqldb -e MYSQL_USER=mysql -e MYSQL_PASSWORD=mysql -e MYSQL_DATABASE=sample -e MYSQL_ROOT_PASSWORD=supersecret -d mysql

And inspect as:

docker inspect -f {{.Volumes}} <CONTAINER_ID>

Then it shows the anonymous volumes:

map[/var/lib/mysql:/mnt/sda1/var/lib/docker/vfs/dir/78f079dae09bf052cf900951b6d71a611fcf7c72f3460a2013e6f4d941a2b256]

If you are using Boot2Docker, then /mnt/sda1 directory is used for storing images, containers, and data. This directory is from the Boot2Docker virtual machine filesystem. This is clarified in Docker docs as well and worth repeating here:

Note: If you are using Boot2Docker, your Docker daemon only has limited access to your OSX/Windows filesystem. Boot2Docker tries to auto-share your /Users (OSX) or C:\Users (Windows) directory – and so you can mount files or directories using docker run -v /Users/<path>:/<container path> ... (OSX) or docker run -v /c/Users/<path>:/<container path ... (Windows). All other paths come from the Boot2Docker virtual machine’s filesystem.

You can view this mounted directory on Boot2Docker by logging into the VM as:

boot2docker ssh

And then view the directory listing as:

docker@boot2docker:~$ ls -la /mnt/sda1/var/lib/docker/
total 72
drwxr-xr-x   11 root     root          4096 Apr  9 19:45 ./
drwxr-xr-x    4 root     root          4096 Mar 27 13:58 ../
drwxr-xr-x    5 root     root          4096 Mar 27 13:59 aufs/
drwx------   10 root     root          4096 Apr  9 19:45 containers/
drwx------    3 root     root          4096 Mar 27 13:59 execdriver/
drwx------  128 root     root         20480 Apr  9 19:45 graph/
drwx------    2 root     root          4096 Apr  7 23:34 init/
-rw-r--r--    1 root     root          7168 Apr  9 19:45 linkgraph.db
-rw-------    1 root     root          2229 Apr  9 19:45 repositories-aufs
drwx------    2 root     root          4096 Apr  9 19:14 tmp/
drwx------    2 root     root          4096 Mar 27 17:56 trust/
drwx------    3 root     root          4096 Apr  9 19:45 vfs/
drwx------    4 root     root          4096 Apr  9 19:45 volumes/

MySQL Data Across Container Restart – Anonymous Volumes

Anonymous volumes, i.e. volumes created by a container and which are not explicitly mounted, are container specific. They stay around unless explicitly deleted using docker remove -v command. This means a new anonymous volume is mounted for a new container even though the previous volume may not be deleted. The volume still lives on the Docker host even after the container is terminated and removed. Anonymous volume created by one MySQL container is not accessible to another MySQL container. This means data cannot be shared between different data containers.

Lets understand this using code.

Start a MySQL container as:

docker run --name mysqldb -e MYSQL_USER=mysql -e MYSQL_PASSWORD=mysql -e MYSQL_DATABASE=sample -e MYSQL_ROOT_PASSWORD=supersecret -it -p 3306:3306 mysql

docker exec -it <CONTAINER_ID> bash

Connect to the MySQL instance, and create a table, as:

root@04c2f54b7fe7:/# mysql --user=mysql --password=mysql
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.24 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| sample             |
+--------------------+
2 rows in set (0.00 sec)

mysql> connect sample;
Connection id:    3
Current database: sample

mysql> show tables;
Empty set (0.00 sec)

mysql> create table user(name varchar(50));
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+------------------+
| Tables_in_sample |
+------------------+
| user             |
+------------------+
1 row in set (0.00 sec)

Stop the container:

docker stop <CONTAINER_ID>

Restart the container:

docker start <CONTAINER_ID>

Now when you connect to the MySQL container, the database table is shown correctly. This shows that anonymous volumes can persist state across container restarts.

Inspect the container:

~> docker inspect -f {{.Volumes}} ea7b1eff9714
map[/var/lib/mysql:/mnt/sda1/var/lib/docker/vfs/dir/78f079dae09bf052cf900951b6d71a611fcf7c72f3460a2013e6f4d941a2b256]

And it correctly shows the same anonymous volume from /mnt/sda1 directory.

Now lets delete the container, and start a new MySQL container. First remove the container:

docker rm -f <CONTAINER_ID>

And start a new container using the same command as earlier:

docker run --name mysqldb -e MYSQL_USER=mysql -e MYSQL_PASSWORD=mysql -e MYSQL_DATABASE=sample -e MYSQL_ROOT_PASSWORD=supersecret -it -p 3306:3306 mysql

Now when you try to see the list of tables, its shown as empty:

mysql> connect sample;
Connection id:    3
Current database: sample

mysql> show tables;
Empty set (0.00 sec)

This is because anonymous volumes are visible across container restarts, but not visible to different containers. A new volume is mounted for a new run of the container. This is also verified by inspecting the container again:

~> docker inspect -f {{.Volumes}} bde73c930275
map[/var/lib/mysql:/mnt/sda1/var/lib/docker/vfs/dir/4d0ab6d1412bfbe79541b2d87d632cf12e70044201665f859a6a678132fb323f]

A different directory is used to mount the anonymous volume.

So effectively, any data stored in the MySQL database by one container is not available to another MySQL container.

Docker Volume to Store MySQL Data

One option to share data between different MySQL containers is to mount directories on your Docker host as volume in the containers using -v switch when running the Docker image. If you are using Boot2Docker, then there are two options:

Mount a directory from the Boot2Docker VM filesystem. This directory, if does not exist already, would need to be created.
Mount a directory from your Mac host. For convenience, this need to exist in /Users/arungupta or whatever your corresponding directory is.

The first approach ties to the specific Boot2Docker VM image, and the second approach ties to a specific Mac host. We’ll look at how this can be fixed later.

We’ll discuss the first approach only here. Start the MySQL container as:

docker run --name mysqldb -v /mnt/sda1/var/mysql_data:/var/lib/mysql -e MYSQL_USER=mysql -e MYSQL_PASSWORD=mysql -e MYSQL_DATABASE=sample -e MYSQL_ROOT_PASSWORD=supersecret -it -p 3306:3306 mysql

/var/lib/mysql is the default directory where MySQL container writes its files. This directory is not persisted after a Boot2Docker reboot. So the recommended option is to create a directory in /mnt/sda1 and map that instead. Make sure to create the directory /mnt/sda1/var/mysql_data, as is the case above.

Now inspecting the container as:

~> docker inspect -f {{.Volumes}} cd7deacc9d18
map[/var/lib/mysql:/mnt/sda1/var/mysql_data]

Now any additional runs of the container can mount the same volume and will have access to the data.

Remember, multiple MySQL containers cannot access this shared mount together and instead will give the error:

2015-04-10 02:35:58 1 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
2015-04-10 02:35:58 1 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2015-04-10 02:35:58 1 [Note] InnoDB: Retrying to lock the first data file

So you need to make sure to stop an existing MySQL container, start a new MySQL container using the same volume, and the data would still be accessible.

This might be configured using master/slave configuration, where the master and slave have access to same volume. It’ll be great if somebody who has tried that configuration can share that recipe.

But as mentioned before, this approach is host-centric. It restricts MySQL to a particular Boot2Docker VM image. That means, you once again loose the big benefit of portability as offered by Docker.

Meet Docker data-only containers!

Docker Data-only Containers

Docker follows Single Responsibility Principle (SRP) really well. Docker Data-only containers are NoOp containers that perform a command that is not really relevant, and instead mount volumes that are used for storing data. These containers don’t even need to start or run, and so the command really is irrelevant, just creating them is enough.

Create the container as:

docker create --name mysql_data -v /var/lib/mysql mysql

If you plan to use a MySQL container later, its recommended to use the mysql image to save bandwidth and space from downloading another random image. You can adjust this command for whatever database container you are using.

If you intend to use MySQL, then this data-only container can be created as:

docker create --name mysql_data arungupta/mysql-data-container

Dockerfile for this container is pretty simple and can be adopted for a database server of your choice.

Since this container is not running, it will not be visible with just docker ps. Instead you’ll need to use docker ps -a to view the container:

~> docker ps -a | grep mysql
ec48ddda196e        mysql:5                      "/entrypoint.sh mysq   2 days ago                                                        mysql_data

Docker allows to mount, or pull in, volumes from other containers using --volumes-from switch specified when running the container.

Lets start our MySQL container to use this data-only container as:

docker run --name mysqldb --volumes-from mysql_data -v /var/lib/mysql:/var/lib/mysql -e MYSQL_USER=mysql -e MYSQL_PASSWORD=mysql -e MYSQL_DATABASE=sample -e MYSQL_ROOT_PASSWORD=supersecret -it -p 3306:3306 mysql

Boot2Docker VM has /var/lib/mysql directory now populated:

total 110596
drwxrwxrwx    5 999      999            180 Apr 10 05:05 ./
drwxrwxr-x    4 root     staff          120 Apr  9 21:11 ../
-rw-rw----    1 999      999             56 Apr 10 00:57 auto.cnf
-rw-rw----    1 999      999       50331648 Apr 10 05:05 ib_logfile0
-rw-rw----    1 999      999       50331648 Apr 10 00:57 ib_logfile1
-rw-rw----    1 999      999       12582912 Apr 10 05:05 ibdata1
drwx------    2 999      999           1620 Apr 10 00:57 mysql/
drwx------    2 999      999           1100 Apr 10 00:57 performance_schema/
drwx------    2 999      999            100 Apr 10 01:18 sample/

If you stop this container, and run another container then the data will be accessible there.

In a simple scenario, application server, database, and data-only container can all live on the same host. Alternatively, application server can live on a separate host and database server and data-only container can stay on the same host.

Hopefully this would be more extensive when Docker volumes can work across multiple hosts.

It would be nice if all of this, i.e. creating the data-only container and starting the MySQL container that uses the volume from data-only container can be easily done using Docker Compose. #1284 should fix this.

Usual mysqldump and mysql commands can be used to backup and restore from the volume. This can be achieved by connecting to the MySQL using CLI as explained here.