MySQL/MariaDB with scheduled backups running in Docker
How to set up a MySQL/MariaDB server using Docker + Docker Compose
Containers keep on being a vibe in the IT market. They are useful for DevOps and, many times, enablers of IaC (Infrastructure as Code) strategies. With distinct applications and configurations, they can be used in production, staging, development, testing, and CI workloads.
In this article, I’ll address a MySQL/MariaDB environment setup with scheduled backups using Docker + Docker Compose. You’ll see how it becomes easy to deploy a reliable database server running in containers — and trust me: writing only 3 small text files. I assume that the reader does have at least a basic knowledge of Linux, Docker and Docker Compose, otherwise please take a look at https://www.docker.com/what-docker and https://docs.docker.com/compose/overview before proceeding.
First of all, let’s create a docker-compose.yml file with the MariaDB service, as follows:
When Docker run, it will download a MariaDB 10.2.16 image, start a container and the server within it and create a database named my-database using the root user with a password defined by the MYSQL_ROOT_PASSWORD environment variable (copied from a variable with the same name configured in the host machine). Pay attention to the /opt/mysql/data:/var/lib/mysql config — it’s responsible for making data persistent between container startups and shutdowns. How does it work? It “maps” the /var/lib/mysql folder from the container to host machine’s /opt/mysql/data folder every time the container is started up. If the host machine’s folder doesn’t exist, it is created by Docker, otherwise, it’s just mounted to the container in such way existing data is never lost. After that, just run docker-compose up --detach in the folder where the file is stored and see Docker in action.
Now we have a running MariaDB instance, ready for connections. You can google how to connect to the instance and easily find instructions, so I won’t repeat them here… Let’s move forward!
The next step is to schedule periodic backups for the database… let’s say, on a daily basis. Following containers' best practices, each container should only execute tasks related to its main purpose (aka “single software per container” principle). So we will add a second container to our project just to handle the backups. We can start with a basic Alpine Linux distribution — which has a built-in cron scheduler — to trigger backup jobs. A MariaDB client shall be installed in this container in order to communicate with the MariaDB server. So, the following lines will be appended to the docker-compose.yml file:
They will instruct Docker to start a container from a custom version of Alpine Linux to handle backups and the files will be persisted to the host machine’s /opt/mysql/backup folder, similar to what happened with the MariaDB container’s data. The custom image build process will be described in a separate Dockerfile with the below content:
Basically, it will build an Alpine image with an executable backup script copied from the project folder to the image’s /etc/periodic/daily folder. It will also have the mariadb-client package installed. As the script resides in the container’s “daily” folder, it will be executed once every day. A valid script will contain some lines of code, but the most important one would be something similar to mysqldump -h <HOST> -u <USER> -p<PASSWORD> --databases <database> | gzip -9 <file> (please refer to the mysqldump docs for more options).
A full running example can be found at https://github.com/ricardolsmendes/docker-samples. Clone it, cd docker-samples/mysql-mariadb and docker-compose --file docker-compose.server-backup.yml up --detach to see it in action :).
With small changes, such as replacing my-database by an actual database name, the project is ready to be used in development or QA, making the initial setup of those environments very simple — many thanks, Docker! :). It could also be improved for production usage by adding extra steps to the daily backup script, such as copying the generated dump to another server using SFTP or rsync to improve fail-recover support.
Happy coding!
REFERENCES
- How to run cron jobs with Docker: firefart.at/post/docker_and_cron_jobs
- mysqldump CLI options: mariadb.com/kb/en/mysqldump/#options
