Well, not logically valid in the general sense (in that naive master-master replication can be self-destructive) but can be extremely useful when utilized knowing all the limitations and potential pitfalls.
On Sun, 2003-08-31 at 10:01, Douglas Granzow wrote: > 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]
