Restore data without deleting past data flag?

This may not be supported but I have a scenario where we are using xtrabackup to backup a production mysql 5.7 DB. I have restored the data and that seems to work well.

The issue is we want to run historical analytics on the restored database. We have daily partitions created on the production databases and we clean those up periodically and only keep a few months of data on the production databases.

Since xtraBackup is a copy when we delete data from prod it is also gone in the backup db when we restore.

Is there some sort of restore that can only restore new data but not delete any old data?

Thanks for the help,
Luke

1 Like

Hello @sysadminlm,
As you say, when you make a backup from prod, the data is gone from the backup as well. What you need to do is create another MySQL server and use that as your archival database. This is quite typical. Use pt-archiver to copy the data from the production table to this archive server just before you drop the partitions. This new server does not need to contain every table as in production; only those that you want to long-term archive.

1 Like

Thank you, I have looked at pt-archiver very briefly. I will look at it to see if I can have it to work on the restored db. The prod database has very limited access and bandwidth restrictions.

Thank you,

Luke

1 Like