Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

A mysql replication node can either be a master or a slave - it can't be both. But there's nothing to stop you from only sending your writes to some of the nodes (but do set up your autoincrements so that any node can process inserts/updates).

MySQL community doesn’t encourages multiple write masters

IME, master-master replication with updates on both works fine. You just have to be aware that geogpraphically split db operations won't necessarily happen in the same sequence (i.e. never delete data, avoid changing primary/foreign keys where its practical to do so). But for a bulletin board / blog type system this should pose no issues.

For conflict resolution we will control it on code level. e.g. to avoid same username and other unique constraint

These scenarios should be very rare, and predictable - use a spanning mutex to lock all nodes for that data value.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.