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] >
