Enabling MySQL general query log with JDBC

Add 'logger' and 'profileSQL' to the jdbc url:

&logger=com.mysql.jdbc.log.Slf4JLogger&profileSQL=true

Then you will get the SQL statement below:

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

If you're using Hibernate, and perform all data access through it, you can turn on logging by setting the property hibernate.show_sql to 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.

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. SELECT statements aren't recorded in the binary log.

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:

general_log = 1
general_log_file=/tmp/mysql-general.log

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