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.
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
[root@centos7-1 ~]# docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES d5d980ee01d5 mysql/mysql-server:5.7.22 "/entrypoint.sh my..." 19 hours ago Up 2 minutes (healthy) 0.0.0.0:3306->3306/tcp, 33060/tcp mysql1 [root@centos7-1 ~]# docker exec -it mysql1 mysql -u root -ppassword -e "Select * from dockertest.t1"; mysql: [Warning] Using a password on the command line interface can be insecure. +------+ | c1 | +------+ | 1 | | 2 | | 3 | +------+ 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.
[root@centos7-1 ~]# docker inspect mysql1 [ .... "Mounts": [ .... { "Type": "volume", "Name": "f8ade307ebd48638b5364bab4c3e352799c6b0404bfbbd25dbc12d663c174a3a", "Source": "/var/lib/docker/volumes/f8ade307ebd48638b5364bab4c3e352799c6b0404bfbbd25dbc12d663c174a3a/_data", "Destination": "/var/lib/mysql", "Driver": "local", "Mode": "", "RW": true, "Propagation": "" } .... "Networks": { "bridge": { "Gateway": "172.17.0.1", "IPAddress": "172.17.0.2", ... } } In order to back this up, we will need to launch a container based on the perconalab/percona-xtrabackup image. 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. We specify the network location of the container we want to back up and provide the necessary MySQL user credentials. We are leveraging the --rm argument so the container removes itself once finished.
[root@centos7-1 ~]# docker run --rm -it -v /var/lib/docker/volumes/f8ade307ebd48638b5364bab4c3e352799c6b0404bfbbd25dbc12d663c174a3a/_data:/var/lib/mysql -v /backup:/xtrabackup_backupfiles perconalab/percona-xtrabackup --backup --host=172.17.0.2 --user=root --password=password ... 180523 13:46:32 Backup created in directory '/xtrabackup_backupfiles/' ... 180523 13:46:32 completed OK! [root@centos7-1 ~]# ls -lh /backup/ total 77M -rw-r----- 1 root root 487 May 23 09:46 backup-my.cnf drwxr-x--- 2 root root 48 May 23 09:46 dockertest -rw-r----- 1 root root 1.3K May 23 09:46 ib_buffer_pool -rw-r----- 1 root root 76M May 23 09:46 ibdata1 ... Now we have a fresh backup in the /backup directory, but we need to prepare it for restore. We launch the container again, making sure the backup data files are available and passing the --prepare argument.
[root@centos7-1 ~]# docker run --rm -it -v /backup:/xtrabackup_backupfiles perconalab/percona-xtrabackup --prepare --target-dir=/xtrabackup_backupfiles ... InnoDB: Shutdown completed; log sequence number 11907112 180523 13:52:02 completed OK! Now we need a place to restore it. I am going to launch a second container (mysql2). Again, we use docker inspect to find out where its volume was created on the host.
[root@centos7-1 ~]# docker run -p 3307:3306 --name=mysql2 -e MYSQL_ROOT_PASSWORD=password -d mysql/mysql-server:5.7.22 [root@centos7-1 ~]# docker inspect mysql2 ... "Source": "/var/lib/docker/volumes/c3bd8fce20f3bc4bc5175a30aa522f4bad84f02a6556da288fe19f1e1e187d72/_data", ... To restore, we stop the container (which performs a safe MySQL shutdown), remove the existing data, move our backup into place, and correct the ownership (UID 27 is typically the mysql user inside the container).
[root@centos7-1 ~]# docker stop mysql2 [root@centos7-1 ~]# rm -rf /var/lib/docker/volumes/c3bd8fce20f3bc4bc5175a30aa522f4bad84f02a6556da288fe19f1e1e187d72/_data [root@centos7-1 ~]# mv /backup /var/lib/docker/volumes/c3bd8fce20f3bc4bc5175a30aa522f4bad84f02a6556da288fe19f1e1e187d72/_data [root@centos7-1 ~]# chown -R 27:27 /var/lib/docker/volumes/c3bd8fce20f3bc4bc5175a30aa522f4bad84f02a6556da288fe19f1e1e187d72/_data [root@centos7-1 ~]# docker start mysql2 [root@centos7-1 ~]# docker exec -it mysql2 mysql -u root -ppassword -e "select * from dockertest.t1" +------+ | c1 | +------+ | 1 | | 2 | | 3 | +------+ 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 publicly 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 where there have been concerns about implementing new backup tools on running production systems. Running yum install can make the best of us a little nervous.
Using Docker, we can feel a bit more at ease; we have container boundaries like kernel namespaces and cgroups to help give us the confidence to implement new backup technologies or perform simple trials of new tech with more security than before. That’s the beauty of containerization!
Looking to optimize your MySQL use?