Differential and incremental MySQL backups

It often happens that the loss of data, even in a short period of time, is very sensitive, and it becomes necessary to make backups as often as possible.

Creating full backups of large databases more often than once a day can be difficult – as a rule, due to the size of the dumps, the ability to copy only the changes made will be very useful here.

Depending on the backup strategy, differential and incremental backups can be used, in addition to full ones. A differential backup contains changes in the data relative to a full backup, an incremental backup contains changes since the last partial backup – the last incremental backup.

Depending on the size of the databases and the required backup frequency, the strategies may differ noticeably, but I will consider the option with a “weekly” plan, when a full backup is created at the end of the week, a differential backup is created at the beginning of each working day, and every hour during working hours – creating an incremental backup.

In the worst case scenario, such a plan will allow you to save data without losing more than one hour, or restore the state of the databases at the beginning of any working hour. And the presence of a differential backup will allow you to reduce the number of necessary copies used for recovery.

Installing XtraBackup and Configuring a Schedule

Further actions were performed on CentOS 8, you can download the appropriate version of XtraBackup from

official site

…

To create a full backup, you need to run xtrabackup with option –backup… Additionally, you can use the –target-dir option, which points to the directory where the backup will be created, if the directory for creating the backup does not exist, Xtrabackup will create it.

By default, XtraBackup looks for server configuration data and user connection data from files:

• /etc/my.cnf
• /etc/mysql/my.cnf
• /usr/etc/my.cnf
• ~ / .my.cnf

If there is a need to override the server and client settings, you can create a separate .cnf file and specify it with the –defaults-file option, I will not use this option, since the username and password for connecting the MySQL client are set in / root / .my.cnf:

[client]
user=root
password=mysqlpassword

1. Installation:

Download and install XtraBackup from RPM:

wget 
https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.26-18/binary/redhat/8/x86_64/percona-xtrabackup-80-8.0.26-18.1.el8.x86_64.rpm
yum localinstall percona-xtrabackup-80-8.0.26-18.1.el8.x86_64.rpm

If you plan to use compression, you will need to install Qpress:

wget 
https://repo.percona.com/yum/release/8/RPMS/x86_64/qpress-11-1.el8.x86_64.rpm
yum localinstall qpress-11-1.el8.x86_64.rpm

2. Check that the installation was successful:

xtrabackup -v

3. Let’s create a minimal version of the script that can be called on schedule in cron:

#!/bin/sh

BACKUP_PATH="/data/backups/db"
BACKUP_FULL="$(date -dlast-monday +%Y%m%d)-FULL"
BACKUP_DIFF="$(date +%Y%m%d)-DIFF"
BACKUP_INCR="$(date +%Y%m%d)-INCR-$(date +%H%M)"
XTRABACKUP_THREADS="--parallel 2"
XTRABACKUP_COMPRESS_OPTIONS="--compress --compress-threads=2"

case "$1" in
"")
echo "Required parameter missing! Use full, diff, incr as first parameter to make corresponding backup."
;;

"full")
if [[ ! -e $BACKUP_PATH/$BACKUP_FULL ]]; then
/usr/bin/xtrabackup --backup $XTRABACKUP_THREADS $XTRABACKUP_COMPRESS_OPTIONS --no-timestamp --target-dir=$BACKUP_PATH/$BACKUP_FULL
echo "Full backup complete."
else
echo "ERROR: Full backup already exist for current stage!"
exit
fi
;;

"diff")
if [[ ! -e $BACKUP_PATH/$BACKUP_FULL ]]; then
echo "ERROR: Corresponding full backup doesnt exists!"
exit
elif [[ ! -e $BACKUP_PATH/$BACKUP_DIFF ]]; then
/usr/bin/xtrabackup --backup $XTRABACKUP_THREADS $XTRABACKUP_COMPRESS_OPTIONS --no-timestamp --target-dir=$BACKUP_PATH/$BACKUP_DIFF --incremental-basedir=$BACKUP_PATH/$BACKUP_FULL
echo "Differential backup complete."
else
echo "ERROR: Differential backup exists for current stage!"
fi
;;

"incr")
if [[ ! -e $BACKUP_PATH/$BACKUP_DIFF ]]; then
echo "ERROR: Corresponding differential backup doesnt exists!"
exit
elif ! compgen -G "$BACKUP_PATH/$(date +%Y%m%d)-INCR*" > /dev/null; then
echo "Incremental backup dir not found - differential backup used as incremental basedir..."
/usr/bin/xtrabackup --backup $XTRABACKUP_THREADS $XTRABACKUP_COMPRESS_OPTIONS --no-timestamp --target-dir=$BACKUP_PATH/$BACKUP_INCR --incremental-basedir=$BACKUP_PATH/$BACKUP_DIFF
echo "Incremental backup complete."
exit
else
echo "Incremental backup dir found - last incremental backup used as incremental basedir..."
INCR_PATH_BASE="$(find /data/backups/db/ -name "*INCR*" -printf "%Pn" | sort -n | tail -1)"
/usr/bin/xtrabackup --backup $XTRABACKUP_THREADS $XTRABACKUP_COMPRESS_OPTIONS --no-timestamp --target-dir=$BACKUP_PATH/$BACKUP_INCR --incremental-basedir=$BACKUP_PATH/$INCR_PATH_BASE
echo "Incremental backup complete."
exit
fi
;;
esac

4. Add a schedule to / etc / crontab:

# Full mysql backup every monday at 00:00
0 0 * * MON    root    /opt/mysql-backup/mysql-backup full

# Differential backup every day at 08:00
0 8 * * *      root    /opt/mysql-backup/mysql-backup diff

# Incremental backup every hour between 09:00-20:00
0 9-20 * * *    root    /opt/mysql-backup/mysql-backup incr

Now the backup will be performed according to the specified schedule to the directory

/data/backups/db

(as it was set in the script), but you need to take into account that the copying will not start until a full and differential backup is created (on Monday), therefore, to test the script’s work, we will create them manually:

/opt/mysql-backup/mysql-backup full
/opt/mysql-backup/mysql-backup diff

Recovery

To restore the state of the bases for the required time, we will need to follow the steps in sequence:

• Unzip required backups if compression was used
• Prepare a full backup and apply the required backups (full, differential and all subsequent incremental ones until the desired time)
• Stop the mysql server, if necessary, make a copy of the current server files
• Copy files from the prepared copy to the place of the original files

1. Unzipping

xtrabackup --decompress --target-dir=/data/backups/db/20210913-FULL/
xtrabackup --decompress --target-dir=/data/backups/db/20210916-DIFF/
xtrabackup --decompress --target-dir=/data/backups/db/20210916-INCR-2239/

At the stage of preparation, in the case when subsequent incremental backups will be applied to the full backup, you must specify the option

--apply-log-only

to avoid canceling pending transactions.

2. Preparation:

xtrabackup --prepare --apply-log-only --target-dir=/data/backups/db/20210913-FULL/
xtrabackup --prepare --apply-log-only --target-dir=/data/backups/db/20210913-FULL/ --incremental-dir=/data/backups/db/20210916-DIFF/
xtrabackup --prepare --apply-log-only --target-dir=/data/backups/db/20210913-FULL/ --incremental-dir=/data/backups/db/20210916-INCR-2239/

Now that in the directory

/data/backups/db/20210913-FULL/

we have ready-to-use database files, all that remains is to stop the server, delete or move old files and copy the new files to their original location and restore the owner of the files (mysql).

3. Application of the backup:

systemctl stop mysqld
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/data/backups/db/20210913-FULL/
chown -R mysql. /var/lib/mysql
systemctl start mysqld

Conclusion

As you know, administrators are divided into those who do not make a backup, and those who are already doing it. The latter can also be divided into those who do not check the integrity of the backups, and those who are already checking.

The step with checking the integrity is beyond the scope of this article, which does not negate its importance, it is also worth mentioning that the article does not describe all the nuances of using Xtrabackup, but it can serve as a starting point for administrators who decide to make a backup more often than once a day.

---

UFO flew in and left here promotional codes for the readers of our blog:

HABRFIRSTVDS – 15% discount on all VDS tariffs, except for VDS Warm-up, on the website firstvds.ru…

HABRFIRSTDEDIC – 20% discount on flexible dedicated server configurations based on AMD Ryzen and Intel Core processors on the website 1dedic.ru…

The discount is applied for the entire paid period (1, 3, 6 or 12 months) and applies only to server resources. You can use the promo code until 12/31/21 when ordering any number of new servers.