# logging – How to enable MySQL Query Log?

## The Question :

262 people think this question is useful

How do I enable the MySQL function that logs each SQL query statement received from clients and the time that query statement has submitted? Can I do that in phpmyadmin or NaviCat? How do I analyse the log?

314 people think this answer is useful

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;



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.

200 people think this answer is useful

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:

• 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;



64 people think this answer is useful

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;



61 people think this answer is useful

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.

15 people think this answer is useful

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

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



9 people think this answer is useful

I also wanted to enable the MySQL log file to see the queries and I have resolved this with the below instructions

1. Go to /etc/mysql/mysql.conf.d
2. open the mysqld.cnf

and enable the below lines

general_log_file        = /var/log/mysql/mysql.log
general_log             = 1


1. restart the MySQL with this command /etc/init.d/mysql restart
2. go to /var/log/mysql/ and check the logs

7 people think this answer is useful
// To see global variable is enabled or not and location of query log
SHOW VARIABLES like 'general%';
// Set query log on
SET GLOBAL general_log = ON;



3 people think this answer is useful

On Windows you can simply go to

C:\wamp\bin\mysql\mysql5.1.53\my.ini



Insert this line in my.ini

general_log_file = c:/wamp/logs/mysql_query_log.log



The my.ini file finally looks like this

...
...
...
socket      = /tmp/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
myisam_sort_buffer_size = 8M
basedir=c:/wamp/bin/mysql/mysql5.1.53
log = c:/wamp/logs/mysql_query_log.log        #dump query logs in this file
log-error=c:/wamp/logs/mysql.log
...
...
...
...



3 people think this answer is useful

There is bug in MySQL 5.6 version. Even mysqld show as :

    Default options are read from the following files in the given order:
C:\Windows\my.ini C:\Windows\my.cnf C:\my.ini C:\my.cnf c:\Program Files (x86)\MySQL\MySQL Server 5.6\my.ini c:\Program Files (x86)\MySQL\MySQL Server 5.6\my.cnf



Realy settings are reading in following order :

    Default options are read from the following files in the given order:
C:\ProgramData\MySQL\MySQL Server 5.6\my.ini C:\Windows\my.ini C:\Windows\my.cnf C:\my.ini C:\my.cnf c:\Program Files (x86)\MySQL\MySQL Server 5.6\my.ini c:\Program Files (x86)\MySQL\MySQL Server 5.6\my.cnf



Check file: “C:\ProgramData\MySQL\MySQL Server 5.6\my.ini”

Hope it help somebody.

1 people think this answer is useful

for mysql>=5.5 only for slow queries (1 second and more) my.cfg

[mysqld]
slow-query-log = 1
slow-query-log-file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log-queries-not-using-indexes



1 people think this answer is useful

Open the following file:

vi /private/etc/my.cnf



Set the query log url under ‘mysqld’ section as follows:

[mysqld]

general_log_file=/Users/kumanan/Documents/mysql_query.log



Few machine’s are not logging query properly, So that case you can enable it from MySQL console

mysql> SET global general_log = 1;



1 people think this answer is useful

Not exactly an answer to the question because the question already has great answers. This is a side info. Enabling general_log really put a dent on MySQL performance. I left general_log =1 accidentally on a production server and spent hours finding out why performance was not comparable to a similar setup on other servers. Then I found this which explains the impact of enabling general log. http://www.fromdual.com/general_query_log_vs_mysql_performance.

Gist of the story, don’t put general_log=1 in the .cnf file. Instead use set global general_log =1 for a brief duration just to log enough to find out what you are trying to find out and then turn it off.

0 people think this answer is useful

In phpMyAdmin 4.0, you go to Status > Monitor. In there you can enable the slow query log and general log, see a live monitor, select a portion of the graph, see the related queries and analyse them.

[ERROR] Incorrect definition of table mysql.general_log: expected the type of column 'user_host' at position 1 to have character set 'utf8' but found character set 'latin1'