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?



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:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.