MySQL query log - how to log all queries?

Hi guys!

Today I would like to tell you a bit more about MySQL query logging. It's very useful to preview all queries that goes to your database. Sometimes you don't have to access to direct query that is generated by ORM, but you have to check what's going on? Not a problem. Enable logging in your MySQL service and get all queries.

Problem with MySQL query logging

When using Drupal I can not previewed exact query that goes to database. I checked documentation for any useful function that can print full SQL query but I didn't find anything like that. So after further investigation and checking the code I get the PDO method. So yes, now I can print SQL query but without parameters... You can get queries like:

SELECT * FROM table_name WHERE name LIKE :name

or very similar. Unfortunately I needed full query with binded parameters. Sure, I could write some short function that will bind all parameters for me, it would be easy. But sometimes there goes very large query with hell of a lot of parameters including arrays etc. I needed something more than that.

I looked for specialized software etc but hey! Why not use the database build-in logging mechanism? It can catch all the database queries that are executed to your database. It was exactly what I needed.

How to turn on MySQL query log

OK, case is very simple, basically there are 3 ways of turning on the MySQL query log in your installation.

Method 1

You can turn on log on MySQL start/restart via command:

mysqld --general_log=1 --general_log_file=my_logs.txt

First option will turn on query log, second is path to the file where log will be saved.

Depend on the version of MySQL you're using: You should use --log parameter. More informations here: http://dev.mysql.com/doc/refman/5.1/en/query-log.html

Method 2

Method 2 is very similar to method 1 above. It's hard to remember that every time you're run MySQL you should pass logging parameters if You want to turn on MySQL query log. It's easier if you put these settings into MySQL configuration file.

Open Your configuration file and append these lines in [mysqld] section:

general_log=1
general_log_file=my_logs.txt

Have in mind version of your mysql installation. Check method 1 with --log parameter. After saving changes in mysql configuration file restart MySQL service to enable logging mechanism.

*Method 3

My favorite method:) If you don't want to restart MySQL server or you want to turn logging only for single page refresh than turn off logging it's your method.

Execute queries below:

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';

You will find all logs in mysql.general_log table. It's pretty useful because you can perform select operation on this table and you don't have to bother searching in text file. For me it's best method for good MySQL query log:)

If you want to log queries into file except of database table execute these queries:

SET GLOBAL log_output = 'FILE';
SET GLOBAL general_log_file = 'my_logs.txt';
SET GLOBAL general_log = 'ON';

You can execute these queries in runtime! There's no need to restart your MySQL service.

You can read more about setting global configuration options here:

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_general_log

Viewing MySQL query log

Log file is very easy to read. It's text file so you can open it in any editor you prefer. Open your log file, you will find there 4 columns:

Time - date time of query execution in yymmdd hh:mm:ss format.
Id - Thread Id of the connected client
Command - Command type, usually Query
Argument - Query body executed on MySQL body

Important info

OK, so now you have the list of the queries that are executed on database. It's great to check the full query executed on database. It's really helpful especially when You don't have direct access to generated queries for example like in Drupal.

But don't forget to turn off your logging. When You have many queries executed on your database it will make query log very big and you can run out of space on your disk. Second thing is lot of write operation when logging is turned off. So it's nice to turn logging on only for debug purposes and turn off logging on production environments.