November 22, 2014

What Are Full, Incremental, and Differential Backups?

Sometimes you might hear people talk about full backups, and differential backups versus incremental backups. What is the difference?

A full backup is pretty self-explanatory. It makes a copy of all of your MySQL data.

A differential backup, on the other hand, simply records the differences since your last full backup. The advantage of taking a differential backup is usually the space savings. Most databases have a lot of data that does not change from one backup to the next. Not copying this data into your backups can result in significantly smaller backups. In addition, depending on the backup tool used, a differential backup can be less labor-intensive for the server. If a differential backup does not have to scan all of the data to determine what has changed, the differential backup process can be significantly more efficient.

An incremental backup is a modification of a differential backup. An incremental backup records the changes since the last backup of any type, be it a differential or full backup. The advantages of incremental backups are similar to those of differential backups.

Here is an example that might explain this more clearly. Suppose that you take a full backup on Sunday. On Monday, you make a backup of all the changes since Sunday. This is a differential backup.

On Tuesday is when you begin to see the differences between the backup strategies. If you back up the changes since Sunday, then you have made a differential backup. If you back up the changes since Monday, it is an incremental backup.

Why would you choose an incremental versus a differential backup? That is a little bit out of scope for this blog post, because there are a lot of subtleties to consider. However, perhaps the biggest difference is in the way that you would restore a backup. Suppose that you need to restore your database on Friday. If you have taken differential backups all week long, you only need to restore Sunday, and then apply the changes that have happened since Sunday. If you have taken incremental backups, you must restore Sunday’s backup, and then apply changes repeatedly until you reach Friday. This can be more labor intensive, error-prone, and risky. It can also take longer.

Percona XtraBackup is capable of taking incremental backups, and when you specify the base backup as the previous incremental or full backup, you can easily make differential backups.

About Baron Schwartz

Baron is the lead author of High Performance MySQL.
He is a former Percona employee.

Comments

  1. Interesting distinction with ‘differential’ vs ‘incremental’, Baron. I had honestly not considered using the incremental backup feature of XtraBackup to ever backup the changes of anything other than your most recent backup, whether it have been a full backup or incremental backup.

    In XtraBackup Manager I tried to offset the time spent doing say, a restore on Friday, applying each set of incremental changes one after another since the full backup on Sunday, by providing a “materialized backups” feature, whereby XtraBackup Manager always maintains a fully materialized “restore” of the latest backup you took, so all the restore would require is copying that data to the restore target, not applying all the deltas. This is obviously at the expense of storage space.

    I might have to consider adding another “Backup Strategy” to XtraBackup Manager that uses the differential method, rather than incrementals…

  2. james says:

    This article describes full, incremental, and differential backups. The Backup tool in Windows 95 supports full and incremental backups. Differential backups are not supported.
    Backup uses backup file sets to help you manage backing up drives, folders, or files. Incremental and differential backups rely on an initial full backup of the drives, folders, or files in question.

  3. Colum Wells says:

    Can you please give examples of data usage with each of the types of backup e.g. A backup done once per week using full on the first week, then incremental for 7 weeks thereafter. Using 25MB on week1 then various differences for each of the weeks. Can you give an example in a table? I am doing a project with Open university, and would appreciate your help!

  4. Milo says:

    Hi Collum, you must be my fellow OU student. Get in touch. We may discuss the thing.

  5. Unknown says:

    Guys, I need help with this as well. Any ideas on this?

  6. Milo says:

    My mail is milo@netiq.sk get in touch

  7. VN says:

    Im having the same issue with that project. i could really use some help right about now. its been wrecking my head for a while nw

  8. Milo says:

    My mail is up there……….

  9. Milo says:

    my mail is up there……..

Speak Your Mind

*