36

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.

Kibbee
  • 65,369
  • 27
  • 142
  • 182

2 Answers2

60

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.

Kibbee
  • 65,369
  • 27
  • 142
  • 182
  • 1
    Would this work if I was doing this in the command line? `mysqldump mydb | mysql -h remote.host.com remote_db` – vinhboy Mar 29 '13 at 19:01
  • Yes, the main point it should be in one mysql seession. If you reconnect it is different session. – Iaroslav Vorozhko Apr 03 '13 at 09:14
  • You need to prepend command to the output of mysqldump. Like `echo "SET sql_log_bin = 0; " & mysqldump mydb | mysql -h remote.host.com remote_db` Not sure it is correct syntax to prepend sting, but you can start from this. – Iaroslav Vorozhko Apr 03 '13 at 09:18
  • Or you could dump to a file, start up a new session and import the dump using the "source" command after executing Set SQL_LOG_BIN = 0; – Kibbee Apr 03 '13 at 12:17
  • @Kibbee, How Do I enable/disable sql_bin_log on Amazon RDS service? because this need SUPER privileges right? – Manish Sapkal Mar 26 '15 at 09:05
  • @ManishSapkal Try asking as a new question. For this command to work, you need SUPER. Not sure how to do that with Amazon. – Kibbee Mar 26 '15 at 12:39
26

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.

Rizier123
  • 58,877
  • 16
  • 101
  • 156
Mihai
  • 261
  • 3
  • 2