Automating restore-tests for MariaDB with Docker

I use incremental xtrabackup with MariaDB and they need to be merged into the base backup from time to time or they'll keep growing.

But MariaDB replication being relatively new technology I didn't trust the merging so I basically wanted to monitor it, by doing a restore-test.

Here are the three broad steps I want to perform and monitor every week.

Check all the LSN sequences of the incremental backups and ensure they're sequential.
Merge all incremental backups into the base backup.
Restore the base backup and ensure it's working.

Step 2 could technically be performed every day to ensure the sequence isn't broken for any reason.

Step 3 is what I'll focus on here.

Restore base backup

Out of scope for this guide but it's only one command.

$ sudo innobackupex --copy-back /var/backups/xtrabackup

Dockerfile

Here's what a Dockerfile for the restore-test container could look like.

FROM ubuntu:14.04

MAINTAINER Stefan Midjich: 1.0

ENV DEBIAN_FRONTEND noninteractive

RUN apt-get update && \
    apt-get install -y software-properties-common && \
    apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db && \
    add-apt-repository 'deb [arch=amd64,i386,ppc64el] http://ftp.ddg.lth.se/mariadb/repo/10.1/ubuntu trusty main' && \
    apt-get update

RUN apt-get update && \
    apt-get install -y mariadb-server

ADD ./mysql-setup.sh /tmp/mysql-setup.sh
RUN /bin/sh /tmp/mysql-setup.sh

ADD ./run-mysql.sh /tmp/run-mysql.sh

CMD ["/bin/sh", "/tmp/run-mysql.sh"]

The ''mysql-setup.sh'' script prepares the config for running in Docker.

#!/bin/sh

# Listen on all IPs

sed -i -e"s/^bind-address\s*=\s*127.0.0.1/bind-address = 0.0.0.0/" /etc/mysql/my.cnf

# Ensure we run as root

sed -i -e "s/^user\s*=\s*mysql/user = root/" /etc/mysql/my.cnf

Lastly the ''run-mysql.sh'' script cleans up some logfiles that might interfere with the startup and runs mysqld.

#!/bin/sh

rm /var/db/mysql/aria_log_control
rm /var/db/mysql/ib_logfile*

exec /usr/sbin/mysqld --basedir=/usr --datadir=/var/db/mysql --user=root --skip-grant-tables

Running docker

This would run the docker, assuming /var/db/mysql is where you copied back the base backup files with innobackupex.

docker run -i -t -v /var/db/mysql:/var/db/mysql restoretest

Now once you've figured out the IP you can simply connect to the DB as root without any password.

mysql -h $(sudo docker inspect --format '{{ .NetworkSettings.IPAddress }}' XXXXX) -u root -p

Replacing XXXXX with your docker container ID.

And once you're satisified that the DB is intact you stop the docker container and report a success to the monitoring system.

Tips & workarounds

SElinux

On CentOS 7 for example you'll run into the problem of Docker not being allowed to write to your datadir volume.

I fix this easily with semanage, for example if ''/var/database/mysql'' is my datadir.

$ sudo semanage fcontext -a -t svirt_sandbox_file_t '/var/database/mysql(/.*)?'