Mysql必读Mysql慢日志开启

《Mysql必读Mysql慢日志开启》要点：
本文介绍了Mysql必读Mysql慢日志开启，希望对您有用。如果有疑问，可以联系我们。

1.先查看目前日志输出方式

show variables like '%log_output%';MYSQL必读

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+

log_output 参数设定日志文件的输出,可选值为 TABLE, FILE ,NONE; "TABLE" 意思为设定日志分别记录到 mysql 库的 general_log 和 slow_log 表中; "FILE" 意思为记录日志到操作系统的文件中, "NONE" 意思为取消日志记录.MYSQL必读

set global log_output='FILE,TABLE';MYSQL必读

2.查看慢日志是否开启

show variables like '%slow%';MYSQL必读

+---------------------+------------------------------------+
| Variable_name       | Value                              |
+---------------------+------------------------------------+
| log_slow_queries    | ON                                 |
| slow_launch_time    | 2                                  |
| slow_query_log      | ON                                 |
| slow_query_log_file | /ssddata2/mysql/3306/logs/slow.log |
+---------------------+------------------------------------+

3.设置开启慢日志

set global log_slow_queries = on;MYSQL必读

4.查询没有index的查询记录开关

show global variables like '%indexes%';MYSQL必读

+----------------------------------------+-------+
| Variable_name                          | Value |
+----------------------------------------+-------+
| log_queries_not_using_indexes          | OFF   |
| log_throttle_queries_not_using_indexes | 0     |
+----------------------------------------+-------+

第一个参数 表示是否开启记录没有index的查询,第二个
参数用来做日志记录的流量控制,一分钟可以记录多少条,默认0是表示不限制.MYSQL必读

5.修改慢日志存储路径

set global slow_query_log_file = '/ssddata2/mysql/3306/logs/slow.log'；MYSQL必读

6.设置慢日志记录时间

show variables like "%long%"MYSQL必读

+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 5.000000 |
+-----------------+----------+

set global long_query_time = 5;MYSQL必读

slow_launch_time的设定跟慢查询日志的查询阀值设定不同,表示了thread create的一个阀值,如果thread create的时间超过了这个值,这变量slow_launch_time的值加1.
而设置Long_query_time表示超过多少秒的查询就写入日志,默认的是10s,设置为0的话表示记录所有的查询.MYSQL必读

7.管理型SQL可以通过--log-slow-admin-statements开启记录管理型慢SQL

a. The query must either not be an administrative statement, or --log-slow-adminstatements must have been specified.MYSQL必读

b. The query must have taken at least long_query_time seconds, or log_queries_not_using_indexes must be enabled and the query used no indexes for row lookups.MYSQL必读

c. The query must have examined at least min_examined_row_limit rows.MYSQL必读

d. The query must not be suppressed according to the log_throttle_queries_not_using_indexes setting.MYSQL必读

转载请注明本页网址：
http://www.vephp.com/jiaocheng/5030.html