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’.
[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": {
"IPAMConfig": null,
"Links": null,
"Aliases": null,
"NetworkID": "0cf8f76e253de2969655ad905f7866d04f9276c296422dd60f5c66cce589b891",
"EndpointID": "9ce20efa5d5d47c3323e1cdc651fbf190e226a3a9b5e37da04547b366e481e69",
"Gateway": "172.17.0.1",
"IPAddress": "172.17.0.2",
"IPPrefixLen": 16,
"IPv6Gateway": "",
"GlobalIPv6Address": "",
"GlobalIPv6PrefixLen": 0,
"MacAddress": "02:42:ac:11:00:02"
}
}
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.
[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
encryption: using gcrypt 1.6.3
180523 13:46:13 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=172.17.0.2;port=3306;mysql_socket=/var/run/mysqld/mysqld.sock' as 'root' (using password: YES).
180523 13:46:13 version_check Connected to MySQL server
180523 13:46:13 version_check Executing a version check against the server...
180523 13:46:13 version_check Done.
180523 13:46:13 Connecting to MySQL server host: 172.17.0.2, user: root, password: set, port: 3306, socket: /var/run/mysqld/mysqld.sock
Using server version 5.7.22
......
180523 13:46:32 Executing UNLOCK TABLES
180523 13:46:32 All tables unlocked
180523 13:46:32 [00] Copying ib_buffer_pool to /xtrabackup_backupfiles/ib_buffer_pool
180523 13:46:32 [00] ...done
180523 13:46:32 Backup created in directory '/xtrabackup_backupfiles/'
180523 13:46:32 [00] Writing /xtrabackup_backupfiles/backup-my.cnf
180523 13:46:32 [00] ...done
180523 13:46:32 [00] Writing /xtrabackup_backupfiles/xtrabackup_info
180523 13:46:32 [00] ...done
xtrabackup: Transaction log of lsn (11906527) to (11906536) was copied.
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
drwxr-x--- 2 root root 4.0K May 23 09:46 mysql
drwxr-x--- 2 root root 8.0K May 23 09:46 performance_schema
drwxr-x--- 2 root root 8.0K May 23 09:46 sys
-rw-r----- 1 root root 115 May 23 09:46 xtrabackup_checkpoints
-rw-r----- 1 root root 436 May 23 09:46 xtrabackup_info
-rw-r----- 1 root root 2.5K May 23 09:46 xtrabackup_logfile
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.
[root@centos7-1 ~]# docker run --rm -it -v /backup:/xtrabackup_backupfiles perconalab/percona-xtrabackup --prepare --target-dir=/xtrabackup_backupfiles
encryption: using gcrypt 1.6.3
xtrabackup version 2.4.11 based on MySQL server 5.7.19 Linux (x86_64) (revision id: b4e0db5)
xtrabackup: cd to /xtrabackup_backupfiles/
.....
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 11907112
180523 13:52:02 completed OK!
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.
[root@centos7-1 ~]# docker run -p 3307:3306 --name=mysql2 -e MYSQL_ROOT_PASSWORD=password -d mysql/mysql-server:5.7.22
c2d7889744e1372481a637a67c7d7363128ab78f51d3725c52e943fe111a1321
[root@centos7-1 ~]# docker inspect mysql2
[
.....
"Mounts": [
{
"Type": "volume",
"Name": "c3bd8fce20f3bc4bc5175a30aa522f4bad84f02a6556da288fe19f1e1e187d72",
"Source": "/var/lib/docker/volumes/c3bd8fce20f3bc4bc5175a30aa522f4bad84f02a6556da288fe19f1e1e187d72/_data",
"Destination": "/var/lib/mysql",
"Driver": "local",
"Mode": "",
"RW": true,
"Propagation": ""
}
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.
[root@centos7-1 ~]# docker stop mysql2
mysql2
[root@centos7-1 ~]# ls -lh /var/lib/docker/volumes/c3bd8fce20f3bc4bc5175a30aa522f4bad84f02a6556da288fe19f1e1e187d72/_data
total 173M
-rw-r----- 1 27 27 56 May 23 09:55 auto.cnf
-rw------- 1 27 27 1.7K May 23 09:55 ca-key.pem
-rw-r--r-- 1 27 27 1.1K May 23 09:55 ca.pem
-rw-r--r-- 1 27 27 1.1K May 23 09:55 client-cert.pem
-rw------- 1 27 27 1.7K May 23 09:55 client-key.pem
-rw-r----- 1 27 27 668 May 23 09:56 ib_buffer_pool
-rw-r----- 1 27 27 76M May 23 09:56 ibdata1
-rw-r----- 1 27 27 48M May 23 09:56 ib_logfile0
-rw-r----- 1 27 27 48M May 23 09:55 ib_logfile1
drwxr-x--- 2 27 27 4.0K May 23 09:55 mysql
drwxr-x--- 2 27 27 8.0K May 23 09:55 performance_schema
-rw------- 1 27 27 1.7K May 23 09:55 private_key.pem
-rw-r--r-- 1 27 27 452 May 23 09:55 public_key.pem
-rw-r--r-- 1 27 27 1.1K May 23 09:55 server-cert.pem
-rw------- 1 27 27 1.7K May 23 09:55 server-key.pem
drwxr-x--- 2 27 27 8.0K May 23 09:55 sys
[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 ~]# ls -lh /var/lib/docker/volumes/c3bd8fce20f3bc4bc5175a30aa522f4bad84f02a6556da288fe19f1e1e187d72/_data
total 193M
-rw-r----- 1 27 27 487 May 23 09:46 backup-my.cnf
drwxr-x--- 2 27 27 48 May 23 09:46 dockertest
-rw-r----- 1 27 27 1.3K May 23 09:46 ib_buffer_pool
-rw-r----- 1 27 27 76M May 23 09:52 ibdata1
-rw-r----- 1 27 27 48M May 23 09:52 ib_logfile0
-rw-r----- 1 27 27 48M May 23 09:52 ib_logfile1
-rw-r----- 1 27 27 12M May 23 09:52 ibtmp1
drwxr-x--- 2 27 27 4.0K May 23 09:46 mysql
drwxr-x--- 2 27 27 8.0K May 23 09:46 performance_schema
drwxr-x--- 2 27 27 8.0K May 23 09:46 sys
-rw-r----- 1 27 27 115 May 23 09:52 xtrabackup_checkpoints
-rw-r----- 1 27 27 436 May 23 09:46 xtrabackup_info
-rw-r----- 1 27 27 8.0M May 23 09:52 xtrabackup_logfile
-rw-r--r-- 1 27 27 1 May 23 09:51 xtrabackup_master_key_id
[root@centos7-1 ~]# docker start mysql2
mysql2
[root@centos7-1 ~]# docker exec -it mysql2 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 |
+------+
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!
