logging - Enabling MySQL general query log with JDBC

Question

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?

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.

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.

Answer 1

You can enable logging in the JDBC URL like this:

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.

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.

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

HTH