Two master databases for two separate apps 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 Europe datacenter master server first and needs to come back on Asia data center database servers slaves to synchronize data and on Europe 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.

Thank you.



Thank you for the suggestions.
But still many questions left.

Is there any automated tool available to resolve conflicts and solve replication issues between both masters ?

As long as database data concerns, we have to synchronize both datacenter data to each other (Asia &amp; Europe). We can't restrict data to it's related datacenter only.
MULTIMASTER SETUP
I would recommend a multimaster setup only with the idea that you restrict Asia's data to one master, and Europe's data to the other master. As for the shared databases, make sure auto_increment_increment and auto_increment_offset are in full use. In addition, make sure you only write Asia's data to the shared databases at the Asian DC and likewise with Europe.
STORAGE ENGINE CONSIDERATIONS
If there is no specfic reason to keep MyISAM (such as FULLTEXT indexing or heavy volume reads), I would recommend converting everything to InnoDB. That way, if a server crashes, then crash recovery can bring databases to a consistent state. With MyISAM, tables can be left in a crashed state and repairing those tables may result in the disappearence of one or more rows of data.
HIGH AVAILABILITY
You should strengthened your HA Setup by implementing DRBD. DRBD should be setup as at each DC. What are the benefits?

You maintain a disk-level replica of the mysql data within both data centers
You can have automatic failover within each data center
You only worry about MySQL Replication between DCs

REPLICATION
To minimize data loss with MySQL Replication over geographic distance, I would recommend using smaller binary logs. By default, a binary log/relaylog is 1G (max_binlog_size and max_relay_log_size). You can make these values much smaller. I got this idea from PostgreSQL because WAL files are 16M by default. This creates more log files, but those log files will be closed and complete faster on the acting slave. If you are not using it, you should upgrade to MySQL 5.5 because it has Semisynchronous Replication. It can be tuned to detect heartbeat timeouts with some granularity.
I wrote about some of these concepts in an earlier post back on March 29, 2011.