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?

Dioptase

Answer 1

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.

Tops

This is not working in MySQL 5.6.19. Did they change it again? – Dictation

general_log = on general_log_file=/path/to/query.log worked for me – Limy

This worked for me (MySQL 5.6.12 on Windows) : SET global general_log_file='c:/Temp/mysql.log'; SET global general_log = on; SET global log_output = 'file'; – Beagle

Make sure that the mysql user access permissions for the log file, or it will return a 'not found' error when setting the general_log parameter in the MySQL console – Cioffi

For 5.6.22 @Beagle got it exactly right. Thanks! The only thing to make sure is that the directory and file being written to exists and is writable by mysql. – Nephrolith

SET GLOBAL general_log = 1; SET GLOBAL general_log_file='/var/log/mysql/mysql.log'; worked for 8.0.27. It appeared that general_log_file was mandatory. – Elidaelidad

Can we set logs only for specific user ? – Intercom

Answer 2

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

Learn

Should be noted that 'table' is meant to be entered literally, and not replaced with a table name in your database. – Leap

On Mac OS X, the MySQL software installer seems to have automatically created the general_log table, with the type CSV. This means that I can also tail the corresponding CSV file: sudo tail -f -n 250 /usr/local/mysql/data/mysql/general_log.CSV – Forebode

After SETing global log_output = 'table'; is slow query will be writing to a file? I think after SET global general_log = 0; you should set previous 'log_output' value, it probable be 'FILE' – Dear

Answer 3

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;

Orozco

I've found that under Linux systemd, if you try to log to a file to /tmp/, it may be end up somewhere like /tmp/systemd-private-...-mariadb.service-.../tmp/ – Chopstick

@commonpike: I'd upvote this more often if I could ... I find myself googling your answer about once a month :) – Lantern

just to let you know, here I am again :) – Lantern

hi mate! here I am again :D. I'm so happy you provided this answer! – Lantern

@Lantern haha, I found myself googling my own answers too :-D – Orozco

@Orozco me too again, this bond will be hard to break ;) although I'm apparently switching between your answer and the linked one. hope you don't mind! https://mcmap.net/q/92563/-log-all-queries-in-mysql/… – Lantern

Answer 4

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.

Poteet

Answer 5

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

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

Blakley

In my case, the general query log cannot be enabled on shared hosting servers. I can only have the slow query log enabled, and the system administrators can provide the entries related to my account on request. – Dioptase

Answer 6

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

Horvath

Answer 7

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

Imena

It's ok to save those settings in conf file in order to be applied every time that MySQL is started, but you don't need to restart MySQL to apply this configuration. You can set it using 'SET global' like others said. – Questionless

Answer 8

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
...
...
...
...

Girhiny

Answer 9

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.

Geotectonic

Is there a bug report somewhere for this bug? – Chopstick

Answer 10

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

Mortensen

This is incorrect - that enables the slow query log, not the query log. – Prudence

I think you have to use dashes. – Salivate

Answer 11

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;

Felafel

Answer 12

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.

Koh

Answer 13

You may come across a set of Hexadecimal values, like this (argument column):

mysql> select * from mysql.general_log LIMIT 1\G
*************************** 1. row ***************************
  event_time: 2023-01-27 13:37:20.950778
   user_host: root[root] @ localhost []
   thread_id: 1434
   server_id: 1
command_type: Query
    argument: 0x73656C656374202A2066726F6D207573657273
1 row in set (0.00 sec)

so to make it readable, just use:

select a.*, convert(a.argument using utf8) from mysql.general_log a;

And the return is something like this:

mysql> select a.*, convert(a.argument using utf8) from mysql.general_log a LIMIT 1\G
*************************** 1. row ***************************
                    event_time: 2023-01-27 13:37:20.950778
                     user_host: root[root] @ localhost []
                     thread_id: 1434
                     server_id: 1
                  command_type: Query
                      argument: 0x73656C656374202A2066726F6D207573657273
convert(a.argument using utf8): select * from users
1 row in set, 1 warning (0.00 sec)

Ps: I used LIMIT 1 on examples, because my log table is too big.

Clearness

Answer 14

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.

Total

Answer 15

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.

Anthracene

Answer 16

My OS Win10, MySQL server version - 5.7

The path to my.ini

C:\ProgramData\MySQL\MySQL Server 5.7\my.ini

Just add into my.ini file

general_log_file        = C:/ProgramData/MySQL/MySQL Server 5.7/mysql.log
general_log             = 1

Esoteric