Docker MySQL Persistence (Tech Tip #83)

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:

And inspect as:

Then it shows the anonymous volumes:

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:

And then view the directory listing as:

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:

Login to the container:

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

Stop the container:

Restart the container:

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:

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:

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

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

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:

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:

/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:

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:

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:

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:

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 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:

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

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

Docker Data Containers

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.

You can also look at docker-volumes to manage volumes on your host.

You can also read more about volumes may evolve in future at #6496.

Enjoy!

Be Sociable, Share!

5 thoughts on “Docker MySQL Persistence (Tech Tip #83)

  1. Not sure if this is an issue with Kitematic specifically or Docker in general, but I did some digging and there are issues trying to create a volume to handle DB data dirs with a number of different DB products, including Mongo, Postgres, redis, etc – the owner of the directory has to match the user that starts the database service, which the volume doesn’t appear to match.

  2. So are you saying that currently, it is impossible to share data only containers between hosts? I am looking to share a data only container as an image to a friend. Can I or can’t I do that?

  3. Awesome article, thank you.
    Can you tell me how to do a data-container and a mysql container exactly like yours but using docker-compose.yml?
    Cheers
    Pierre

Leave a Reply

Your email address will not be published. Required fields are marked *