List:Replication« Previous MessageNext Message »
From:Marcus Bointon Date:November 2 2011 2:34pm
Subject:Re: sql_log_bin
View as plain text  
I quite agree. 

Another way to do the filtering would be to use mysqlproxy or tungsten replicator. I can
think of plenty of opportunities for a delete-free feed to fail. E.g. An insert that will
succeed on a and b could fail on c because of a key clash.

Sent from my iPhone

On 2 Nov 2011, at 15:24, Johan De Meersman <vegivamp@stripped> wrote:

> ----- Original Message -----
>> From: "Diego Martínez" <tabarem@stripped>
>> 
>> [ A -> B -> C ]
>> 
>> I try to put a set session sql_log_bin=0 or set session sql_log_bin=1
>> in triggers in B (before delete value 0, after delete value 1)... not
>> work :(
> 
> That's not entirely surprising, as the statement is already executing when that
> trigger fires :-p
> 
>> C must have all the data, no delete of any kind.
> 
> An interesting problem.
> 
> First of all, you must be very aware that this could cause serious errors in the
> dataset on C. Imagine a statement like'insert into table1 select * from table2'. If no
> deletes on table2 ever propagate to slave C, that statement is going to insert a whole lot
> more into table1 than it did on hosts A and B. That's just the tip of the iceberg.
> 
> The first thing that pops into my mind would be to put failing pre-delete triggers on
> all tables on C, but that is likely to break replication.
> 
> The second option would be to do the replication to C manually - copy the completed
> master logs, pass them through mysqldump, filter the delete statements and then apply the
> remainders.
> 
> You could partially automate that latter step by just running the slave's IO_THREAD
> to accumulate the relay logs, but it would be necessary to add skip-slave-start to your
> startup files to prevent the SQL_THREAD to launch upon service restart.
> 
> Maybe others have other suggestions, but I repeat that you must be very aware of the
> potential for incorrect data.
> 
> 
> -- 
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
> 
> -- 
> MySQL Replication Mailing List
> For list archives: http://lists.mysql.com/replication
> To unsubscribe:    http://lists.mysql.com/replication?unsub=1
> 
Thread
sql_log_binDiego Martínez2 Nov
  • Re: sql_log_binOleg Tsarev2 Nov
  • Re: sql_log_binJohan De Meersman2 Nov
    • Re: sql_log_binMarcus Bointon2 Nov
    • Re: sql_log_binMySQL)2 Nov
  • Re: sql_log_binRick James2 Nov
    • Re: sql_log_binMarcus Bointon2 Nov
      • Re: sql_log_binRicardo2 Nov
        • Re: sql_log_binMarcus Bointon2 Nov
          • Re: sql_log_binRicardo2 Nov
            • Re: sql_log_binDiego Martínez3 Nov