Tip of the Day — Disabling Binary Logging Temporarily
Recently I came across something new (for me anyways). There are times when it can be useful to temporarily turn off binary logging. You can disable it in the my.cnf file, but that requires a daemon restart. Coming from the Reference Manual:
“A client that has the SUPER privilege can disable binary logging of its own statements by using a SET SQL_LOG_BIN=0 statement”
As a brief example, if I am loading a large table it could be good to disable logging before beginning the import.
mysql> SET SQL_LOG_BIN=0;
mysql> LOAD DATA INFILE ‘honking_big_file’ INTO BIG_TABLE;
I don’t know about you, but more than once I have started a large import and had /var fill up because of logging. Just remember, if you need the table replicated to a slave server this will defeat replication.
Hope that is useful.
3 Comments so far
Leave a reply
An example of a useful time to do this is when restoring from a backup; binary logging of a restore of a backup is almost never useful (unless you’re doing it into a replication master), and you’re usually restoring it as a user with SUPER privilege.
Something like:
(echo ‘SET SESSION SQL_LOG_BIN=0;’; gzip -c -d backupfile.sql.gz) | mysql -u whoever -pwhatever
Will do it.
Mark
thanks mark!
I don’t really understand why you would ever have binlogging enabled if you are not using replication. Does it have other uses?
I like to keep the sql log enabled in devel environments, but production allways has it off to minimize IO noise. Seems like having the binlog on would just add IO ops and take up space for no reason (unless you are replicating)