We have two different websites one is hosted in asia datacenter called it asiadata.com and other site is hosted in europe datacenter called it europedata.com. Currently we have only one master database server which is hosted in europe datacenter and it’s responsible for all writes from both websites and to replicate on underlying slaves on europe and asia datacenter.Also both apps writes on common database.
We have complains from asiadata.com users about site slow browsing and posting and things are not visible immediately on asiadata.com. After digging we found that database is culprit because it needs to write on eurpose datacenter master server first and needs to come back on asia data center database servers slaves to synchronize data and on eurpose datacenter slaves as well. All this process takes time because of geographical distance between asia and europe and because of network latency.
Now we decided to resolve this issue by having multiple write servers (masters) each on Asia and Europe datacenter so each site will write data locally and both masters will also be slaves of each other. And each master is responsible to replicate data on it’s own datacenter only on underlying slaves. For conflict resolution we will control it on code level. e.g. to avoid same username and other unique constraint. Hopefully, this will resolve split brain situation and this will make sure neither master broke.
Can someone please let me know if i missed anything on multiple write masters or any scenario on database level which break up replication ? I thought this will be the best way to improve my apps performance but along with that i am afraid to built this solution because MySQL community doesn’t encourages multiple write masters. Also 90% of the database tables are MyISAM and replication is single threaded. I am afraid both masters might will not produce the best performance because of table level locking as both masters will also be slaves at the same time.
Also is there any automated tool available to resolve conflicts and solve replication issues between both masters ?
Overall, please comment multi master be the best solution or is there any better alternatives.