Go back in time after restoring latest incremental backup

MySQL & MariaDB
1

Hello,
I’m currently in a situation where I need to backup a 250GB MySQL mixed InnoDB and MyISAM Database. I was wondering if it was the right choice to proceed like this:
Sunday - Full Backup
Monday till Saturday - Incremental Backups

After a while of testing we thought it was the optimal solution size-wise, but the reason we are not proceeding to put it in production is the following:
Let’s take 1 full and 6 incrementals as an example
In case we need to restore the DB as of day 4, but for some reasons wrongly restore day 5, would it be possible to go back in some ways?
We tried creating a mirror of the incrementals and prepare the full with them - not working
creating a mirror of the full and prepare it with the existing incremental - not working
Copy all the incrementals and the full and use them as a backup of backups - working

The problem with the last choice is of course the size occupied with all these backups.

Any help will be highly appreciated.

Thanks in advance to all

2

No, you would have to start over the restore process.

Please provide terminal output of commands used and result.

If you are concerned with space, ensure you are using --compress option as this will drastically reduce the storage required.

3

Hello Matthew,
thank you for your answer

What do you exactly mean by that? If I prepare the full backup with incremental5 how can I start back the restore process? I cannot prepare it with incremental1 because of the error:

This target seems to be already prepared with --apply-log-only.
This incremental backup seems not to be proper for the target.
Check ‘to_lsn’ of the target and ‘from_lsn’ of the incremental.

Thanks a lot for the --compress I didn’t know this option!

Thanks,
Andrea

4

You must prepare the incrementals in order. Example:

  • prepare full
  • prepare 1st incr (merges into the full)
  • prepare 2nd incr (merges into the full+1st)
  • etc

Thus, you cannot simply “unmerge” a prepared incremental. You must start over if you realize that you’ve prepared too many incrementals.

Make a copy of your full backup because the preapre’s of incrementals will modify it!

5

I’ve tried to create a copy of the full backup, the problem is that once an incremental have been merged into the full, it would not be possible to use it again on the copy.

So I think it is not possible to “go back” once I’ve restored the wrong full with the wrong incremental merged, right?

Could you explain better what you mean by “start over”?

Thanks and sorry if I’m not comprehending correctly.
Andrea

6

Correct. That’s why it’s important to make a copy of your full backup before you do any incremental merging.

Correct.

The steps I mentioned above; you have to start the whole prepare/merge process over starting with the full, then the incrementals.