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.