如何启用 MySQL 查询日志？

How to enable MySQL Query Log?

提问by Feng-Chun Ting

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?

如何启用记录从客户端收到的每个 SQL 查询语句以及查询语句提交时间的 MySQL 功能？我可以在 phpmyadmin 或 NaviCat 中这样做吗？如何分析日志？

回答by Gryphius

First, Remember that this logfile can grow very large on a busy server.

首先，请记住，此日志文件在繁忙的服务器上可能会变得非常大。

For mysql < 5.1.29:

对于 mysql < 5.1.29：

To enable the query log, put this in /etc/my.cnfin the [mysqld]section

要启用查询日志，把这个/etc/my.cnf在[mysqld]节

log   = /path/to/query.log  #works for mysql < 5.1.29

Also, to enable it from MySQL console

另外，从 MySQL 控制台启用它

SET general_log = 1;

See http://dev.mysql.com/doc/refman/5.1/en/query-log.html

见http://dev.mysql.com/doc/refman/5.1/en/query-log.html

For mysql 5.1.29+

对于 mysql 5.1.29+

With mysql 5.1.29+ , the logoption is deprecated. To specify the logfile and enable logging, use this in my.cnf in the [mysqld]section:

对于 mysql 5.1.29+ ，该log选项已弃用。要指定日志文件并启用日志记录，请在 my.cnf[mysqld]部分中使用它：

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):

或者，要从 MySQL 控制台打开日志记录（还必须以某种方式指定日志文件位置，或找到默认位置）：

SET global general_log = 1;

Also note that there are additional options to log only slow queries, or those which do not use indexes.

另请注意，还有其他选项可以仅记录慢查询或不使用索引的查询。

回答by Alexandre Marcondes

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

记录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:

如果您不想或无法重新启动 MySQL 服务器，您可以在正在运行的服务器上进行如下操作：

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

• 创建您的日志表（请参阅答案）

• 在数据库上启用查询日志记录（请注意，字符串 'table' 应该按字面意思放置，而不是用任何表名替换。谢谢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;

回答by Layke

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_logtable to retrieve recent queries.

然后，您可以从我的mysql.general_log表中进行选择以检索最近的查询。

I can then do something similar to tail -fon the mysql.log, but with more refinements...

然后我可以tail -f在 mysql.log 上做类似的事情，但有更多的改进......

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.

这使我可以轻松查看我可以尝试并减少的查询。我使用 8 秒间隔来仅获取在过去 8 秒内执行的查询。

回答by commonpike

This was already in a comment, but deserves its own answer: Without editing the config files: in mysql, as root, do

这已经在评论中，但值得自己回答：无需编辑配置文件：在 mysql 中，以 root 身份执行

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;

回答by Jon

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

您可以禁用或启用常规查询日志（记录所有查询）

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

回答by shrikant

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

我还想启用 MySQL 日志文件以查看查询，并且我已按照以下说明解决了此问题

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

1. 去 /etc/mysql/mysql.conf.d
2. 打开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

3. 使用此命令重新启动 MySQL /etc/init.d/mysql restart
4. 去/var/log/mysql/检查日志

回答by Sameer Kumar Choudhary

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

回答by HimalayanCoder

On Windows you can simply go to

在 Windows 上，您只需转到

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

Insert this line in my.ini

在my.ini 中插入这一行

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

The my.inifile finally looks like this

在my.ini的文件终于看起来像这样

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

回答by croonx

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

MySQL 5.6 版本中存在错误。甚至 mysqld 显示为：

    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"

检查文件：“C:\ProgramData\MySQL\MySQL Server 5.6\my.ini”

Hope it help somebody.

希望它可以帮助某人。

回答by Kumanan

To enable the query log in MAC Machine:

在 MAC Machine 中启用查询日志：

Open the following file:

打开以下文件：

vi /private/etc/my.cnf

Set the query log url under 'mysqld' section as follows:

在 'mysqld' 部分下设置查询日志 url，如下所示：

[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 控制台启用它

mysql> SET global general_log = 1;