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@stripped
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" <mwilliams@stripped> 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" <awfief@stripped>
>> 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 <mwilliams@stripped> 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/mysql?unsub=1
>> >>
>> >>
>> >
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>>
>
>
