Now I have 5 Linux VMs up and running with mysql replication configured and tested – this is a single master setup.
I did some research around and found that dual/multiple masters is still not a good solution thinking of the potential conflicts. It seems auto increment will not be a big issue as if end user does not have direct mysql access, then they will not be able to change auto_increment_increment and auto_increment_offset. However, it is still possible that two or more masters get updates for the same record at same or close time, which will cause a tricky question – what should be the last status of the record? It’s not totally rely on when the client (of mysql) triggered the update, it also depends on how fast the replication goes.
Some people/application may not care about this too much – such as user update profile information, if it’s user him/herself edit the record from two browsers and hit submit at almost the same time, it’s up to him/her to take the risk. However, this is extrem dangerous if this is a transaction system, that something to do with money, so again, it’s up to developer to decide using dual/multiple masters or not. If the application itself can guarantee different clients won’t write to same record at the same or close time (or better be never), such as data are geo-related, then multiple master is still a good solution.
Anyway, since dual/multiple masters won’t give me any benifit (no performance gain, no improvement on reliability), so I will leave the test to the future. For now, I will just leave the replication there (well, will add some monitoring stuffs) and go with mysql proxy test.
Also