无效查询的 MySQL 日志
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4631133/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Log of invalid Queries
提问by anon5456919
I AM NOT RUNNING THE COMMANDS FROM PHP!
我不是从 PHP 运行命令！
I have MySQL log_error value set to /var/log/mysql/error.log
我将 MySQL log_error 值设置为 /var/log/mysql/error.log
However when I connect to a database and run an SQL command, the error does not appear in the log.
但是，当我连接到数据库并运行 SQL 命令时，日志中没有出现该错误。
SELECT *
FROM some_table
where this_is_invalid_and_will_fail="Doesn't matter because column doesn't exist!";
There are commands running from some sort of windows application. All I want to know is what invalid commands its sending to the MySQL server so that I can attempt to resolve them.
有从某种 Windows 应用程序运行的命令。我只想知道它发送到 MySQL 服务器的无效命令是什么，以便我可以尝试解决它们。
采纳答案by Mchl
Error log doesn't do that: https://dev.mysql.com/doc/refman/8.0/en/error-log.html
错误日志不会这样做：https: //dev.mysql.com/doc/refman/8.0/en/error-log.html
The error log contains information indicating when mysqld was started and stopped and also any critical errors that occur while the server is running.
错误日志包含指示何时启动和停止 mysqld 以及服务器运行时发生的任何严重错误的信息。
MySQL doesn't log invalid/failed queries anywhere.
MySQL 不会在任何地方记录无效/失败的查询。
If it's for debugging purposes, you might try setting up a MySQL Proxy, which could log this I think:
如果是出于调试目的，您可以尝试设置一个 MySQL 代理，我认为它可以记录以下内容：
回答by Andrew Zhilin
Basically, there are 2 ways.
基本上，有2种方式。
1) setup some kind of proxy, which can log error queries. There are a lot of forks of the original mysql proxy (which was mentioned by @Mchl) - https://github.com/search?utf8=%E2%9C%93&q=MySQL+ProxyAlso you can find latest version of the original mysql proxy here https://github.com/mysql/mysql-proxy
1）设置某种代理，可以记录错误查询。原来的mysql代理有很多fork（@Mchl提到过） - https://github.com/search?utf8=%E2%9C%93&q=MySQL+Proxy你也可以找到最新版本的原来的mysql代理在这里https://github.com/mysql/mysql-proxy
I dont like this way, because its kind of too complicated for quick debug
我不喜欢这种方式，因为它对于快速调试来说太复杂了
2) you can enable general log in mysql (which will log ALL queries). It will create big overhead and doesnt fit production requirements, but its fast and easy way to fix bugs in development environment.
2）您可以在mysql中启用一般登录（它将记录所有查询）。它会产生很大的开销并且不符合生产要求，但是它可以快速简便地修复开发环境中的错误。
Just login to your mysql and execute
SET GLOBAL general_log = 'ON';
SHOW GLOBAL VARIABLES LIKE 'general_log%';
只需登录到您的 mysql 并执行
SET GLOBAL general_log = 'ON';
SHOW GLOBAL VARIABLES LIKE 'general_log%';
You will see logs location, like
+------------------+------------------------+
| Variable_name | Value |
+------------------+------------------------+
| general_log | OFF |
| general_log_file | /mnt/ssd/mysql/s.log |
+------------------+------------------------+
您将看到日志位置，例如
+------------------+------------------------+
| Variable_name | Value |
+------------------+------------------------+
| general_log | OFF |
| general_log_file | /mnt/ssd/mysql/s.log |
+------------------+------------------------+
After that execute
mysqladmin flush-logs -u root -p
之后执行
mysqladmin flush-logs -u root -p
When you will need to stop logs - just execute
SET GLOBAL general_log = 'OFF';
当您需要停止日志时 - 只需执行
SET GLOBAL general_log = 'OFF';
回答by Stas Filippov
As of mysql version 5.6.3 (released 2011-10-03), there is a variable called log-rawwhich allows you to include invalid queries in the general query log:
从 mysql 版本 5.6.3（2011-10-03 发布）开始，有一个名为的变量log-raw，它允许您在常规查询日志中包含无效查询：
https://dev.mysql.com/doc/refman/5.6/en/server-options.html#option_mysqld_log-raw
https://dev.mysql.com/doc/refman/5.6/en/server-options.html#option_mysqld_log-raw
You will need to turn on general query log using general-logand general-log-file, e.g.:
您需要使用general-log和打开一般查询日志general-log-file，例如：
[mysqld]
general-log=1
log-raw=1
general-log-file=/var/log/mysql/general.log
回答by Martin Broadhurst
Could you enable the General Query Log? That should tell you everything you need to know.
你能启用通用查询日志吗？这应该告诉你你需要知道的一切。
回答by Silver Light
This is not so trivial. The best way to do this, is to log bad queries in your application. There is no built-in way.
这不是那么简单。最好的方法是在您的应用程序中记录错误的查询。没有内置的方式。
