8

I want to connect and execute one (or sometimes several) SQL statements, and NOT have those replicated to the slaves.

I have no replicate-do or replicate-ignore configs, so I can't use some non-replicated database to send the commands from. And I know about:

set global sql_slave_skip_counter = 1

But that's on the slave. I'd like to be able to run a similar command on the master and have the following N commands not sent out to the slaves (which I guess means not logged in the binlogs, either).

Nautical
  • 83
  • 1
  • 3

2 Answers2

16

SET sql_log_bin=0 is what you're looking for. Requires SUPER priv., and will turn off logging of commands from your session until you set it back to 1. See http://dev.mysql.com/doc/refman/5.0/en/server-session-variables.html#sysvar_sql_log_bin

SET sql_log_bin=0;
UPDATE ... ;
INSERT ... ;
DELETE ... ;
SET sql_log_bin=1 ;
derobert
  • 49,731
  • 15
  • 94
  • 124
  • The above link is old and dead. At the end of the line for 5.x you probably want https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html – dland Mar 03 '20 at 09:02
2

BE CAREFUL.....!

SET sql_log_bin=0;

Master MySQL server automatically set this 0 value to 1 after a while(after about one minute). It does not wait until we explicitly set it to 1. So according to my experience this not a safe way to turn off binary logging at all.....!

BenMorel
  • 34,448
  • 50
  • 182
  • 322
sambanxxx
  • 21
  • 2
  • 1
    What if it runs in a transaction? – Roman M Jul 16 '14 at 16:47
  • 3
    System variables do not magically change value when left alone. Your code is doing this explicitly, it's just you haven't debugged it to determine where and why. – dland Mar 03 '20 at 09:07