run trigger without writing to binary log

Question

I'm trying to insert rows into a table via a trigger or stored procedure without writing any data to the binary log. Is this possible? I know that for normal connections you can set SQL_LOG_BIN=0 to disable binary logging for the connection, but I haven't been able to get that to work for triggers. If there's a way to do this with a federated engine table, that would also be OK.

edit:

I can almost accomplish this via a stored procedure:

CREATE PROCEDURE nolog_insert(`id` INT, `data` blob)
BEGIN
  SET SESSION SQL_LOG_BIN = 0;
  INSERT INTO `table` (`id`, `data`) VALUES (id, data);
  SET SESSION SQL_LOG_BIN = 1;
END

I insert a record by calling the procedure from the mysql prompt:

call nolog_insert(50, 'notlogged');

As expected, the record (50, 'notlogged') is inserted into the table, but is not written to the binary log.

However, I want to run this procedure from a trigger. When using a trigger as follows:

create trigger my_trigger before insert on blackhole_table for each row call nolog_insert(new.id, new.data);

The data is both inserted to the table and written to the binary log.

Answer 1

If you run statement based replication triggers are executed on both the master and the slave but not replicated. Perhaps that could solve your problem.

Other than that, it's not allowed to change sql_log_bin inside a transaction so I would say that there is no good way to have the effects of a trigger not replicated when using row based replication.