List:General Discussion« Previous MessageNext Message »
From:mwilliams Date:February 21 2006 5:12pm
Subject:Re: (mysqldump) Serial output. . .?
View as plain text  
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
>> 
>
>
Thread
(mysqldump) Serial output. . .?mwilliams16 Feb
  • RE: (mysqldump) Serial output. . .?Dathan V. Pattishall16 Feb
  • Re: (mysqldump) Serial output. . .?sheeri kritzer20 Feb
RE: (mysqldump) Serial output. . .?mwilliams16 Feb
  • RE: (mysqldump) Serial output. . .?SGreen16 Feb
RE: (mysqldump) Serial output. . .?mwilliams16 Feb
RE: (mysqldump) Serial output. . .?SST - Adelaide)16 Feb
RE: (mysqldump) Serial output. . .?Dirk Bremer16 Feb
RE: (mysqldump) Serial output. . .?mwilliams17 Feb
Re: (mysqldump) Serial output. . .?mwilliams20 Feb
  • Re: (mysqldump) Serial output. . .?SGreen21 Feb
Re: (mysqldump) Serial output. . .?mwilliams21 Feb
  • Re: (mysqldump) Serial output. . .?SGreen21 Feb
Re: (mysqldump) Serial output. . .?mwilliams21 Feb
  • Re: (mysqldump) Serial output. . .?SGreen21 Feb
Re: (mysqldump) Serial output. . .?mwilliams21 Feb