Best practice for data archiving

Question

I am trying to figure out the best practice for data archiving. I have 1 pair of Master/Slave MariaDB servers where data is replicated from Master to slave. Since this is a very old app I am not able to modify yet, I can not keep data in some tables for more than a few days. I want to archive the data to a third server and then purge/delete data from the source.

On first run, I can dump all data but on the second run, I only want to copy the new additional data and keep existing on the archive server. So the archive will always have historical and current data. I tried extracting the SQL Statements from the bin log using time range but that does not seems to give proper results and the counts don't match.

What approach should I take? Data sync between MySQL/MariaDB servers without removing existing data on the target?

Thanks.

Answer 1

I don't think there is a "best practice" for archiving chunks of the data.

Some points to ponder:

• Are you limited in disk space? On the Primary server? On the Replica? If yes to both, then some form of compression would be a temporary solution. Or, as you suggest, moving chunks to a 3rd server.
• Will you need to restore the archived data? Sounds 'impossible' if the disk is too small. In any case, it sounds clumsy and impractical. Please state the requirements for "restoring".
• It is quite clumsy to require the user to go to a different server to get "historical" data. Is that part of your vision?
• PARTITION BY RANGE(TO_DAYS(..)) is a good way to break up the data into chunks by day (or week or whatever). It also facilitates splitting off 'old' chunks. (Cf: "transportable tablespace"). Please provide SHOW CREATE TABLE and whether you want to archive by date.
• Often, you don't really need all the details from "old" data. Some 'summary' of the data would suffice. Such a summary would be enough smaller to allow you to keep that data 'forever', while deleting (via DROP PARTITION) the old raw data.

More reading:

• Partitioning a time series
• Building and maintaining a Summary Table

Answer some of my questions; I may be able to further guide toward a usable solution.