List:General Discussion« Previous MessageNext Message »
From:SGreen Date:February 28 2005 5:11pm
Subject:Re: two-way replication
View as plain text  
"Chris Knipe" <savage@stripped> wrote on 02/28/2005 11:53:14 AM:

> Hi,
> 
> Is two-way replication possible with MySQL 5.x?  Any good sites / docs 
> describing this type of setup?
> 
> --
> Chris. 
> 
> 

By "two-way" replication, do you mean "changes to either database are 
synchronized to the other"? This is a dual-master situation and not yet 
supported by MySQL.  The problem is with cross-server locking. Right now 
(even with v5.0) MySQL does not know to lock a record on your "other" 
server to prevent someone over "there" from changing that record while you 
are editing it on "this" server. Same goes for inserts (auto_increment 
values are not coordinated) and deletes.

Now, I have been monitoring the development lists on the MySQL Cluster and 
MySQL Federated lines and both are working on inter-server coordination 
but I have no idea if either of those products will fit your need.

As of today, MySQL does support 1-way, and chained replication. There is 
no rule that prevents you from creating circular replication by making 
each server the "child" of the other. The danger with circular replication 
is that you need to ensure (by application-level logic, data partitioning, 
or any of other techniques) that insert collisions do not occur (no two 
new records get the same primary key from different servers) and that you 
can gracefully handle simultaneous updates (as they are not prevented at 
the server level). 

Search this list's and the other lists' archives (http://lists.mysql.com/) 
for some pretty clever workarounds to help stabilize circular replication. 
During your searches, you will find links to several books that are 
online, I highly recommend you read ALL of those links. 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Thread
two-way replicationChris Knipe28 Feb
  • Re: two-way replicationSGreen28 Feb
    • Re: two-way replicationChris Knipe28 Feb
  • Re: two-way replicationGleb Paharenko1 Mar