How can I perform incremental or differential backups on a single schema?

MySQL & MariaDB Percona XtraBackup
1

I’m currently using rdiff-backup and mysqldump to create incremental backups of a single schema. I need to be able to backup and restore various schemas in this database independently.

From what I’ve read, backups of a single schema are referred to as “partial backups” and incremental backups cannot be used with partial backups. Is this true? Is there any to accomplish what I want with XtraBackup?

1 Like
2

Hi Billynoah,

With xtrabackup you can take incremental and partial backups.

For incremental: Incremental Backup - Percona XtraBackup
for partial backups: Partial Backups

Check this note from the official doc:


Incremental backups simply read the pages and compare their LSN to the last backup’s LSN. You still need a full backup to recover the incremental changes, however; without a full backup to act as a base, the incremental backups are useless.

So, the incremental backup will only contain and apply the changes on the subset of partially backed up tables.

Last, a partial backup must be restored one table at a time as explained in https://www.percona.com/doc/percona-xtrabackup/2.3/innobackupex/restoring_individual_tables_ibk.html
Otherwise, restoring the partial backup will overwrite the mysql internal table dictionary. Restoring each individual table to MySQL will require MySQL to read the entire .ibd file

Usually taking and restoring a physical backup should be faster than a logical backup.You can test with the commands and examples from the official doc to check how faster is for your database and workload. Make sure you check the examples with the correct version of xtrabackup you are using (i.e xtrabackup 2.4 for 5.7 and 5.6, xtrabackup 8.0 for 8.0, mariabackup for mariaDB)

Regards
1 Like
3

Hi - I’m not sure you answered my question. Can I perform incremental backups on a partial backup? I’ve read the entire section of the docs regarding partial backups and I don’t see any mention of a way to to incrementals with partial backups. If I am wrong, can you please provide an example of how that would work?

2 Likes