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" <[EMAIL PROTECTED]> wrote on 02/21/2006 12:12:42 PM: > 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: [EMAIL PROTECTED] > Date: Tue, 21 Feb 2006 11:18:44 -0500 > > >Michael, > > > >I have been following this thread from the beginning and I just don't see > >the practical difference between what you propose and the replication > >methods (SBR and RBR) already in place. How does what you propose differ > >from the SBR (statement -based replication) that MySQL already supports? > > > >Sorry if I am being dense but don't your source and destination schemas > >need to stay in synch in order for the changes in one table to be able to > >apply to the other? Isn't that why you are worried about capturing your > >schemas as ALTER TABLE statements? With SBR, each time a table is > >altered on the replication master, that ALTER TABLE statement is inserted > >in the binlog so that the change propagates to the slaves. The DML (data > >modification language) statements that follow the ALTER TABLE statement in > >the binlog won't fail because they will be applied to the correct schema > >on the slave. > > > >Again, my sincere apologies for missing the difference in the purpose of > >what you are trying to do. > > > >Respectfully, > > > >Shawn Green > >Database Administrator > >Unimin Corporation - Spruce Pine > > > > > >"mwilliams" <[EMAIL PROTECTED]> wrote on 02/20/2006 01:36:18 PM: > > > >> Sheeri, > >> > >> Thanks very much for the reply. However, that is not what I'm > >> looking for. I don't want the > >> tables that *have been* altered. I want CREATE TABLE statements > >> output in the ALTER TABLE > >> format for re-creation on another system (clean or otherwise). > >> > >> Regards, > >> Michael > >> ---------- Original Message ---------------------------------- > >> From: "sheeri kritzer" <[EMAIL PROTECTED]> > >> Date: Mon, 20 Feb 2006 12:22:55 -0500 > >> > >> >mysqldump takes a table or database and "dumps" it -- current schema, > >> >current data. You won't get alter tables. > >> > > >> >What you want is something that will show all the alter statements. > >> >You can run something like this on unix: > >> > > >> >tail -f binlog* | grep ALTER > alter.sql > >> > > >> >and then the alter.sql text file will always have the alter > >> >statements. The binary log captures the alter statements. > >> > > >> >Or, you could create an 'alteration' table with a text field and > >> >timestamp, and have a trigger copy the alter statement to the > >> >alteration table. > >> > > >> >But mysqldump is the wrong solution, because it only dumps "now". > >> > > >> >hope this helps! > >> >-Sheeri > >> > > >> >On 2/16/06, mwilliams <[EMAIL PROTECTED]> wrote: > >> >> All, > >> >> > >> >> I'm looking to output every piece of data from the database line > >> by line. Is there any > >> >> methody by which 'mysqldump' can output the following?: > >> >> > >> >> > >> >> use MY_DATABASE; > >> >> > >> >> CREATE TABLE IF NOT EXISTS MY_TABLE; > >> >> > >> >> ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] > >> >> ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] > >> >> ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] > >> >> ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] > >> >> ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] > >> >> ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] > >> >> ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] > >> >> > >> >> INSERT IGNORE . . . . > >> >> INSERT IGNORE . . . . > >> >> INSERT IGNORE . . . . > >> >> INSERT IGNORE . . . . > >> >> INSERT IGNORE . . . . > >> >> INSERT IGNORE . . . . > >> >> INSERT IGNORE . . . . > >> >> > >> >> > >> >> > >> >> The most important of the features above are the ability to > >> CREATE a table only if it > >> doesn't > >> >> exist (I never want to drop because the same script will be used > >> for syncing) and the > >> ability to > >> >> have 'mysqldump' be "smart" and output ALTER IGNORE statements. > >> Any asistance would > >> be > >> >> greatly appreciated. > >> >> > >> >> Regards, > >> >> Michael > >> >> > >> >> -- > >> >> 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] > >> > > > >
