使用 JDBC 启用 MySQL 通用查询日志
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10903206/
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
Enabling MySQL general query log with JDBC
提问by alfredough
Is there a way to enable MySQL general query logging through JDBC? The closest thing I have found through my search is the ability to log slow queries through JDBC (http://dev.mysql.com/doc/refman/5.5/en/connector-j-reference-configuration-properties.html). Maybe I should do that and set the slow query threshold to 0 ms?
有没有办法通过 JDBC 启用 MySQL 常规查询日志记录？我通过搜索发现的最接近的是能够通过 JDBC (http://dev.mysql.com/doc/refman/5.5/en/connector-j-reference-configuration-properties.html) 记录慢查询。也许我应该这样做并将慢查询阈值设置为 0 毫秒？
I would like to log all queries through MySQL in a human-readable format and would like to specify the location where the log file should be written. I know I will take a performance hit, but my application only has one user and is simple enough that I would be surprised if the performance hit was noticeable. I would like to try it out anyway to see.
我想以人类可读的格式记录通过 MySQL 的所有查询，并想指定应该写入日志文件的位置。我知道我的性能会受到影响，但我的应用程序只有一个用户，而且非常简单，如果性能受到明显影响，我会感到惊讶。反正我想试试看。
I believe another option I have is to turn on binary logging and use mysqlbinlog to convert the binary logs to a human-readable format, but it sounds like the general query log would provide a simpler means of getting what I want.
我相信我的另一个选择是打开二进制日志记录并使用 mysqlbinlog 将二进制日志转换为人类可读的格式，但听起来通用查询日志将提供一种更简单的方法来获取我想要的内容。
采纳答案by alfredough
I ended up finding a workaround. I enable MySQL general query logging through Java by modifying MySQL global system variables at runtime with the following SQL queries.
我最终找到了解决方法。我通过使用以下 SQL 查询在运行时修改 MySQL 全局系统变量，通过 Java 启用 MySQL 常规查询日志记录。
SET GLOBAL log_output="FILE"
SET GLOBAL general_log_file="Path/File"
SET GLOBAL general_log='ON'
I recommend using forward slashes in the general_log_file path. I could not get backslashes to work, even in a Windows environment.
我建议在 general_log_file 路径中使用正斜杠。即使在 Windows 环境中，我也无法使用反斜杠。
I disable general query logging at runtime with the following SQL query.
我使用以下 SQL 查询在运行时禁用一般查询日志记录。
SET GLOBAL general_log='OFF'
回答by Frans
You can enable logging in the JDBC URL like this:
您可以像这样在 JDBC URL 中启用日志记录：
jdbc:mysql://host/db?logger=com.mysql.jdbc.log.Log4JLogger&profileSQL=true
Other logging backends are available (CommonsLogger, Slf4jLogger, JDK14Logger). I believe direct Log4J logging was dropped at some point due to licencing issues so it might not work with your version of the JDBC driver.
其他日志后端可用（CommonsLogger、Slf4jLogger、JDK14Logger）。我相信由于许可问题，直接 Log4J 日志记录在某些时候被删除，因此它可能不适用于您的 JDBC 驱动程序版本。
Naturally, you'll need the relevant logging library's JAR in your classpath, and a configuration file (log4j.properties). I would set the root level to TRACE first to see what's happening and tighten it up by log level and category once you see what's being logged.
自然，您需要在类路径中包含相关日志库的 JAR，以及一个配置文件 (log4j.properties)。我会首先将根级别设置为 TRACE 以查看发生了什么，并在您看到正在记录的内容后按日志级别和类别收紧它。
Further reading:
进一步阅读：
- https://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html
- http://docs.oracle.com/cd/E17952_01/mysql-monitor-2.3-en/mem-qanal-using-cj.html
- https://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html
- http://docs.oracle.com/cd/E17952_01/mysql-monitor-2.3-en/mem-qanal-using-cj.html
HTH
HTH
回答by imxylz
Add 'logger' and 'profileSQL' to the jdbc url:
将 'logger' 和 'profileSQL' 添加到 jdbc url：
&logger=com.mysql.jdbc.log.Slf4JLogger&profileSQL=true
Then you will get the SQL statement below:
然后你会得到下面的SQL语句：
2016-01-14 10:09:43 INFO MySQL - FETCH created: Thu Jan 14 10:09:43 CST 2016 duration: 1 connection: 19130945 statement: 999 resultset: 0
2016-01-14 10:09:43 INFO MySQL - QUERY created: Thu Jan 14 10:09:43 CST 2016 duration: 1 connection: 19130945 statement: 999 resultset: 0 message: SET sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'
2016-01-14 10:09:43 INFO MySQL - FETCH created: Thu Jan 14 10:09:43 CST 2016 duration: 1 connection: 19130945 statement: 999 resultset: 0
2016-01-14 10:09:43 INFO MySQL - QUERY created: Thu Jan 14 10:09:43 CST 2016 duration: 2 connection: 19130945 statement: 13 resultset: 17 message: select 1
2016-01-14 10:09:43 INFO MySQL - FETCH created: Thu Jan 14 10:09:43 CST 2016 duration: 0 connection: 19130945 statement: 13 resultset: 17
2016-01-14 10:09:43 INFO MySQL - QUERY created: Thu Jan 14 10:09:43 CST 2016 duration: 1 connection: 19130945 statement: 15 resultset: 18 message: select @@session.tx_read_only
2016-01-14 10:09:43 INFO MySQL - FETCH created: Thu Jan 14 10:09:43 CST 2016 duration: 0 connection: 19130945 statement: 15 resultset: 18
2016-01-14 10:09:43 INFO MySQL - QUERY created: Thu Jan 14 10:09:43 CST 2016 duration: 2 connection: 19130945 statement: 14 resultset: 0 message: update sequence set seq=seq+incr where name='demo' and seq=4602
2016-01-14 10:09:43 INFO MySQL - FETCH created: Thu Jan 14 10:09:43 CST 2016 duration: 0 connection: 19130945 statement: 14 resultset: 0
The default logger is:
默认记录器是：
com.mysql.jdbc.log.StandardLogger
Mysql jdbc property list: https://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html
Mysql jdbc 属性列表：https: //dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html
回答by Eric R. Rath
If you're using Hibernate, and perform all data access through it, you can turn on logging by setting the property hibernate.show_sqlto true. This will write parameterized statements (e.g. SELECT foo.id FROM foo WHERE foo.bar = ?), though. If you need parameter values, or don't use a tool like Hibernate, you might need to have MySQL write this log. See the MySQL documentation on the general query log.
如果您正在使用 Hibernate，并通过它执行所有数据访问，您可以通过将该属性设置hibernate.show_sql为 true来打开日志记录。不过，这将编写参数化语句（例如SELECT foo.id FROM foo WHERE foo.bar = ?）。如果你需要参数值，或者不使用像 Hibernate 这样的工具，你可能需要让 MySQL 写这个日志。请参阅有关一般查询日志的 MySQL 文档。
FWIW, the MySQL binary log is a means to a different end; it records changes to the data, and is used for incremental backups and/or replication. SELECTstatements aren't recorded in the binary log.
FWIW，MySQL 二进制日志是达到不同目的的一种手段；它记录对数据的更改，并用于增量备份和/或复制。 SELECT语句不会记录在二进制日志中。
EDIT:I was able to get MySQL to write the general log by adding the following two lines to my.cnf (after confirming neither variable was already set), and restarting MySQL:
编辑：通过将以下两行添加到 my.cnf（在确认两个变量都没有设置之后）并重新启动 MySQL，我能够让 MySQL 写入一般日志：
general_log = 1
general_log_file=/tmp/mysql-general.log
回答by Gabriel Gates
Using MySQL along with the mysql-connector-java-8.0.13.jarI added the profileSQL=trueto my JDBC connection URL. For example:
将 MySQL 与mysql-connector-java-8.0.13.jar一起使用，我将它添加profileSQL=true到我的 JDBC 连接 URL。例如：
jdbc:mysql://${HOST}:${PORT}/${SCHEMA}?autoReconnect=true&profileSQL=true
You could also add &logger=com.mysql.cj.log.StandardLoggerto the URL, but is not needed because it is the default value as indicated in the documentation.
您也可以添加&logger=com.mysql.cj.log.StandardLogger到 URL，但不需要，因为它是文档中指示的默认值。
Documentation reference:
文档参考：
https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-configuration-properties.html
https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-configuration-properties.html
The logging output was annoying to read because it contains a stack trace indicating where exactly in the code the query was run. If anyone figures out a way to exclude the stack trace please let me know.
日志输出读起来很烦人，因为它包含一个堆栈跟踪，指示查询在代码中的确切运行位置。如果有人想出排除堆栈跟踪的方法，请告诉我。
