Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do i run a query in MYSQL without writing it to the binary log

I want to run an import of a large file into MySQL. However, I don't want it written to the binary log, because the import will take a long time, and cause the slaves to fall far behind. I would rather run it seperately on the slaves, after the fact, because it will be much easier on the system. The table in question is a new one, and therefore I don't really have to worry about it getting out of sync, as the master and all the slaves will have the same data in the end, because they will all import the same file eventually. I also don't want to change any of the replicate-ignore-* or binlog_ignore-* options, because they require a restart of the servers in question. Is there a a way to run a single query without writing it to the binary log?

I already know the answer, but I couldn't find it very easily on the net, so I'm letting somebody post the answer to get some rep points. If there's no answers in a little white I'll post the answer I found.

like image 998
Kibbee Avatar asked Jun 02 '10 14:06

Kibbee


People also ask

How do I disable binary logging in MySQL?

To disable the MySQL Binary Logging, you can use the --skip-log-bin or --disable-log-bin option at startup. If one of the two options is used with –log-bin , the option that has been used recently gets precedence. The –log-bin variable is turned OFF when the Binary Logging is disabled.

Do I need MySQL binary logs?

The MySQL BinLogs serve two important purposes: Replication: When working on a master server, the binary logs contain a record of the changes that have occurred. These records are sent to the slave servers to help them execute those events & make the same data changes that were made on the master server.

Can I delete binary logs MySQL?

The PURGE BINARY LOGS statement deletes all the binary log files listed in the log index file prior to the specified log file name or date. BINARY and MASTER are synonyms. Deleted log files also are removed from the list recorded in the index file, so that the given log file becomes the first in the list.

How do I use binary logs in MySQL?

mysql> SHOW BINARY LOGS; To determine the name of the current binary log file, issue the following statement: mysql> SHOW MASTER STATUS; The mysqlbinlog utility converts the events in the binary log files from binary format to text so that they can be viewed or applied.


2 Answers

You can use the sql-log-bin session variable to turn off logging for the current session. Just simply enter:

SET sql_log_bin = 0; 

and all queries on your current session will not be sent to the binary log. If you want to turn binary logging back on, run:

SET sql_log_bin = 1; 

This is only for the currently running session you are in. All other queries from all other connections will still continued to be logged. Also, you must have SUPER privileges for this to work.

like image 121
Kibbee Avatar answered Sep 20 '22 21:09

Kibbee


If you want to do this from the cli, try this, it worked for me:

$ mysqldump old_DB | mysql --init-command="SET SQL_LOG_BIN = 0;" new_DB 

Possible that the "init-command" param was added in a newer MySQL version.

like image 21
Mihai Avatar answered Sep 18 '22 21:09

Mihai