Few questions regarding full and incremental backups/storage

MySQL & MariaDB Percona XtraBackup
1

Hi all, few questions regarding XtraBackup.

  1. I think(?) I read that when doing full backups I can stream the files to S3?
  2. When doing incremental backups, do I need to have the base dir locally available?
  3. What is the purpose of --prepare as it reads as though I can pass --prepare into the backup routine?
2

Hi @alturic

  1. I think(?) I read that when doing full backups I can stream the files to S3?

That’s right, please refer to: Use the xbcloud binary with Amazon S3

  1. When doing incremental backups, do I need to have the base dir locally available?

Edit: It’s not required, the only information required for creating incremental backups is the LSN of the previous full, or previous incremental.
You can pass that information using --incremental-lsn=LSN (Docs) . You can retrieve that info from the xtrabackup_checkpoints file on the previous backup.

Also, XtraBackup has a feature that allows you to store backup LSN history on the mysql server itself, and create incrementals using that information. Please refer to this section.

  1. What is the purpose of --prepare as it reads as though I can pass --prepare into the backup routine?

I’m not sure I understood this last question, but if you’re working with incremental backups you should use the --apply-log-only for each incremental, as using only --prepare will make the next incremental backups useless.
Refer to this section for more details about that.

Best,
Mauricio.

3

@Mauricio_Cacho So there is no way to perform incremental backups without having, ultimately, a duplicate amount of storage space available to retain the last full backup?

Is that not what --extra-lsn and/or --incremental-lsn would “solve”, not needing to have the full backup directory?

4

well, incremental backup the files that have changed since the previous incremental or full backup. So you need full backup space and then need space for incremental, let say you want to take weekly full and then rest incrementally through the week, so you need to a good the space, as you need to keep the full backup as well. But it better test it and check how much full backup takes and how much space is required for incremental backup

5

Interesting. I guess in my case I’ll just do full backups daily so I can move the files off-server and not have to have duplicate the amount of disk required.

I really just want to be 100% clear. Our database is roughly 10TB. If I wanted to do a full backup weekly (then store it to AWS), and incremental backups daily (also storing to AWS), I would need to have a minimum of 20 TB of disk space, realistically? I know there’s compression options and such, but if we were talking straight backups, no compression. Just want to be clear as it seems crazy to need to have double the disk space just to have the full backup ‘sit there’.

6

Hi @alturic .

I’ve edited my firs answer, please check the answer to the second question, as it has some great news for your requirement.

I’m sorry for my previous answer stating it was required to have the full backup.

Feel free to let us know if you still have questions after checking the documents shared in there as well.

Best,
Mauricio.