Eric, you are in the same boat I am in. I also have satellite offices
across WAN links that should keep functioning even if the WAN is down.
My , yet to be tested due to "office politics", workaround design was to
have each office (satellite and main) have their own "mini-master" that
replicated to a central location. At the central location I will be running
a daemon or cron task (the actual design of which is on hold) to collect
the new records/updates and merge them into a new "mega-master" database.
The megamaster will contain the combined records of all of my locations.
Each remote office will get a read-only replica of the megamaster (thus
enforcing one-way replication)
This way, I can see what data collisions I have in a central location (the
hub server) and deal with them before letting any changes head back out to
the satellites. The disadvantage of this is that each location will have 2
copies of "their" data (one read-write and one read-only). The Advantage is
that every location will eventually have a copy of the data that makes it
into the megamaster. I guess I could create a different "mega-master" for
each remote office so that it only contained the records of the OTHER
offices but that's an awful lot of duplicated data sitting in my hub
server. Anyway some of that data is summary data that won't exist in the
"mini-master" for each site and that data would also have to make it into
the mega-master. I think it will be much less hassle to just let all of the
data flow to each remote.
I do not have any code written yet for my insert/update daemon. As I
mentioned, I have "turf war" issues to settle before I will be allowed to
begin my data consolidation efforts.
I have seen scripts that will monitor a master server for activity and
switch a slave server to assume the master role. Even this solution runs a
risk as it may be possible with this kind of auto-promotion to interpret a
loss-of-communication as a "failure" yet still have both servers acting as
"masters" . This means that when communication is restored, both the old
master and the new master must stop what they are doing and work out
between the two of them what happened while they were out of contact and
decide which server will continue acting as the true master. Depending on
the length of time they were off-line with each other, you could have
several data collisions (tens of thousands?) to reconcile by hand or
script.
I will be more than happy to help as much as I can.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Robinson, Eric"
<[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>,
<[EMAIL PROTECTED]>
cc:
05/17/2004 02:40 Fax to:
PM Subject: RE: 1-Way or 2-Way
Replication?
Shawn, your answer is excellent, and I now understand why replication is
a touchy issue. But now I am worried that I will not be able to
accomplish what I had hoped.
I want to have a server at my corporate office and a server at my branch
office. Most of the time, all users at both locations would use the
master server at the corporate office.
Is it possible to set up replication in such a way that if the WAN link
goes down, the users at the branch office switch over to their local
server and continue working normally, including INSERT and UPDATE? If
so, what happens when the WAN link comes back up?
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Monday, May 17, 2004 11:12 AM
To: [EMAIL PROTECTED]
Subject: RE: 1-Way or 2-Way Replication?
<Robinson, Eric: 5/17/04 1048>
>there is no mechanism for propagating slave changes from the slave back
>up to the master... synchronization occurs *only* from master to slave
>(hence the terminology).
Then why do they call it 2-way replication? Is there such a thing as
master-to-master?
</Robinson, Eric>
Eric,
In order to declare a server a "master" server it must be able to accept
INSERTs and UPDATEs without corrupting your data integrity. If you want
to keep 2 "master" servers in sync, you must implement some form of
distributed locking. Distributed locking is what keeps both servers from
issuing the same ID number at the same time to two different records or
permitting simultaneous updates to the same record on two different
servers. Once you solve the issue of distributed transaction locking
you are now able to keep both sets of data intact. The entire
locking/communication cycle between two (or more) servers that ensures
BOTH data integrity AND consistency is what is mean by the term "2-way
replication".
MySQL does not support distributed locking (yet). Until then, your data
integrity is ensured ONLYif you allow inserts/updates on only 1 server
at a time and copy the changes as they happen to all other servers that
need copies of the data. If you have two servers, A and B, and your
applications are written so that server A is where all of the data
creation and changes occur then you can ensure that all new records will
have unique primary key values and that each primary key value
identifies the same data (you have achieved data consistency). Those new
records could be copied over to B through the built-in replication
system in near-real-time. This is the 1-way replication you have read so
much about. Each master server (like A) can "push" data to multiple
slaves (like B) but as of right now there is no way for separate MySQL
servers to communicate with each other so that each of them can accept
new or updated data while preventing loss of data consistency (2-way or
n-way replication).
I hope I helped you to understand that 2-way replication involves much
more than simple data copying from server to server and requires an
entire distributed transaction system in order to prevent data
collisions and corruptions. MySQL will have it in a future release but
it's ready for an initial alpha test, yet.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--
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]
