Incremental backups based on full backups

MySQL & MariaDB Percona XtraBackup
, ,
1

I am setting up automated full backups and incremental backups as per the below documentation.

Going through the script, I understand that every time incremental backup is triggered, it is based on the same full backup of the day. Shouldn’t this always be based on the previous backup which could be an incremental backup as well ? Is the script wrong or am I understanding the process incorrectly ?

Thanks in advance.

2

Your understanding is correct. The documentation specifies that you take a full backup and then the incremental hourly backups are taken with the base as full backup.

You can set the base backup as previous incremental backup too and that is how you can tweak the script.
You should also consider that you don’t end up taking incremental backups for a long long time. so that when you restore then you need to restore number of incremental backups.
That is just an example script but you can off course set it according to your requirement.

3

Hey @Krishnadas_K_P

This actually creates “differential” backups. A differential backup is a type of incremental which always starts from the original full backup. Yes, incremental backups should reference the previous incremental.

As Yunus said, do not take incrementals often. You will add unnecessary load to the system. Your MySQL server already has a way to replay transactions, called the binary log.

The best practice is to take a daily full backup, and also backup the binary logs. This allows for point-in-time recovery. Restore the full, replay the binlogs.

Incremental backups are useful to shrink the gap between full and last binlog entry.

4

Thank you very much @matthewb.

I have setup binlog purging only after 7 days. If I have a daily full backup, I should be able to PITR to atleast 7 days even without incremental backups I think. Is that correct ?

5

Thanks @Yunus for the prompt response

6

Once you create a full backup, you can delete all binary logs that came before because the full backup represents a snapshot in time. Restoring that puts your data at, for example, 03:44. Then you replay binary logs to bring you to 18:32. You would keep more binary logs in case you needed to go back to previous day, or days before full backup and play forward.

If you take a full backup on Sunday, you can, in theory, never take a backup again until Saturday since you have 7 days of binlogs, correct. You would restore that full from Sunday, then replay to any point.