0

Our company needs to create an application that operates in all parts of the world. We are looking to have three servers (one in Europe, one in Asia and one in America). I'm looking to use multi-master synchronisation using Tungesten on MySQL with row-based replication. So far, I've tested this on three local VMs with success.

However, what happens if Database A disconnects with Database B and Database C still connected to eachother. Let's say Database A updates table Dogs and sets Dog_name = Rover where dog_id = 34, while Database B (and C) update to set dog_name = Fido where dog_id = 34. The situation can arise where B and C take the value Rover, while A takes the value Fido. I've tested this to confirm it does happen.

Tungsten allows you to generate a hash key of the databases and compare them. It generates a hash on one of the masters and sends it to the slaves which computer their own hash. This allows you to see if there is a synchronisation mismatch. However, I'm concerned in a big database this could take a lot of time and consume system resources.

Another option would be to timestamp all tables and use a script file to export all values updated say in the last hour to a central server which generates a hash keys, and if the hash keys are different (accross servers), compares the difference and mails the difference to the sysadmin to correct. The third option, would be to use Nagios to monitor and when the connection is restored, to run a hash file check.

My questions are as follows: 1. What are the risks of the above scenario occuring? 2. If anyone has any experience of this, is there a more optimal way of checking? 3. Are there any other major risks in a multi-master scenario to be aware of?

Ultimately, I plan to do extensive testing before implementing any solution like this, including breaking as much as possible to see what happens.

Many thanks.

1

I don't have experience of your exact situation, but could you use:

auto_increment_increment=3 auto_increment_offset=1 [then 2 and then 3]

on your different masters, so that each one generates a different set of id's.

e.g.

server 1: 1, 4, 7

server 2: 2, 5, 8

server 3: 3, 6, 9

  • Thanks, I've already implemented that. It definitely alleviates the risk of two primary keys. However, that doesn't solve the problem of updates to a table. One option we discussed in design is to never use update and only use insert statements, however that option seems inherently clunky and unwieldy to me. – Tim Ellis-Smith Nov 26 '14 at 10:39

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.