Setting Up MySQL Xtrabackup Incremental Backups the Right Way

Protect your data with MySQL Xtrabackup incremental backups. Learn full and incremental backup commands and how to restore safely. Our MySQL Support Team is always here to help you.

Setting Up MySQL Xtrabackup Incremental Backups the Right Way

Backing up databases is one of those tasks no one can afford to get wrong. For MySQL users, mysql xtrabackup incremental backups offer a reliable way to capture only the changes since the last backup instead of duplicating everything each time. This not only saves time but also reduces storage usage. Let’s break down exactly how it works and how you can put it into practice.

How Incremental Backups Work

Incremental backups depend on the log sequence number (LSN). Each InnoDB page carries its own LSN, showing how recently it changed. Xtrabackup uses this to determine what needs to be included in each backup, similar to how buffered queries help manage memory and efficiency in MySQL.

Creating the First Full Backup

Everything begins with a full backup. Use the following command:

$ xtrabackup --backup --target-dir=/data/backups/base

After the backup completes, a file called xtrabackup_checkpoints will appear inside the directory. It looks like this:

backup_type = full-backuped

from_lsn = 0

to_lsn = 1626007

last_lsn = 1626007

compact = 0

recover_binlog_info = 1

Making the First Incremental Backup

Once the full backup is done, you can capture only the changes since that point. Run:

$ xtrabackup --backup --target-dir=/data/backups/inc1 \

--incremental-basedir=/data/backups/base

Inside /data/backups/inc1/, you’ll find .delta files representing the changes. The checkpoints will look like this:

backup_type = incremental

from_lsn = 1626007

to_lsn = 4124244

last_lsn = 4124244

compact = 0

recover_binlog_info = 1

Notice that from_lsn matches the to_lsn of the previous backup.

Adding Another Incremental Backup

You can continue the chain with another incremental:

$ xtrabackup --backup --target-dir=/data/backups/inc2 \

--incremental-basedir=/data/backups/inc1

Checkpoint file content:

backup_type = incremental

from_lsn = 4124244

to_lsn = 6938371

last_lsn = 7110572

compact = 0

recover_binlog_info = 1

Preparing the Backups

Now comes the preparation phase. You start with the full backup:

$ xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base

Then apply the first incremental:

$ xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base \

--incremental-dir=/data/backups/inc1

Finally, apply the second incremental:

$ xtrabackup --prepare --target-dir=/data/backups/base \

--incremental-dir=/data/backups/inc2

Restoring the Data

After preparation, the data can be restored. First stop MySQL:

$ systemctl stop mysql

Then restore using one of these methods:

$ xtrabackup --copy-back --target-dir=/data/backups/

or

$ xtrabackup --move-back --target-dir=/data/backups/

or with rsync:

$ rsync -avrP /data/backup/ /var/lib/mysql/

Configuration Note

Xtrabackup reads /etc/mysql/my.cnf by default. You can also set parameters there:

[xtrabackup]

target_dir = /data/backups/mysql/

[If needed, Our team is available 24/7 for additional assistance.]

Conclusion

With mysql xtrabackup incremental backups, you get efficiency and reliability without extra complexity. Start with a full backup, apply incrementals, prepare them correctly, and restore confidently. By following the commands above, you’ll always have recoverable backups ready when you need them most. Also, if you’re working across platforms, you can convert SQL Server to MySQL with Workbench.