AnswerBun.com

Replicate data between MySQL servers with a different structure

Database Administrators Asked by SPKSmithy on November 28, 2020

As part of a system upgrade I need to sync a database between 2 different servers (master-master). The catch is that the structure is slightly different between the 2.

Most of the differences are:-

  • Columns that exist in 1 but not the other and need populating based on other fields/tables
  • Type changes where some integer fields are now enums with text values that will need mapping from a static list
  • Multiple tables have been merged into 1

So far I’ve been thinking about setting triggers up which would catch all INSERT/UPDATE/DELETE statements and insert them into a “queue” table. A PHP script would then convert the data and write the changes to the other server.

All queries in the system are executed through a central database object so I could potentially add some code to parse each query, create a new query that works with the other server and execute both queries together.

There’s also the possibility to only use the new database and use query parsing code to re-format the data on each read/write to make it compatible with the old system.

Data has to be copied both ways (master-master) and it has to be as close to realtime as possible. Has anyone got any better suggestions or advice on how to do this?

One Answer

Not possible.

Master-Master allows writing to both, and, hence, provides no way to make changes during the replication, at least not bi-directionally.

Master-Slave does allow limited flexibility, such as different engine, different indexes, and, in a few cases, different datatypes. However, you could lose data. And it could fail depending on whether you are using binlog_format = ROW versus STATEMENT. ROW is less forgiving.

Would you like to explain the need you have? Perhaps we can devise a workaround.

OK, maybe it is possible with Triggers, since they don't have to be the same on both Master and Slave. (M-M is M-S in both directions.) So, if you can devise a pair of triggers that completely modify the table(s) at the time of writes, and if selects don't stumble over extra/missing columns/datatypes/tables, then maybe you could do what you want.

Answered by Rick James on November 28, 2020

Add your own answers!

Related Questions

Read after write in Galera

3  Asked on December 4, 2021

   

Generate SQL Table DDL from a View

2  Asked on December 4, 2021 by user162241

       

MySQL taking up 400% CPU

2  Asked on December 4, 2021 by littlelebowski

         

No output when use pymongo watch()

1  Asked on December 2, 2021 by red-black

   

changing the connection string in SQL Server

1  Asked on November 30, 2021 by teja

   

SSIS Excel Import – First row getting skipped

1  Asked on November 30, 2021 by mithun-john-jacob

     

Get if field has max value and display value

2  Asked on November 30, 2021 by carlos-alberto-martinez-martin

   

Ask a Question

Get help from others!

© 2022 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP, SolveDir