0

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?

0

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.

  • It doesn't have to rely only on the built in MySQL replication (hence the trigger / PHP script ideas). It does have to be master-master though which I can't see being the hardest part. Both sides would need to translate the data into the format the other one is expecting. – SPKSmithy Sep 8 '17 at 5:55
  • We have updated a system which includes some changes to the database structure. The original idea was to take the system down, migrate the data then bring the new version up but now we need to run both versions together and all the data has to be synced. If a user makes a change in 1 version, the change must be visible in the other version almost instantly. – SPKSmithy Sep 8 '17 at 5:58
  • OK, maybe it will work. I modified my answer. But it scares me. – Rick James Sep 8 '17 at 13:24

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.