Enable General and Slow Query Logging

As I work with applications that use a MySQL database, I have various ways to check my actual queries, but MySQL makes it really easy to log queries and slow queries. Since my dev machine is 64-bit Windows 7, and I’m running XAMPP 1.8.0, I just needed to add a few lines to MySQL’s my.ini file, and turn the query logging on in phpMyAdmin.

UPDATE: Since adding XAMPP 1.8.3, slight changes have been necessary. See details at end of post.

Enable Query Logging in my.ini

[mysqld]
#Enable Slow Query Log
long_query_time = 1
slow_query_log_file = "C:/slowquery.log"

#Do NOT Start Logging on Startup
slow_query_log = 0

#Enable General Log XAMPP 180
log = "C:/genquery.log"

#Enable General Log XAMPP 183
#general_log_file = "C:/xampp183/genquery.log"

#Do NOT Start Logging on Startup
general_log = 0

Notice that I specifically changed the log location to C:/. I wasn’t sure if MySQL would be able to create these files on it’s own, so I created them manually.

Turn On Logging in phpMyAdmin

In phpMyAdmin, I went to the SQL tab, and ran this:

SET GLOBAL general_log = 'ON';
SET GLOBAL slow_query_log = 'ON';

Now if you open up genquery.log in your text editor, you can watch this file update each time you load a page or otherwise run queries. Notepad++ will tell you that the file has changed, and ask if you would you like to update it. Sublime Text 2 just applies the changes as soon as you click within it’s window. I think the way Notepad++ behaves is a little annoying, so I just use Sublime Text 2.

I’ve never experienced a query that got logged to the slow query log, so I don’t have any information about it. I guess that’s a good thing.

Turn Off Logging When You’re Done

When you are all done, there’s no reason to keep logging, so turn it off in phpMyAdmin:

SET GLOBAL general_log = 'OFF';
SET GLOBAL slow_query_log = 'OFF';

Update for XAMPP 1.8.3

The log system variable was removed in MySQL 5.6.1. Instead, use the general_log_file = “file_name” option to set the general query log file name. Also, due to Windows file permissions, you may need to locate your log files inside your XAMPP directory, instead of directly on C as shown above.