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

User · Answer

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.

User · Answer

I also wanted to enable the MySQL log file to see the queries and I have resolved this with the below instructions   Go to  etc mysql mysql conf d open the mysqld cnf   and enable the below lines  general log file           var log mysql mysql log general log               1    restart the MySQL with this  command  etc init d mysql restart go to  var log mysql  and check the logs

User · Answer

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

User · Answer

You can disable or enable the general query log  which logs all queries  with  SET GLOBAL general log   1    or 0 to disable

User · Answer

First  Remember that this logfile can grow very large on a busy server   For mysql  lt  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  lt  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

User · Answer

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

User · Answer

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

User · Answer

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 read buffer size   256K read rnd buffer size   512K 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 datadir c  wamp bin mysql mysql5 1 53 data

User · Answer

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

User · Answer

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 gt  SET global general log   1

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] How to enable MySQL Query Log?

Examples related to mysql

Examples related to logging