Full vs Incremental vs Differential Backups

Before diving into incrementals, let's clarify the three main backup strategies:

Full Backup

A complete copy of your entire database. Every backup is independent and self-contained. Simple to restore but expensive in storage and time.

Incremental Backup

Only captures changes since the LAST backup (full or incremental). Each incremental backup is small but depends on all previous backups in the chain.

Differential Backup

Captures all changes since the last FULL backup. Larger than incrementals but only requires the full backup plus one differential to restore.

How MySQL Incremental Backups Work

MySQL incremental backups leverage InnoDB's internal tracking mechanisms to identify which data pages have changed since a specific point.

Log Sequence Number (LSN)

Every time InnoDB modifies data, it increments a counter called the Log Sequence Number. This LSN is stored with each data page and in the redo log. By comparing LSNs, backup tools know exactly which pages have changed.

When you take a full backup, the tool records the final LSN. Subsequent incremental backups only copy pages with LSNs higher than the reference point.

The Backup Chain

An incremental backup strategy creates a chain: one full backup followed by multiple incrementals. To restore, you must have the complete chain.

Sunday:    Full Backup (LSN: 0 -> 1000000)
Monday:    Incremental (LSN: 1000000 -> 1050000)
Tuesday:   Incremental (LSN: 1050000 -> 1095000)
Wednesday: Incremental (LSN: 1095000 -> 1160000)
Thursday:  Incremental (LSN: 1160000 -> 1210000)

To restore Thursday's state:
- Apply Full Backup
- Apply Monday's incremental
- Apply Tuesday's incremental
- Apply Wednesday's incremental
- Apply Thursday's incremental

Implementing Incremental Backups

Here's how to set up incremental backups using Mariabackup (similar process for Percona XtraBackup):

Step 1: Create Base Full Backup

# Create full backup
mariabackup --backup \
  --target-dir=/backup/base \
  --user=backup_user \
  --password=secret

After completion, check the LSN in xtrabackup_checkpoints:

cat /backup/base/xtrabackup_checkpoints
# backup_type = full-backuped
# from_lsn = 0
# to_lsn = 1000000
# last_lsn = 1000005

Step 2: Create Incremental Backups

# First incremental - based on full backup
mariabackup --backup \
  --target-dir=/backup/inc1 \
  --incremental-basedir=/backup/base \
  --user=backup_user \
  --password=secret

# Second incremental - based on first incremental
mariabackup --backup \
  --target-dir=/backup/inc2 \
  --incremental-basedir=/backup/inc1 \
  --user=backup_user \
  --password=secret

Step 3: Prepare for Restore

Before restoring, you must 'prepare' the backups by applying the redo log and merging incrementals:

# Prepare base backup (keep redo log for incrementals)
mariabackup --prepare \
  --apply-log-only \
  --target-dir=/backup/base

# Apply first incremental
mariabackup --prepare \
  --apply-log-only \
  --target-dir=/backup/base \
  --incremental-dir=/backup/inc1

# Apply second incremental (last one, no --apply-log-only)
mariabackup --prepare \
  --target-dir=/backup/base \
  --incremental-dir=/backup/inc2

Step 4: Restore

# Stop MySQL
systemctl stop mariadb

# Remove old data
rm -rf /var/lib/mysql/*

# Copy prepared backup
mariabackup --copy-back --target-dir=/backup/base

# Fix permissions
chown -R mysql:mysql /var/lib/mysql

# Start MySQL
systemctl start mariadb

Storage and Time Savings

The efficiency of incremental backups depends on your data change rate. Here are typical scenarios:

Example: 100GB E-commerce Database

Factors Affecting Incremental Size

• Write activity volume - More changes = larger incrementals
• Data distribution - Changes to many tables spread across pages
• Page size - InnoDB backs up full 16KB pages even for small changes
• Compression effectiveness - Already-compressed data doesn't shrink much more
• Backup frequency - More frequent = smaller individual incrementals

Managing Incremental Chains

Long incremental chains create risk and complexity. If any backup in the chain is corrupted, you lose all subsequent recovery points.

Chain Length Strategy

Balance between storage efficiency and risk. Common approaches:

• Weekly full + daily incrementals (7-backup chains)
• Daily full + hourly incrementals (24-backup chains)
• Daily full + 15-minute incrementals (96-backup chains)
• Hourly full + 15-minute incrementals (4-backup chains)

Rotation and Retention

Implement a rotation strategy that maintains multiple complete chains:

Week 1: Full (Sun) + Inc (Mon-Sat) -> Keep 7 days
Week 2: Full (Sun) + Inc (Mon-Sat) -> Keep 7 days  
Week 3: Full (Sun) + Inc (Mon-Sat) -> Keep 7 days
Week 4: Archive Week 1's Sunday full for long-term

Verification Requirements

Every backup in an incremental chain should be verified. A single corrupt backup breaks the entire chain. Verification should:

• Check file integrity (checksums)
• Attempt actual restore to test server
• Run validation queries against restored data
• Alert immediately on any failure

Common Incremental Backup Mistakes

Mistake 1: Not Testing Restores

Many teams create incremental backups but never test the full restore chain until disaster strikes. Regular restore testing is essential - automate it if possible.

Mistake 2: Losing the Base Backup

If you delete or lose your full backup, all incrementals become useless. Ensure full backups are protected and replicated to multiple locations.

Mistake 3: Wrong basedir Reference

Incremental backups must reference the correct basedir. Using the wrong previous backup breaks the chain. Automate this process to prevent human error.

Mistake 4: Ignoring Verification

Without verification, you won't know a backup is corrupt until you need it. By then it's too late. Every incremental backup should be verified before the next one starts.

Mistake 5: Indefinite Chains

Chains that grow forever accumulate risk. Eventually, one link will fail. Start fresh full backups periodically to limit chain length and isolate failures.

How DBCalm Manages Incrementals

DBCalm automates the complexity of incremental backup management, eliminating common mistakes and ensuring reliable recovery.

Automated Chain Management

• Automatic full backup scheduling based on chain length
• Correct basedir tracking without manual intervention
• Immediate verification of every incremental backup
• Automatic rotation with configurable retention policies

15-Minute Incremental Backups

DBCalm creates incremental backups every 15 minutes, providing tight recovery points without excessive storage consumption. Each backup is verified immediately after creation.

Simplified Recovery

Instead of manually preparing and applying incrementals, DBCalm handles the entire prepare process automatically. Select a recovery point and the system handles the rest.

Frequently Asked Questions

Related Guides

• Physical vs Logical Backups
• Point-in-Time Recovery Guide
• MySQL Backup Best Practices