Docker MySQL Automatic Backups using Cron and Supervisor

mysqldump command allows you to create an SQL dump file of the database. By executing this command using a scheduler application like Windows Task Scheduler or Linux Cron you can create periodic backups automatically.
But when it comes to Docker MySQL image you cannot do this since base image does not contain any scheduler application. Even though we can install a scheduler utility like Cron on MySQL image, it does not do what we want since the MySQL container executes only the DB application at the container startup. It does not trigger the executable related to the scheduler application.
Here is my solution to the above problem. Using 'Supervisor' application to execute both MySQL and Cron. Supervisor is a process monitoring and controlling application for UNIX-like operating systems.

Following is the step by step guide to the solution. All these steps are instructions in the Dockerfile of the final output image. You don't want to do these things manually except copying the contents of the files. My Dockerfile will do the job. I'm listing these thing here just for your clarification.

1. Use MySQL docker image as the base image.
2. Install Cron on base image.
3. Install Supervisor on base image.
4. Prepare supervisord.conf Supervisor configuration file so that it starts MySQL DB and Contrab.
5. Add an entry to Crontab file so that it execute the dbdump.sh (Shell script which contains the mysqldump commands) everyday 11.45pm.

Following are the files which do all these things. Place all files inside a single folder and build image of the final container.

Every commands are straight forward where you can search and find more information. But touchrootcron in supervisord.conf file is something special. This is a trick to solve permission problem in Docker layered file system (Read for Docker layered file system and copy-on-write (CoW) strategy). root crontab file should be in the writable layer container layer.

	echo *** Backup Started on `date` ***
	timestamp=`date +%Y%m%d_%H%M`
	echo Creating backup file mydb.sql
	mysqldump -u root --password="mydbpwd" --routines mydb > /home/lakj-backups/mydb-$timestamp.sql
	echo Finished creating backup file.

view raw dbdump.sh hosted with ❤ by GitHub

	FROM mysql:5.7
	MAINTAINER Lak J Comspace
	# Installing Crontab
	RUN apt-get update && apt-get -y install -qq --force-yes cron
	#Installing Supervisor
	RUN apt-get update && apt-get -y install -qq --force-yes supervisor
	# Copying supervisord.conf file as the supervisor config
	COPY supervisord.conf /etc/supervisor/conf.d/supervisord.conf
	# Add shell script which contains mysqldump command and grant execution rights
	ADD dbdump.sh /opt/lakj/dbdump.sh
	RUN chmod +x /opt/lakj/dbdump.sh
	# Folling entries will be added to the Crontab file. PATH entry should be available to execute the commands.
	# Log entries related to the backup process will be added to lakj-backup.log file.
	RUN echo "PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin" >> lakjcron
	RUN echo "45 23 * * * sh /opt/lakj/dbdump.sh >> /home/lakj-backups/lakj-backup.log 2>&1\n" >> lakjcron
	RUN crontab lakjcron
	RUN rm lakjcron
	# DB dumps will be created in this folder.
	RUN mkdir /home/lakj-backups
	# Command to execute Supervisor
	CMD ["/usr/bin/supervisord", "-c", "/etc/supervisor/supervisord.conf"]

view raw Dockerfile hosted with ❤ by GitHub

	[supervisord]
	nodaemon=true
	[program:touchrootcron]
	priority = 1
	startsecs = 0
	autorestart = false
	command=touch /var/spool/cron/crontabs/root
	[program:mysql]
	priority = 2
	startsecs = 0
	autorestart = false
	command=service mysql start
	[program:cron]
	priority = 3
	startsecs = 0
	autorestart = false
	command=/etc/init.d/cron start

view raw supervisord.conf hosted with ❤ by GitHub

Don't hesitate to put a comment if you have any problem.