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?