Automate MySQL Backups in Docker with Crontab and Recover Using Binlog

Introduction

In production environments, regular MySQL backups are essential to prevent data loss. This guide shows how to use Linux crontab to schedule MySQL backups, with the database running inside a Docker container.

Crontab Scheduling

Use crontab -e to edit the crontab file. The five fields represent minute, hour, day of month, month, and day of week, followed by the command to execute. 0 5 * * 1 [command] For a daily backup at 8 PM, write: 0 8 * * * [command] Additional useful commands: crontab -l – list current crontab entries crontab -r – remove all crontab entries for the user

MySQL Backup

Create a directory for backups: mkdir -p /var/backups/mysql Run a backup with mysqldump inside the Docker container:

docker exec mysql_container mysqldump --single-transaction -uroot -proot_password database_name > /var/backups/mysql/test.sql

Verify that /var/backups/mysql/test.sql is created and has a reasonable size.

Add the backup command to crontab:

0 8 * * * docker exec mysql_container mysqldump --single-transaction -uroot -proot_password database_name > /var/backups/mysql/$(date +%Y%m%d_%H%M%S).sql

Crontab Optimization

Instead of writing many commands directly in the crontab file, place the backup logic in a separate Bash script, make it executable, and call the script from crontab.

#!/bin/bash
docker exec mysql_container mysqldump -uroot --single-transaction -pmypassword database_name > /var/backups/mysql/$(date +%Y%m%d_%H%M%S).sql

Make the script executable: chmod 711 /var/backups/mysql/mysqldump.sh Schedule the script:

0 20 * * * /var/backups/mysql/mysqldump.sh

Backup Optimization

Compress large .sql files and delete backups older than 15 days:

export mysqldump_date=$(date +%Y%m%d_%H%M%S) \
&& docker exec mysql_container mysqldump --single-transaction -uroot -pmypassword database_name > /var/backups/mysql/$mysqldump_date.sql \
&& gzip /var/backups/mysql/$mysqldump_date.sql \
&& find /var/backups/mysql/ -name "*.sql" -mtime +15 -exec rm -f {} \;

Data Recovery

If a DROP DATABASE command is executed accidentally, first recreate the database: mysql create database database_name; Then restore the most recent backup:

docker exec -i mysql_container mysql --single-transaction -uroot -proot_password database_name < /var/backups/mysql/20200619_120012.sql

Data created after the last full backup is not restored. To recover that data, use MySQL binlog files.

Binlog Logs

List existing binary logs: mysql> show binary logs; Show the current binlog file: mysql> show master status\G; Force MySQL to start a new binlog file: mysql> flush logs; View events in a specific binlog: mysql> show binlog events in 'binlog.000003'; Enable logging of full SQL statements by adding --binlog-rows-query-log-events=ON to the MySQL startup options or to /etc/mysql/my.cnf and restarting the container.

Recovering Data with Binlog

Use mysqlbinlog to replay events between a start and stop point:

mysqlbinlog [options] file | mysql -uroot -proot_password database_name

Common options include --start-datetime, --stop-datetime, --start-position, and --stop-position.

--start-datetime 2020-06-19 18:00:00 --stop-datetime 2020-06-19 20:00:00 --start-position 660 --stop-position 822

Example to recover data up to the point before a DROP DATABASE at position 822:

mysqlbinlog --start-datetime=2020-06-19 20:00:00 --stop-position=660 /var/lib/mysql/binlog.000003 | mysql -uroot -proot_password database_name

Do not include the -i (interactive) flag in Docker exec commands inside crontab, as it prevents the backup from writing to the output file.