如何启用 MySQL 查询日志?

2021-11-20 00:00:00 logging mysql

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

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?

推荐答案

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

对于 mysql <5.1.29:

要启用查询日志，请将其放在[mysqld]部分的/etc/my.cnf

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

另外，从 MySQL 控制台启用它

Also, to enable it from MySQL console

SET general_log = 1;

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

对于 mysql 5.1.29+

在 mysql 5.1.29+ 中，不推荐使用 log 选项.要指定日志文件并启用日志记录，请在 [mysqld] 部分的 my.cnf 中使用它:

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

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

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.

相关文章