I think the message they were trying to convey in the answer is "two-way replication will result in data corruption and therefore should not be used." Consider a table with an AUTO_INCREMENT id field. If two different inserts are done simultaneously, one on each master, both will get the same id number.
For example: client A connects to master 1 and does "INSERT INTO TABLE VALUES (NULL, 7, 8, 9)" client B connects to master 2 and does "INSERT INTO TABLE VALUES (NULL, 9, 8, 7)" on master 1, the table looks like this: +---+---+---+---+ | 1 | 7 | 8 | 9 | +---+---+---+---+ on master 2, the table looks like this: +---+---+---+---+ | 1 | 9 | 8 | 7 | +---+---+---+---+ Now, the insert from master 1 gets replicated to master 2 -- but master 2 gives this insert a different AUTO_INCREMENT number. Master 2 now looks like this: +---+---+---+---+ | 1 | 9 | 8 | 7 | | 2 | 7 | 8 | 9 | +---+---+---+---+ And finally, the insert from master 2 gets replicated to master 1, again with different results. Master 1 now looks like this: +---+---+---+---+ | 1 | 7 | 8 | 9 | | 2 | 9 | 8 | 7 | +---+---+---+---+ As you can see, you have two tables which are supposedly replicated, but neither matches the other. It may be technically possible to set up MySQL to make it appear to have a master-master replication configuration, but such a configuration would not be logically valid. --- Emi Nelson <[EMAIL PROTECTED]> wrote: > Hello, > > I have set up a master/slave replication environment using > 4.0.13 but would like to know if there is a way to set up > master/master replication between two databases, both able > to be inserted/updated/deleted, tables created, etc., with > the changes on both databases propagated to each other. I > see one comment in the documentation on two way replication: > > Q: What issues should I be aware of when setting up two-way > replication? > > A: MySQL replication currently does not support any locking > protocol between master and slave to guarantee the atomicity > of a distributed (cross-server) update. In other words, it > is possible for client A to make an update to co-master 1, > and in the meantime, before it propagates to co-master 2, > client B could make an update to co-master 2 that will make > the update of client A work differently than it did on > co-master 1. Thus when the update of client A will make it > to co-master 2, it will produce tables that will be > different from what you have on co-master 1, even after all > the updates from co-master 2 have also propagated. So you > should not co-chain two servers in a two-way replication > relationship, unless you are sure that you updates can > safely happen in any order, or unless you take care of > mis-ordered updates somehow in the client code. > > You must also realise that two-way replication actually does > not improve performance very much, if at all, as far as > updates are concerned. Both servers need to do the same > amount of updates each, as you would have one server do. The > only difference is that there will be a little less lock > contention, because the updates originating on another > server will be serialised in one slave thread. This benefit, > though, might be offset by network delays. > > ------ > > But there is no further info on how to set it up. Is > bidrectional set up by using the log-slave-updates > parameter on the slave and point it to the 'master'? It's > just kind of vague :-( > > Thanks in advance, > Emi > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
