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] >> > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
