Incremental data migration from 1st DB to 2nd DB?

Alles, was MariaDB und MySQL betrifft, kann hier besprochen werden.

Incremental data migration from 1st DB to 2nd DB?

Postby nbk33r1 » 13. January 2019 05:21

Incremental data migration from 1st DB to 2nd DB? Here is the scenario... have a multiple table, indexed database, where information records are cross index to child records, fairly simple structure. For example, table 1 references records in table 2, 3, 4, etc. via a unique record ids in tables 2, 3, 4, etc. New records indexed by unique key are added to tables 2, 3, 4, etc. respectively then a new record added to table 1 as noted, this avoids data values being replicated in tables, since only indexes provide links between table 1 and the noted sub tables.

Typical log shipping would work, if the desired result is to keep server 1 and 2 server DBs synchronized. But that is not the requirement, what is needed is for 7 days of data in server 1, to be forwarded to server 2, since server 2 is the historical archive. But server 1 only needs relatively current data. So the logic of the need or expectation of function is:

1) Select records (from table 1 and as needed, table 2, 3, 4, etc.) for the given 7 days, table 1 does have a epoch time data field value per record, so that could be used for the 7 day selection window, each sub table, i.e. 2, 3, 4, etc. also have epoch time data field values per record by the way.
2) Copy these records from server 1 DB to server 2 DB
3) Delete these records from server 1 DB
4) Repeat sequence each week

I figure a stored procedure with a time/date trigger would be first step for #1 as noted above, to get the 'data set' as noted above. But can't seem to find any examples of how this might be done via Google, maybe I am looking or searching incorrectly? Someone has to have a similar need somewhere or somehow, right? Once I have the data set, would be simple to just delete the records in server 1 DB, after doing a limited export from server 1 DB and import into server 2 DB, this I believe I know how to do in theory, have yet to write any code or scripting for it as yet.

Any thoughts or suggestions welcome, thanks!
Posts: 1
Joined: 13. January 2019 04:55
XAMPP version: NA
Operating System: Debian 9

Return to MariaDB - MySQL

Who is online

Users browsing this forum: No registered users and 3 guests