1

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.

Improve this question
4
  • First question: how close to real-time does the 2nd DB have to be to the first? IE: is it ok if the data in B is a day behind the data in A? If so, can you just run the sync every night and call it good? Second question: would it be possible to rewrite the new "schema" as views on top of the old DB?
    – Becuzz
    Nov 7, 2016 at 16:03
  • Frequency synch now is 4 times a day (incremental synch) And a full synch daily. Using views is not an option I think: wouldn't it be too slow ?
    – koalaok
    Nov 7, 2016 at 16:15
  • Whether views would be too slow or not depends on your situation. If your views are really complicated they could be too slow. If you write them poorly they could be too slow. If you do really poor queries with the views, they could be too slow. They could also do perfectly fine. It all depends on what you are doing and what defines being "too slow". Test it and see.
    – Becuzz
    Nov 7, 2016 at 16:20
  • I'm doing ETL (data transformation) means JOINs and computations that would be better to be performed only if origin data has been change (synch) and not on every request (view)
    – koalaok
    Nov 7, 2016 at 17:14

1 Answer 1

Reset to default
1

Have you considered using one of the database replication methods?

There are several main approaches for MySQL database replication. The approach that can suite yout case is "Binlog replication".

This method (sometimes referred to as change data capture - CDC) utilizes MySQL’s binlog. MySQL’s binlog keeps an ordered log of every operation that was performed by the database.

This approach allows near real-time replication at scale and it doesn’t lock or affect the performance of the database.

It also supports deletions and table alterations and therefore enables exactly one-to-one replication.

Check this out for more details: https://support.alooma.com/hc/en-us/articles/214535985

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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