List:Replication« Previous MessageNext Message »
From:Johan De Meersman Date:November 2 2011 2:24pm
Subject:Re: sql_log_bin
View as plain text  
----- 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

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