Enabling MySQL General Query Log

We have Extended Events and Profiler in SQL Server for tracking database activity. What if we want to track queries in MySQL? Let’s take a look at a few methods to do just that.

General Query Log

The general query log in MySQL can be used to view what’s happening on your MySQL server. In the following examples, we’ll use Workbench to setup viewing this log from a table and from a file.

Log to Table

First, let’s show how to log to a table. We’ll set general_log=1 and specify that we want to log to a table:

SET global log_output = 'table';
SET global general_log = 1;

Let’s run some test queries against the callihan test schema so that we have something to look at in the log:

SELECT * FROM callihan.user;
DELETE FROM callihan.user WHERE id = 4;

Now, let’s run a query to check what was logged:

SELECT * FROM mysql.general_log;

We see BLOB for the argument. We can right click and select “Open Value in Viewer” to see the argument but this could be tedious if we’re looking through a long list of activity. Another option would be to cast the argument to get a better idea of what’s been captured:

SELECT *, CAST(argument as char(100)) as 'cast_arg' 
FROM mysql.general_log;

This makes it easier to skim the results and then open if you find a record you’re curious about.

Log to File

Let’s take a look at how we can point the general_log output to a file instead of a table.

Let’s turn off our logging for now:

SET global general_log = 0;

We’ll specify that we want to log to a file and our log file location. In this case, I’m going to have our file in C:/Temp:

SET global log_output = 'file';
SET global general_log_file='C:/Temp/mysql.log';

Let’s turn logging back on and verify that our file gets created:

SET global general_log = 1;

If we open the file we have not captured any data yet:

Let’s run a few test queries:

SELECT * FROM callihan.user;
DELETE FROM callihan.user WHERE id = 3;

Now we can open our file and see some results:

Thanks for reading!