MySQL Replication: Combination of Master & Slave on 2 servers

Question

I have a situation here that will have 2 MySQL servers linked over a WAN. Each location (lets call them Location X and Location Y) will need access to three databases (Lets call them A, B and C.

Location X needs to use Database A as their main database, and database B as a 'lookup' read only database.

Location Y needs to use Database B as their main database, and database A as a 'lookup' read only database.

BOTH locations need read/write access to a replicated database C. (This database contains no auto incrementing columns etc. - fairly simple logging tables).

In essence, I need a combination of master-slave AND master-master replication happening:

X      Y
--------
A  --> A
B <--  B
C <--> C

Is this feasible to set up on MySQL?

Answer 1

MySQLD-Multi Is a package that can help you accomplish this. Individual MySQL databases can only have one master. If you setup A series mastered in one direction, and your B series mastered in the opposite direction, they would need to run on their own IP or port numbers. Likewise for C.

Multimaster setup becomes arduous if you get data transmission errors, lose table space, or get struck by thundering herds and drop connections. You will likely end up in the un-enviable position of using a log-based recovery tool to re-sync them. Please consider that your application will want to very cautiously use its "write" privileged connection and rely heavily on read connections.

Splitting up MySQL instances in this manner allows for latency between applications to isolated better. In a multi-master situation, you commonly have to watch the latency and begin failing things out of your pool if they differ even by 1 second of latency. If you truly need MM for your C-series, serioiusly consider chaining read-only slaves on each end for your C-series to minimize latency on the write-masters.

Answer 2

I'd suggest using Percona XtraDB Cluster.

PXC is a MySQL derivative server (so everything about using it will seem familiar except for the replication part). It supports synchronous bidirectional replication over a WAN, so both site X and Y can write to its main database, which will then be replicated to the other site. PXC even supports writes to a database on both sites, which satisfies your requirement for database C.

Percona XtraDB Cluster is free and open source software, like all Percona products.
Disclaimer: I worked for Percona 2010-2014.