如何在MYSQL中运行查询而不将其写入二进制日志 [英] How do i run a query in MYSQL without writing it to the binary log

问题描述

我想在MySQL中运行一个大文件的导入。但是，我不希望它写入二进制日志，因为导入将花费很长时间，并导致奴隶远远落后。事实上，我宁愿在奴隶上单独运行它，因为它在系统上会更容易。有问题的表是一个新表，因此我不必担心它不同步，因为主设备和所有从设备最终将具有相同的数据，因为它们将导入相同的文件最终。我也不想更改任何replicate-ignore- *或binlog_ignore- *选项，因为它们需要重新启动有问题的服务器。有没有办法运行单个查询而不将其写入二进制日志？

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.

推荐答案

你可以使用 sql-log-bin 会话变量关闭日志记录对于本届会议。只需输入：

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;

这仅适用于您当前正在运行的会话。来自所有其他连接的所有其他查询仍然是继续被记录。此外，您必须具有SUPER权限才能使用。

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.

这篇关于如何在MYSQL中运行查询而不将其写入二进制日志的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！