RE: Column level replication q?
Michael, It seemed to replicate just fine. At least in test. (5.1.22RC). I believe that we are using statement level replication which might be triggering the trigger on the slave node. I noticed that the triggers themselves also replicatated to the slaves, which is something I didn't expect. Gary From: Michael Dykman [mailto:[EMAIL PROTECTED] Sent: Thu 2/21/2008 8:06 AM To: mysql@lists.mysql.com Cc: Gary W. Smith; Baron Schwartz Subject: Re: Column level replication q? On Wed, Feb 20, 2008 at 10:48 AM, Gary W. Smith <[EMAIL PROTECTED]> wrote: > Well, I created the lookup table, created my two triggers (as nothing is ever > updated, just added or removed) and did a bulk one time load and it seems to > work. Now I just need to replicate that to the other server. I'll have to > find my easy button and press it a few times. > > In fact, on the other end, we don't even need to have the same table names. > So we can replicate it just like this with no problem. > > Thanks for the links, > I am curious: I thought that data written by triggers was not replicated, replicating instead the action to call the trigger. I seem to recall that several approaches to logically replicate triggers have been tried, where are we at these days? -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful.
Re: Column level replication q?
On Wed, Feb 20, 2008 at 10:48 AM, Gary W. Smith <[EMAIL PROTECTED]> wrote: > Well, I created the lookup table, created my two triggers (as nothing is ever > updated, just added or removed) and did a bulk one time load and it seems to > work. Now I just need to replicate that to the other server. I'll have to > find my easy button and press it a few times. > > In fact, on the other end, we don't even need to have the same table names. > So we can replicate it just like this with no problem. > > Thanks for the links, > I am curious: I thought that data written by triggers was not replicated, replicating instead the action to call the trigger. I seem to recall that several approaches to logically replicate triggers have been tried, where are we at these days? -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Column level replication q?
Well, I created the lookup table, created my two triggers (as nothing is ever updated, just added or removed) and did a bulk one time load and it seems to work. Now I just need to replicate that to the other server. I'll have to find my easy button and press it a few times. In fact, on the other end, we don't even need to have the same table names. So we can replicate it just like this with no problem. Thanks for the links, Gary From: Nanni Claudio [mailto:[EMAIL PROTECTED] Sent: Wed 2/20/2008 1:19 AM To: Gary W. Smith; mysql@lists.mysql.com Subject: R: Column level replication q? Of course I am talking about a materialized view, did you try? Here some useful links: http://forums.mysql.com/read.php?100,21746,21746#msg-21746 and from the reply in the same forum topic by Waheed Noor 09/10/2007 01:39PM you get this link: http://www.shinguz.ch/MySQL/mysql_mv.html Aloha! Claudio -Messaggio originale- Da: Gary W. Smith [mailto:[EMAIL PROTECTED] Inviato: mercoledì 20 febbraio 2008 1.18 A: mysql@lists.mysql.com Oggetto: Column level replication q? We need to replicate a table to a third part. The information in the table is pretty much public, with the exception of one column. Is it possible to replicate all of the tables with the exception of one column? What I was thinking was to replication it to a 2nd machine that will limit it to the tables we care about and then expose that slave machine as a secondary master. We are looking to do something like this MasterA -> SlaveA/MasterB -> SlaveC MasterA tableA (our machine) field1 field2 field3 field4 SlaveA/MasterB (our machine) field1 field2 field4 SlaveC (their machine) field1 field2 field4 We know we can limit the tables which they can use (which is why we have introducted SlaveA/MasterB so they will only be able to pull the tables we make avaiable to them. Our current method is database dumps but this is become impracticle due to size. Replication in testing works for what we want to do, we just have a problem with a single field. Any ideas on how to make this work? Gary Wayne Smith "Questo messaggio ed ogni suo allegato sono confidenziali e possono essere riservati o, comunque, protetti dall'essere diffusi. Se il ricevente non é il destinatario diretto del presente messaggio, é pregato di contattare l'originario mittente e di cancellare questo messaggio ed ogni suo allegato dal sistema di posta. Se il ricevente non é il destinatario diretto del presente messaggio, sono vietati l'uso, la riproduzione e la stampa di questo messaggio e di ogni suo allegato, nonché la diffusione del loro contenuto a qualsiasi altro soggetto" * "This message and any attachment are confidential and may be privileged or otherwise protected from disclosure. If you are not the intended recipient, please contact the sender and delete this message and any attachment from your system. If you are not the intended recipient you must not use, copy or print this message or attachment or disclose the contents to any other person."
RE: Column level replication q?
Claudio, I didn't think about that approach either. The triggers will be much simpler to implement on the primary servers, then pull it over with a table rewrite to the intermediate server, then allow that to replicate out just fine. I'll play around with it a little. The table has millions of rows, but the primary data in the table that I really care about is relationship keys, which should be small if we put just that data into a intermediate table. Thanks, Gary Wayne Smith From: Nanni Claudio [mailto:[EMAIL PROTECTED] Sent: Wed 2/20/2008 1:19 AM To: Gary W. Smith; mysql@lists.mysql.com Subject: R: Column level replication q? Of course I am talking about a materialized view, did you try? Here some useful links: http://forums.mysql.com/read.php?100,21746,21746#msg-21746 and from the reply in the same forum topic by Waheed Noor 09/10/2007 01:39PM you get this link: http://www.shinguz.ch/MySQL/mysql_mv.html Aloha! Claudio -Messaggio originale- Da: Gary W. Smith [mailto:[EMAIL PROTECTED] Inviato: mercoledì 20 febbraio 2008 1.18 A: mysql@lists.mysql.com Oggetto: Column level replication q? We need to replicate a table to a third part. The information in the table is pretty much public, with the exception of one column. Is it possible to replicate all of the tables with the exception of one column? What I was thinking was to replication it to a 2nd machine that will limit it to the tables we care about and then expose that slave machine as a secondary master. We are looking to do something like this MasterA -> SlaveA/MasterB -> SlaveC MasterA tableA (our machine) field1 field2 field3 field4 SlaveA/MasterB (our machine) field1 field2 field4 SlaveC (their machine) field1 field2 field4 We know we can limit the tables which they can use (which is why we have introducted SlaveA/MasterB so they will only be able to pull the tables we make avaiable to them. Our current method is database dumps but this is become impracticle due to size. Replication in testing works for what we want to do, we just have a problem with a single field. Any ideas on how to make this work? Gary Wayne Smith "Questo messaggio ed ogni suo allegato sono confidenziali e possono essere riservati o, comunque, protetti dall'essere diffusi. Se il ricevente non é il destinatario diretto del presente messaggio, é pregato di contattare l'originario mittente e di cancellare questo messaggio ed ogni suo allegato dal sistema di posta. Se il ricevente non é il destinatario diretto del presente messaggio, sono vietati l'uso, la riproduzione e la stampa di questo messaggio e di ogni suo allegato, nonché la diffusione del loro contenuto a qualsiasi altro soggetto" * "This message and any attachment are confidential and may be privileged or otherwise protected from disclosure. If you are not the intended recipient, please contact the sender and delete this message and any attachment from your system. If you are not the intended recipient you must not use, copy or print this message or attachment or disclose the contents to any other person."
RE: Column level replication q?
And that is a hack at best, but it does give me some ideas. I really only need two fields out of that table anyhow so I might actually migrate the columns that I care about into a new table and update the corresponding SQL statements that I use to update them (i.e. split the source data). From: [EMAIL PROTECTED] on behalf of Baron Schwartz Sent: Tue 2/19/2008 5:15 PM To: Gary W. Smith Cc: mysql@lists.mysql.com Subject: Re: Column level replication q? Hi, This isn't "natively" supported. You can hack it with replication to a table that has a trigger, which will then insert all but one column into another table, which you can replicate on to the final destination. But I'm scared of such hacks for anything that matters :-)
Re: Column level replication q?
Hi, On Feb 19, 2008 7:17 PM, Gary W. Smith <[EMAIL PROTECTED]> wrote: > We need to replicate a table to a third part. The information in the table > is pretty much public, with the exception of one column. Is it possible to > replicate all of the tables with the exception of one column? > > What I was thinking was to replication it to a 2nd machine that will limit it > to the tables we care about and then expose that slave machine as a secondary > master. > > We are looking to do something like this > > MasterA -> SlaveA/MasterB -> SlaveC > > MasterA tableA (our machine) > field1 > field2 > field3 > field4 > > SlaveA/MasterB (our machine) > field1 > field2 > field4 > > SlaveC (their machine) > field1 > field2 > field4 > > We know we can limit the tables which they can use (which is why we have > introducted SlaveA/MasterB so they will only be able to pull the tables we > make avaiable to them. > > Our current method is database dumps but this is become impracticle due to > size. Replication in testing works for what we want to do, we just have a > problem with a single field. > > Any ideas on how to make this work? This isn't "natively" supported. You can hack it with replication to a table that has a trigger, which will then insert all but one column into another table, which you can replicate on to the final destination. But I'm scared of such hacks for anything that matters :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
