A properly configured backup helps you bounce back from a server failure within minutes. However, backups impose a performance penalty and it is known to even bring down servers due to high load. So, backups should be setup such that it uses as little resources as possible.

Why use Percona incremental backup

Taking a full Percona backup involves copying all database files into an external device, and then re-applying all transactions that happened during the backup. This takes up a lot of disk input/output operations.

Hard disk being the slowest component on a server, such disk intensive operations can put a many valid database accesses on hold – causing websites to be slower, eCommerce transactions to time out, etc.

Incremental backup is a backup method by which, only the data that changed since the last backup is included in the new backup. For eg. if a backup was taken on Monday, only new data added or changed in 24 hours is included in Tuesday’s backup. This minimizes the disk I/O operations, and reduces server load.

How to setup incremental backups in Percona

Incremental backups in Percona can be taken using XtraBackup or InnoBackupEx. These tools store each new backup in a new directory, and connects all backups together using a backup ID called Log Sequence Number (LSN).

In many database servers, we take incremental backups once a day, and full backup once a week. Here’s how we set up these systems.

1. Setting up the initial full backup

Incremental backups are basically differences (aka “deltas”) from the last backup. So, to setup an incremental backup, first a full backup needs to be taken. This is usually done on Sundays when the traffic on the server is low.

To take full backups, InnoBackupEx is better than XtraBackup. While XtraBackup copies only .ibd files, InnoBackupEx copies entire database information like table definitions, triggers, etc. This helps in a full restore even in an offsite facility. Here’s the command to take a full backup using default file paths from /etc/my.cnf.

innobackupex --user=DBUSER --password=DBUSERPASS /backup/db/full/sun/ --no-timestamp

2. Running an incremental backup

InnoBackupEx creates a file called xtrabackup_checkpoints, in which it stores a Log Sequence Number (LSN) indicating the size of the backup file. It looks like this:

backup_type = full-backuped
from_lsn = 0
to_lsn = 1137914

Now, to run an incremental backup on top of the full backup, the “base directory” should be set to “/backup/db/full/sun/“.

innobackupex --incremental /backup/db/inc/mon/ --incremental-basedir=/backup/db/full/sun/ --no-timestamp

This will copy all the changes made from Sunday to Monday in a new directory – “/backup/db/inc/mon/”. In this directory, the “from_lsn” number in xtrabackup_checkpoints will be 1137914, as it is a continuation of the full backup. For eg:

backup_type = incremental
from_lsn = 1137914
to_lsn = 1208892

Similarly, incremental backups for Tuesday should have a base directory set to Monday, backup of Wednesday set to Tuesday, and so on. For eg:

innobackupex --incremental /backup/db/inc/tue/ --incremental-basedir=/backup/db/inc/mon/ --no-timestamp

In this way, you can continue taking incremental backups till Saturday. On Sunday, a fresh new full backup will replace the old full backup, and the whole process repeats.

Of course, if you want a different full backup periodicity (eg. once a month) use another directory naming system such as /backup/db/full/01/, /backup/db/inc/02/, /backup/db/inc/03/, etc.

[ Want to avoid downtime due to database server failures? Here’s how to setup Percona high availability. ]

How to restore incremental backups in Percona

Now, in the example we used, we have one full backup taken on Sunday, and 6 incremental backups taken on the other days.

Let’s say, we want the database state as of Wednesday restored. For this, we will need to take Sunday’s full backup, and add on top to it Monday, Tuesday and Wednesday’s incremental backup. This is called “preparing” a backup.

1. Preparing a Percona incremental backup

At the time of taking a backup, there might be transactions that are not fully completed. So, the files copied during that time would be in a non-usable state. To apply all broken transactions, to discard transactions with incomplete log, and to add all incrementals to the base backup, all transaction logs need to be applied.

Here’s how it can be done for our weekly full backup example.

innobackupex --apply-log --redo-only /backup/db/full/sun/

Now, add the incremental taken on Monday.

innobackupex --apply-log --redo-only /backup/db/full/sun/ --incremental-dir=/backup/db/inc/mon/

Similarly, add Tuesday’s and Wednesday’s logs in sequence.

innobackupex --apply-log --redo-only /backup/db/inc/mon/ --incremental-dir=/backup/db/inc/tue/
innobackupex --apply-log /backup/db/full/tue/ --incremental-dir=/backup/db/inc/wed/

Note that, for the last incremental, the “–redo-only” option was skipped. This will tell InnoBackupEx that any incomplete transactions from this point on should be discarded. That instruction is given using the command:

innobackupex --apply-log /backup/db/full/sun/

Now you have a full, ready-to-use backup from Wednesday.

2. Restoring the backup

InnoBackupEx helps you copy all the needed files to restore a fully functional database. For that, use the command:

innobackupex --copy-back /backup/db/full/sun/

It will use the default settings in /etc/my.cnf to copy all the databases to the database directory. Now, make sure all ownership and permissions of the database files are correct and restart the database.

You then have a fully functional database restored using incremental backups.

[ XtraBackup is a popular alternative to InnoBackupEx. Here’s how you can backup your Percona database using XtraBackup. ]

A good backup design will allow you to recover from a downtime within a matter of minutes. However, if the backup process fails due to high load, disk space issues, or any other such server issues, the disaster recovery plan will fail.

It is important to monitor the backup process, and re-initiate the backup in case the backup process does not complete successfully. Bobcares helps online businesses maintain reliable backups through 24/7 server monitoring, backup audits, and random database restore tests. If you’d like to know how to make your website more reliable, we’d be happy to talk to you.