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.

  • 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 '16 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 '16 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 '16 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 '16 at 17:14
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

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.