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

[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

[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!

[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": ""
 }

[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!