I have a legacy MySQL Database (A), and a new reviewed structure for MySQL data base (B). Problem number one is that Database has to be alive and keeps receiving data from legacy apps.
What I need is to constantly move data from old data structure to the new one. Moving is not 1:1 but it is subject to data transformation.
Until now I have realised a system that have both DB (A & B) under the same host realising MySQL replication for DB (A) (readonly). And then performing a Replace select to DB (B) for the updated new records. I keep track of new edits having a timestamp on update current_timestamp column on key tables subject of data transfer.
This approach is working but it's very laborious and takes a lot of time and resources to synch. And also couples the two DB. I have the issue that the legacy DB have to stay on the same host of the new for performing replace...select sql scripts.
Do you have any better idea on how to solve a problem like this? (I'm thinking to use CSV files as medium for synch but I'm not sure how much will improve the situation)
Thanks for any advice.