How to enable MySQL Query Log?

Question

First, Remember that this logfile can grow very large on a busy server.

For mysql < 5.1.29:

To enable the query log, put this in /etc/my.cnf in the [mysqld] section

log   = /path/to/query.log  #works for mysql < 5.1.29

Also, to enable it from MySQL console

SET general_log = 1;

See http://dev.mysql.com/doc/refman/5.1/en/query-log.html

For mysql 5.1.29+

With mysql 5.1.29+ , the log option is deprecated. To specify the logfile and enable logging, use this in my.cnf in the [mysqld] section:

general_log_file = /path/to/query.log
general_log      = 1

Alternately, to turn on logging from MySQL console (must also specify log file location somehow, or find the default location):

SET global general_log = 1;

Also note that there are additional options to log only slow queries, or those which do not use indexes.

Take a look on this answer to another related question. It shows how to enable, disable and to see the logs on live servers without restarting.

Log all queries in mysql

---

Here is a summary:

If you don't want or cannot restart the MySQL server you can proceed like this on your running server:

• Create your log tables (see answer)

• Enable Query logging on the database (Note that the string 'table' should be put literally and not substituted by any table name. Thanks Nicholas Pickering)

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

• View the log

select * from mysql.general_log;

• Disable Query logging on the database

SET global general_log = 0;

• Clear query logs without disabling

TRUNCATE mysql.general_log

This was already in a comment, but deserves its own answer: Without editing the config files: in mysql, as root, do

SET global general_log_file='/tmp/mysql.log'; 
SET global log_output = 'file';
SET global general_log = on;

Don't forget to turn it off afterwards:

SET global general_log = off;

I use this method for logging when I want to quickly optimize different page loads. It's a little tip...

Logging to a TABLE

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

You can then select from my mysql.general_log table to retrieve recent queries.

I can then do something similar to tail -f on the mysql.log, but with more refinements...

select * from mysql.general_log 
where  event_time  > (now() - INTERVAL 8 SECOND) and thread_id not in(9 , 628)
and argument <> "SELECT 1" and argument <> "" 
and argument <> "SET NAMES 'UTF8'"  and argument <> "SHOW STATUS"  
and command_type = "Query"  and argument <> "SET PROFILING=1"

This makes it easy to see my queries that I can try and cut back. I use 8 seconds interval to only fetch queries executed within the last 8 seconds.

You can disable or enable the general query log (which logs all queries) with

SET GLOBAL general_log = 1 # (or 0 to disable)