| SGreen@unimin.com 2006-02-21, 1:23 pm |
| --=_alternative 0061A5E58525711C_=
Content-Type: text/plain; charset="US-ASCII"
One problem with dual-master or multi-master replication is that you have
to be able to set and check a lock across all masters before performing a
schema change. How would you deal with this scenario using your "ALTER
TABLE" database dumps without such a lock?
Server A and B share a table X that has the following definition
CREATE TABLE X (
id int auto_increment
,name varchar(20) not null
,status tinyint
)
Simultaneously, separate changes are applied to table X on servers A and B
with the following statements:
SERVER A: ALTER TABLE X CHANGE status status tinyint unsigned;
SERVER B: ALTER TABLE X CHANGE status status int;
Without some way to serialize those changes you could possibly get stuck
in an endless loop.
1) Server A's sync process detects B's change and applies it. B's sync
process detects A's changes and applies them to itself.
2) Now both tables (A.X and B.X) are different again. Synchronization
attempts to match schemas again. Repeat step 1) until someone "wins".
Question: What should be the definition of X on both servers at that
point? Which change should have precedence?
</end scenario>
NDB (clustering) is the only MySQL database that supports distributed
locking and distributed transactions (making sure that at any one time all
replicas of the data are kept in sync across the cluster). SBR replication
takes care of the circular reference problem by tagging each DML statement
with the originating server. If a server detects that it is attempting to
process a statement that it already applied to itself, it quits and moves
on to the next statement. Clustering can use both SBR and RBR replication
(RBR = row-based replication or "row-by-row")
No, I do not know of any good system for two-way synching (other than NDB)
built on top of MySQL. Again, you haven't explained why your "synching"
plan is that much different than setting up "circular replication". In
circular replication server A is the master to B and B is the master of A.
This is a useful design if you can ensure that you can somehow ensure that
each server only issues "private" id values so that your records remain
unique throughout your enterprise. Schema changes must occur with great
care.
I have worked with several different replicating database servers (MySQL,
MS SQL server, Lotus Notes) and each have a different way of handling what
they call "replication conflicts". Those arise from scenarios very similar
to what I described above (changes occur to the same record on separate
servers between synchronization cycles). How do you plan to handle those?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"mwilliams" <mwilliams@mgreg.com> wrote on 02/21/2006 12:12:42 PM:
[color=darkred]
> Shawn,
>
> Thanks for the reply, but I think the thread has become much more
> dramatic than
> nececessary. Basically, I'm not looking for what has been ALTERed.
> I simply need the table
> creation data output in ALTER IGNORE (or whatever is appropriate)
> format so as to ensure
> table structure is the same before performing any INSERTS. I don't
> wish to DROP tables
> because that would then require reimporting all data. I simply want
> to write a tool to
> perform updates between multiple databases that keep them in two-way
> sync with their
> respective DBs (and even then, only specific tables) on a main
> server, both structure-wise
> and data-wise.
>
> The current systems don't use bin logs (*eyes rolling in back of
> head*) and we don't need
> replication, but true two-way syncing. Are there any truly quality
> two-way replication master
> techniques that you can recommend? I've been working with SJA and I
> like it pretty well. I'd
> like to write my own, similar program, but I think it might just
> work for now.
>
> Regards,
> Michael
>
> ---------- Original Message ----------------------------------
> From: SGreen@unimin.com
> Date: Tue, 21 Feb 2006 11:18:44 -0500
>
see[color=darkred]
differ[color=darkred
]
supports?[color=darkred]
[color=darkred]
to[color=darkred]
[color=darkred]
inserted[color=darkr
ed]
(data[color=darkred]
in[color=darkred]
schema[color=darkred
]
of[color=darkred]
schema,[color=darkre
d]
http://lists.mysql.com/mysql?unsub=awfief@gmail.com[color=darkred]
--=_alternative 0061A5E58525711C_=--
|