Enabling MySQL query logging

I recently installed the LogAnalyzer graphical syslog analysis tool. After the install completed I went to the “Show Events” page and noticed that no data was being displayed. I wanted to see which queries were being sent by LogAnalyzer to my MySQL database instance, so I enabled query logging by adding the following two statements to the [mysqld] block in the /etc/my.cnf configuration file:


The first line enables logging, and the second line tells MySQL were to write the logs. Once enabled you can see the queries executed against your server by paging out the contents of /var/log/query.log. This will have one or more entries similar to the following:

233 Query Select FOUND_ROWS()
120212 13:15:07 233 Quit
120212 13:15:12 234 Connect rsyslog@localhost on
234 Init DB syslog
234 Query SHOW TABLES LIKE '%SystemEvents%'
234 Query SELECT SQL_CALC_FOUND_ROWS id, devicereportedtime, facility, priority, fromhost, syslogtag, processid, infounitid, message FROM SystemEvents ORDER BY id DESC LIMIT 100

Pretty cool, and definitely super useful for debugging problems and figuring out how restrictive you can be with your GRANT statements. Viva la MySQL!

This article was posted by Matty on 2012-02-14 06:00:00 -0400 -0400