Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I know you can SET SQL_LOG_BIN=0; to disable writing your session to the binlogs.

I was wondering if there was some single serving query syntax to prevent just that one query from being written w/o setting that session var to 0 and back to 1 afterwards?

share|improve this question
    
Sounds dangerous; messing up data consistency on your slaves. I'm curious why you would want to do this –  Derek Downey Jan 6 '12 at 21:02
    
Just some financial data. J/k It's basically heart beat updates from applications every couple of seconds. If there was a disaster and we had to restore or fail over we wouldn't care if had to start from scratch. It's not really causing a problem other than annoying me when looking through binlogs and got me curious :) –  atxdba Jan 6 '12 at 22:55
add comment

1 Answer 1

If you do the following:

  • connect to mysql
  • perform SET sql_log_bin=0;
  • execute the query (INSERT, UPDATE, or DELETE)
  • close the DB connection

then there is no need to SET sql_log_bin=1; afterwards

As an alternative, you may want to try this:

  • open a second DB Connection
  • set the SET sql_log_bin=0; (this done once in the lifetime of the second DB connection)
  • keep that second DB Connection open
  • use only this second DB connection for writes that are not be recorded in the binary logs

Give it a Try !!!

UPDATE 2012-01-06 14:55 EDT

Here is something interesting you may not have realized: Only those SUPER privilege can set one's own session to disable binary logging.

According to the MySQL Documentation on SUPER:

The SUPER privilege enables an account to use CHANGE MASTER TO, KILL or mysqladmin kill to kill threads belonging to other accounts (you can always kill your own threads), PURGE BINARY LOGS, configuration changes using SET GLOBAL to modify global system variables, the mysqladmin debug command, enabling or disabling logging, performing updates even if the read_only system variable is enabled, starting and stopping replication on slave servers, specification of any account in the DEFINER attribute of stored programs and views, and enables you to connect (once) even if the connection limit controlled by the max_connections system variable is reached.

To create or alter stored functions if binary logging is enabled, you may also need the SUPER privilege, as described in Section 18.7, “Binary Logging of Stored Programs”.

Having users with SUPER privilege gives them additional fire power that they may not need or may want to abuse.

share|improve this answer
    
The context for this was hoping I could pass something simple along to our developers along the lines "insert ignore..." w/o them having to worry about managing the separate connections just for this purpose. Also these are persistent Java apps that keep connections open. –  atxdba Jan 6 '12 at 18:29
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.