How to enable MySQL Query Log?

Question

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?

Answer 1

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.

Answer 2

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

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

Answer 3

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.

Answer 4

To enable the query log in MAC Machine:

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;

Answer 5

I had to drop and recreate the general log at one point. During the recreation, character sets got messed up and I ended up having this error in the logs:

[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'

So if the standard answer of "check to make sure logging is on" doesn't work for you, check to make sure your fields have the right character set.

Answer 6

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