MySQL Enterprise Backup Incremental Cumulative and Differential Backup

Preparing the MySQL Enterprise Administrator Training I found that the MySQL Enterprise Backup Incremental Backup is not described very well. Thus I tried it out and wrote down this how-to:

Differential Incremental Backup

Full Backup

mysqlbackup --user=root --backup-dir=/tape/full backup-and-apply-log
grep end_lsn /tape/full/meta/backup_variables.txt
end_lsn=2583666

Incremental Backups

mysqlbackup --user=root --incremental-backup-dir=/tape/inc1 --start-lsn=2583666 --incremental backup
grep end_lsn /tape/inc1/meta/backup_variables.txt
end_lsn=2586138

mysqlbackup --user=root --incremental-backup-dir=/tape/inc2 --start-lsn=2586138 --incremental backup
grep end_lsn /tape/inc2/meta/backup_variables.txt
end_lsn=2589328

mysqlbackup --user=root --incremental-backup-dir=/tape/inc3 --start-lsn=2589328 --incremental backup
grep end_lsn /tape/inc3/meta/backup_variables.txt
end_lsn=2592519

Binary Log Backups

cp /var/lib/binlog/binlog.* /tape/binlog/

Restore

This step will modify the original full backup!

mysqlbackup --incremental-backup-dir=/tape/inc1 --backup-dir=/tape/full apply-incremental-backup

mysqlbackup --incremental-backup-dir=/tape/inc2 --backup-dir=/tape/full apply-incremental-backup

mysqlbackup --incremental-backup-dir=/tape/inc3 --backup-dir=/tape/full apply-incremental-backup

mysqlbackup --user=root --datadir=/var/lib/mysql --backup-dir=/tape/full copy-back

Point-in-Time-Recovery

grep binlog_position /tape/inc3/meta/backup_variables.txt
/tape/inc3/meta/backup_variables.txt:binlog_position=binlog.000001:7731

cd /tape/binlog
mysqlbinlog --disable-log-bin --start-position=7731 binlog.000001 | mysql -uroot

Cumulative Incremental Backup

Full Backup

mysqlbackup --user=root --backup-dir=/tape/full backup-and-apply-log
grep end_lsn /tape/full/meta/backup_variables.txt
end_lsn=2602954

Incremental Backups

mysqlbackup --user=root --incremental-backup-dir=/tape/inc1 --start-lsn=2602954 --incremental backup

mysqlbackup --user=root --incremental-backup-dir=/tape/inc2 --start-lsn=2602954 --incremental backup

mysqlbackup --user=root --incremental-backup-dir=/tape/inc3 --start-lsn=2602954 --incremental backup

Binary Log Backups

cp /home/mysql/database/mysql-5.7/binlog/binlog.* /tape/binlog/

Restore

This step will modify the original full backup!

mysqlbackup --incremental-backup-dir=/tape/inc3 --backup-dir=/tape/full apply-incremental-backup

mysqlbackup --user=root --datadir=/var/lib/mysql --backup-dir=/tape/full copy-back

Point-in-Time-Recovery

grep binlog_position /tape/*/meta/backup_variables.txt
/tape/inc3/meta/backup_variables.txt:binlog_position=binlog.000001:7731

cd /tape/binlog
mysqlbinlog --disable-log-bin --start-position=7731 binlog.000001 | mysql -uroot

I very much dislike that during my restore the backup is modified. So if I do a mistake during restore my backup is gone and I am doomed.