《Mysql必讀Mysql慢日志開啟》要點：
本文介紹了Mysql必讀Mysql慢日志開啟，希望對您有用。如果有疑問，可以聯(lián)系我們。

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

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

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

log_output 參數(shù)設(shè)定日志文件的輸出,可選值為 TABLE, FILE ,NONE; "TABLE" 意思為設(shè)定日志分別記錄到 mysql 庫的 general_log 和 slow_log 表中; "FILE" 意思為記錄日志到操作系統(tǒng)的文件中, "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.設(shè)置開啟慢日志

set global log_slow_queries = on;MYSQL必讀

4.查詢沒有index的查詢記錄開關(guān)

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

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

第一個參數(shù) 表示是否開啟記錄沒有index的查詢,第二個
參數(shù)用來做日志記錄的流量控制,一分鐘可以記錄多少條,默認0是表示不限制.MYSQL必讀

5.修改慢日志存儲路徑

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

6.設(shè)置慢日志記錄時間

show variables like "%long%"MYSQL必讀

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

set global long_query_time = 5;MYSQL必讀

slow_launch_time的設(shè)定跟慢查詢?nèi)罩镜牟樵冮y值設(shè)定不同,表示了thread create的一個閥值,如果thread create的時間超過了這個值,這變量slow_launch_time的值加1.
而設(shè)置Long_query_time表示超過多少秒的查詢就寫入日志,默認的是10s,設(shè)置為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必讀

轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/5030.html