How to Automate Backups on Linux/UNIX Using MySQL Enterprise Backup (MEB)

Goal

Backups should be made regularly.  The easiest way to do so on Linux/UNIX is to write a cron job. In this article MEB users will find two crontab templates: one for weekly full backups and another for daily incremental backups.

Solution

Although MEB allows for the creation of incremental backups, it is still best practice to run full backups periodically.   Below you will find a schedule which makes weekly full backups and daily incremental backups.

Weekly full backup can be done using following crontab command:

@weekly mysqlbackup
--defaults-file=~/backup.cnf
--socket=/tmp/mysql_ssmirnova.sock
--user=root --backup-dir=~/BACKUPDIR/km1381211_1
--with-timestamp backup

The commands are split into different lines for readability - when inputting, must be done in a single line.

For ease of later use and discovery, the following incremental backups automatically use option --with-timestamp here, so you don't need to generate backup directory names yourself.

To make daily incremental backups you need to create cron task in two steps.

First step is necessary, because cron does not allow syntax which we will use in the shell file to extract directory name. Here is example for BASH:

#!/bin/bash

mysqlbackup
--defaults-file=~/backup.cnf
--socket=/tmp/mysql_ssmirnova.sock
--user=root --incremental
--incremental-backup-dir=~/BACKUPDIR/km1381211_1
--incremental-base=dir:$(ls -1d ~/BACKUPDIR/km1381211_1/$(date --date=yesterday +%F_)*)
--with-timestamp backup

Save this file as daily_incremental.sh, make it executable, then add following job to crontab:

@daily daily_incremental.sh

Backups, again, are called with --with-timestamp option. Option --incremental-base=dir:$(ls -1d ~/BACKUPDIR/km1381211_1/$(date --date=yesterday +%F_)*)  points mysqlbackup to a directory where previous backup is stored, so it can read end_lsn of previous backup from the backup itself and you don't need to specify --start-lsn manually.

Option --incremental-base exists only since version 3.7, therefore, if you use earlier version you should parse file meta/backup_variables.txt  yourself to obtain end_lsn of previous backup, then specify it in the command.

If you want to change schedule just replace @weekly and @daily with time and date field which reflects your needs.