如何在我的服务器上启用 MySQL 慢查询日志？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11606972/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I enable MySQL slow query log on my server?
提问by Juice
How do I enable slow query log on my server? I have enabled it on my local host by adding log-slow-queries =[path]in my.inifile, but don't know how to add this on my server. My server is Linux-based and has PHP version 5.2.16.
如何在我的服务器上启用慢查询日志？我已经通过添加启用了我的本地主机上log-slow-queries =[path]的my.ini文件，但不知道如何添加这个我的服务器上。我的服务器基于 Linux，PHP 版本为 5.2.16。
回答by Rituparna Kashyap
Enabling slow query log has nothing to do with PHP version. You have to enable it in the MySQL server. You can enable in two ways
启用慢查询日志与 PHP 版本无关。您必须在 MySQL 服务器中启用它。您可以通过两种方式启用
- In runtime
- During the server start
- 在运行时
- 服务器启动期间
If your server is above 5.1.6 you can set the slow query log in the runtime itself. For which you have to execute this queries.
如果您的服务器高于 5.1.6，您可以在运行时本身中设置慢查询日志。您必须为其执行此查询。
set global log_slow_queries = 1;
set global slow_query_log_file = <some file name>;
Or alternatively you can set the this options in the my.cnf/my.ini option files
或者，您可以在 my.cnf/my.ini 选项文件中设置此选项
log_slow_queries = 1;
slow_query_log_file = <some file name>;
Where the option file is changed, the MySQL server need to be restarted.
在更改选项文件的地方，需要重新启动 MySQL 服务器。
Location of the mysql option file can be found here http://dev.mysql.com/doc/refman/4.1/en/mysql-config-wizard-file-location.html
mysql 选项文件的位置可以在这里找到http://dev.mysql.com/doc/refman/4.1/en/mysql-config-wizard-file-location.html
FYI : log_slow_querieswas removed in MySQL 5.6.1 and slow_query_logis used instead.
http://dev.mysql.com/doc/refman/5.6/en/server-options.html#option_mysqld_log-slow-queries
仅供参考：log_slow_queries已在 MySQL 5.6.1 中删除并slow_query_log改为使用。
http://dev.mysql.com/doc/refman/5.6/en/server-options.html#option_mysqld_log-slow-queries
But for performance you can set the log output (option log_output) to TABLE.
Also you can have a look other slow query log options like long_query_time, log-queries-not-using-indexes
但是为了性能，您可以将日志输出（选项log_output）设置为TABLE. 您还可以查看其他慢查询日志选项，例如long_query_time，log-queries-not-using-indexes
回答by Ross Smith II
You can set it temporarily, by running the following commands:
您可以通过运行以下命令临时设置它：
set global slow_query_log = 1;
set global slow_query_log_file = '/var/log/mysql-slow.log';
but your changes will be undone when mysql is restarted.
但是当 mysql 重新启动时，您的更改将被撤消。
You can set it permanently, by adding the following to your my.cnffile:
您可以通过将以下内容添加到您的my.cnf文件中来永久设置它：
slow-query-log=1
slow-query-log-file=/var/log/mysql-slow.log
The location of my.cnfvaries by OS, but is often found in /etc/my.cnf, or /etc/mysql/my.cnf:
的位置my.cnf因操作系统而异，但通常在/etc/my.cnf, 或 中找到/etc/mysql/my.cnf：
After saving your changes, you will need to restart MySql. This can vary by OS, but here are some common examples:
保存更改后，您需要重新启动 MySql。这可能因操作系统而异，但以下是一些常见示例：
sudo /etc/init.d/mysqld restart
and
和
sudo service mysqld restart
回答by Pascal Roget
First (as root:)
首先（作为根:)
mkdir /var/log/mysql
touch /var/log/mysql/log-slow-queries.log
chown mysql:mysql -R /var/log/mysql
Then under [mysqld] in /etc/my.conf (or wherever your configuration file is:)
然后在 /etc/my.conf 中的 [mysqld] 下（或您的配置文件所在的任何位置：）
slow_query_log = 1
log-queries-not-using-indexes
long_query_time=1
log-slow-queries=/var/log/mysql/log-slow-queries.log
Next:
下一个：
service mysql restart
Finally:
最后：
mysql
mysql> SELECT SLEEP(10);
mysql> quit
Verification:
确认：
cat /var/log/mysql/log-slow-queries.log
/usr/sbin/mysqld, Version: 5.5.42-cll (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: (null)
Time Id Command Argument
# Time: 150727 0:05:17
# User@Host: root[root] @ localhost []
# Query_time: 10.000215 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1437973517;
select sleep(10);
