How to Log All mysql queries into log file?

Question

I am using Mysql 5.6.12 under Wamp server environment. Now I want to log All queries into ".log" file, the queries which are running by PHP or from PHPMyAdmin, I want to log them...

score 23 · Accepted Answer · answered Apr 03 '14 at 14:48

23

[mysqld]
# Set Slow Query Log
long_query_time = 1
slow_query_log = 1
slow_query_log_file = /usr/log/slowquery.log
log_queries_not_using_indexes = 1

#Set General Log
general_log = on
general_log_file=/usr/log/general.log

Note that enabling general_log on a production server has overhead you should avoid it. You can check problematic queries from slow log.

answered Apr 03 '14 at 14:48

Mahesh Patil

3,056
2
16
23

1

For me, I had to use "general_log = 1" instead of "general_log = on". – GuyPaddock Dec 22 '14 at 00:09
Worked on Wamp Server, using mysql 5.6.7. The only thing I had to change were the paths to tje files, in my case, C:\wamp\logs\mysql_query.log . – Mário Rodrigues Mar 19 '15 at 16:07
Why all these config values? Just general_log and general_log_file is enough. – laurent Aug 25 '17 at 13:12

score 14 · Answer 2 · answered Feb 18 '16 at 23:16

14

Since this is the type of thing you probably only want to do temporarily, it may be useful to do this from the shell instead of via the config file:

> set global general_log_file = "/var/log/mysql/queries.log";
> set global general_log = "ON";
[wait some time, hit some pages, whatever]
> set global general_log = "OFF";

answered Feb 18 '16 at 23:16

Xiong Chiamiov

260
3
7

Note, that you need root privileges in the DB for these instructions, which is not a surprise. – Csaba Toth Feb 13 '23 at 02:49

Vérace · Answer 3 · 2018-05-29T05:03:33.853

7

Put these two lines in my.cnf.

[mysqld]

general_log     = on
general_log_file=/users/ugrad/linehanp/mydb/logfile.txt

This will log all queries to the server, from any source, not just PHP/PHPMyAdmin.

Be careful though - enabling the general log can place a heavy load on your server. To be used sparingly for short periods/debugging only.

The documentation is available here. Fro there:

To disable or enable the general query log or change the log file name at runtime, use the global general_log and general_log_file system variables. Set general_log to 0 (or OFF) to disable the log or to 1 (or ON) to enable it. Set general_log_file to specify the name of the log file.

So,

general_log     = on

and

general_log     = 1

are synonyms!

edited May 29 '18 at 05:03

answered Mar 31 '14 at 08:15

Vérace

29,825
9
70
84

For me, I had to use "general_log = 1" instead of "general_log = on". – GuyPaddock Dec 22 '14 at 00:09
@GuyPaddock what version of MySQL are you using? – Vérace Dec 22 '14 at 17:29
Is this the .my.cnf file in a user's home directory? Or /etc/mysql/my.cnf , which on my system is a symlink to /etc/mysql/mysql.cnf ? – Flimm Aug 15 '23 at 06:55
On my system (Ubuntu), the correct file to edit was /etc/mysql/mysql.conf.d/mysqld.cnf – Flimm Aug 15 '23 at 07:02

How to Log All mysql queries into log file?

3 Answers3

Linked