Backing up your MySQL instance physically with Docker

In a previous post I had mentioned that I was doing a bit of digging into Docker in order to get a better grasp of the technology. Part of that was exploring common administrative tasks. I would venture to say that backups are probably among the most important tasks we take on with database administration, so it’s important to know how to do this for Docker MySQL instances.

There is a fair bit of documentation on how to handle this logically (mysqldump / mydumper) as this is a simple task to perform as long as you can connect to the database instance, so I wanted to approach physical backups using the very common xtrabackup tool. Additionally, we’re trying to think with containers here, so I wanted to make sure that not only would I be taking a backup of the Docker container MySQL instance, but I would do it with another Docker container running xtrabackup. This can be extra handy for you if you’re a Windows user, as Docker is the only way to get xtrabackup running in Windows currently. So let’s get started and see how this works.

First, let’s have a look at the instance that we want to back up. You’ll see I have a container instance running 5.7.22 that contains a small set of sample data and has an exposed and mapped port. It should also be noted that the container is leveraging Docker internal networking via the Docker interface and did not have a volume mount specified for the data directory when it was created with Docker run, thus forcing Docker to create one for us in a location of its choosing. In short, you would get a simple container like this if you were to run ‘docker run -p 3307:3306 –name=mysql1 -e MYSQL_ROOT_PASSWORD=password -d mysql/mysql-server:5.7.22’.

In order to do the backup, we need to find the local directory on the host where the data exists as well as the internal IP that the container is using so the xtrabackup container can reach it. We can do this by using Docker inspect. As you’ll see below, the local source directory is /var/lib/docker/volumes/f8ade307ebd48638b5364bab4c3e352799c6b0404bfbbd25dbc12d663c174a3a/_data and the IP address is 172.17.0.2.

In order to back this up, we will need to launch a container based on the perconalab/percona-xtrabackup image, which is in a public repo on Docker hub. We’ll need to make sure the data directory noted above is available to the container by volume mounting it as /var/lib/mysql, as well as volume mounting a location where the backup files can be created mounted as /xtrabackup_backupfiles. I’ve created a directory on the Docker host called /backup for this purpose.

When the xtrabackup container starts, it uses xtrabackup as the entrypoint command. This basically means that when the container starts, it runs xtrabackup with whatever arguments you pass at the end of the Docker run statement. Using this entry point and its arguments, we can specify the network location of the container we want to back up, as well as provide the necessary MySQL user credentials.

Keep in mind that we are leveraging the –rm argument, so once the xtrabackup container had launched and finished its task, it’s going to remove itself.

So now we have a new fresh backup in the /backup directory on the Docker host. Now we need to prepare the backup for restore. We know from our last step that xtrabackup is the entrypoint command, so all we need to do now is launch the container again, making sure that the backup data files are available to it and passing along the correct arguments to prepare the files. We’ll do this by mounting the backup volume in much the same way as we did to create it and by leveraging the prepare argument.

Now we have a backup set that’s fully prepared and ready to be restored. All we need now is a place to restore it. For this, I am going to launch a second container running 5.7.22. I’m going to let Docker create the volume for the data directory as I did before. So for restore purposes, we’re going to need to use Docker inspect on the new container to find out where that volume was created on the Docker host.

Now we need to restore our backup. Typically this would be done in a normal mySQL deployment by stopping mysql, removing the contents of the data directory, copying in the backup set, chowning it to MySQL, and then starting mysql again. In this case, we’re going to be doing something very similar, only instead of stopping mysql, we’re going to stop the container, which in turn does a safe shutdown of mysql. We’re also going to check the data source directory on the Docker host to get the UID of the mysql user that’s being used by the container for proper chowning once the data has been restored.

As you can see, we checked the content of the data directory as seen from the Docker host to get the UID of the mysql user that’s used in the container. We stopped the container, removed its data directory volume content, replaced it with the backup content, chowned it to the appropriate user ID, started the container, and was able to see our example data with no issues.

Conclusion

It’s fairly simple to use Docker for backing up your MySQL data set and this can be done for MySQL instances running in or out of Docker. This is made simple specifically by leveraging publically available Docker images and an entrypoint command that allows us to use arguments we’re already familiar with as backup conscious DBAs.

The bigger point here isn’t what we can do with an xtrabackup container, but why we would use it. I’ve run into situations in the past where there have been concerns about implementing new backup tools on running production systems, assuming xtrabackup wasn’t already in place. Despite its tried and true nature, running ‘yum install’ can make the best of us a little nervous. Using Docker, we can feel a bit more at ease as we have the common Docker container boundaries such as kernel namespaces and cgroups to help give us the warm fuzzies to implement new backup technologies or do simple trials of new tech with more confidence than we had before. That’s the beauty of containerization!

About the Author

Peter Sylvester is one of the Internal Principal Consultants in the Open Source Database Consulting Group at Pythian. He has been with Pythian since January of 2015 and has been working with MySQL since 2008. Apart from work, Peter is an avid ice hockey player to stay in keeping with the stereotypical Canadian lifestyle, playing typically no less than twice a week!

PYTHIAN®, LOVE YOUR DATA®, and ADMINISCOPE® are trademarks and registered trademarks owned by Pythian in North America and certain other countries, and are valuable assets of our company. Other brands, product and company names on this website may be trademarks or registered trademarks of Pythian or of third parties. Use of trademarks without permission is strictly prohibited.