Dual Master and Partitioned Data

[Tobin Harris]

Hi,

I have a small web spider application that sits on one server (Server A). We'd like to introduce a new server to host a web application (Server B), with it's own MySql database. There's only one database I need to replicate between servers. The scenario is like this...

Server A is very busy with reads and updates. It has 2 large InnoDB tables containing data that Server B needs a fairly up-to-date copy of.

Server B will be a web server, and won't be that busy. It has 12 small tables containing data that Server A needs a fairly up to date copy of.

There is one table that both servers will update. This is fairly clean - in that Server A will update one column of this table, and Server B will update the rest.

So, the flow of data between servers, which are on the same network, looks something like this...


A B
t1 ->
t2 ->
t3 <-
...
t15 <-
t16 <->


Now... to my question. I figured that I could implement this strategy by using the Dual Masters topology. As described, each master "owns" a mutually exclusive subset of tables in the database, which means the partitioning is fairly clean. Does this sound like a reasonable approach?

I'm new to replication, and fairly new to MySql, so any pointers on getting this set up working would be very much appreciated. For example...

- Will I need to set up appropriate "replicate-do-db" and "replicate-do-table" settings for each server, so that they know which tables they should subscribe to and which ones they should publish? Will these settings be enough to control "who pushes/pulls which tables"?

- Will I actually be configuring each server as a MASTER and a SLAVE, or will both just be MASTERS?

- Because Server A gets quite a few updates (perhaps 1-3 inserts per second), will replication bog down server B as it tries to keep up? I'm thinking not, since the reason why A is so busy is mainly due to queries, the inserts seem quite light weight.

- I plan on replicating between a Windows 2003 server and a Linux box (same MySql version), I'm hoping that this won't introduce problems?

Any help/thoughts much appreciated.

Tobin