如何启用 MySQL 慢查询日志？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22609257/
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 do I enable the MySQL slow query log?
提问by John
My MySQL version details are
我的 MySQL 版本详细信息是
- Server: Localhost via UNIX socket
- Software: MySQL
- Software version: 5.0.96-community-log - MySQL Community Edition (GPL)
- Protocol version: 10
- 服务器：本地主机通过 UNIX 套接字
- 软件：MySQL
- 软件版本：5.0.96-community-log - MySQL Community Edition (GPL)
- 协议版本：10
How do I enable the MySQL slow query log?
如何启用 MySQL 慢查询日志？
回答by jmail
Version 5.1.6 and above:
5.1.6 及以上版本：
1. Enter the MySQL shell and run the following command:
1、进入MySQL shell，运行以下命令：
set global slow_query_log = 'ON';
set global slow_query_log = 'ON';
2. Enable any other desired options. Here are some common examples:
2. 启用任何其他所需的选项。下面是一些常见的例子：
Log details for queries expected to retrieve all rows instead of using an index:
预期检索所有行而不是使用索引的查询的日志详细信息：
set global log_queries_not_using_indexes = 'ON'
Set the path to the slow query log:
设置慢查询日志的路径：
set global slow_query_log_file ='/var/log/mysql/slow-query.log';
Set the amount of time a query needs to run before being logged:
设置查询在被记录之前需要运行的时间：
set global long_query_time = 20;
(default is 10 seconds)
3. Confirm the changes are active by entering the MySQL shell and running the following command:
3. 通过进入 MySQL shell 并运行以下命令来确认更改处于活动状态：
show variables like '%slow%';
Versions below 5.1.6:
低于 5.1.6 的版本：
Edit the /etc/my.cnf file with your favorite text editor vi /etc/my.cnf
Add the following line under the “[mysqld]” section. Feel free to update the path to the log file to whatever you want:
log-slow-queries=/var/log/mysql/slow-query.log
使用您喜欢的文本编辑器 vi /etc/my.cnf 编辑 /etc/my.cnf 文件
在“[mysqld]”部分下添加以下行。随意将日志文件的路径更新为您想要的任何内容：
log-slow-queries=/var/log/mysql/slow-query.log
3. Enable additional options as needed. Here are the same commonly used examples from above:
3. 根据需要启用其他选项。以下是与上面相同的常用示例：
Set the amount of time a query needs to run before being logged:
设置查询在被记录之前需要运行的时间：
`long_query_time=20
(default is 10 seconds)`
Log details for queries expected to retrieve all rows instead of using an index:
预期检索所有行而不是使用索引的查询的日志详细信息：
`log-queries-not-using-indexes`
4. Restart the MySQL service:
4、重启MySQL服务：
service mysqld restart
5. Confirm the change is active by entering the MySQL shell and running the following:
5. 通过进入 MySQL shell 并运行以下命令来确认更改处于活动状态：
show variables like '%slow%';
Update:1
更新：1
According to MySQL docs, the error #1193 occurs when you use wrong code for SQLSTATE.
根据 MySQL 文档，当您对 SQLSTATE 使用错误的代码时，会发生错误 #1193。
Message: Unknown system variable %s
And, as you can see on the same page, the SQLSTATE 99003 is not defined.
而且，正如您在同一页面上看到的那样，未定义 SQLSTATE 99003。
refer this link:
参考这个链接：
http://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html
http://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html
回答by Sathish D
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.inioption files
或者，您可以在my.cnf/my.ini选项文件中设置此选项
log_slow_queries = 1;
slow_query_log_file = <some file name>;
Refer: http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_slow_query_log_file
参考：http: //dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_slow_query_log_file
