Mysql 5.7 有以下兩個設定可以協助你排查 SQL 執行的狀況以及相關指標，由於這類設定可能會影響效能上的表現，預設會是關閉的，以下簡介如何開啟設定以及相關的設定檔。
- General Query Log：
記錄下所有執行的 SQL query 供排查，開啟後通常會伴隨大量的寫入因此不建議在生產環境開啟設定，範例輸出像是以下：
/usr/local/opt/[email protected]/bin/mysqld, Version: 5.7.35-log (Homebrew). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
2022-08-06T01:24:01.134626Z 2 Query select * from users
2022-08-06T01:24:15.940952Z 2 Query SELECT `event_time`, `user_host`, `thread_id`, `server_id`, `command_type`, `argument` FROM `general_log` ORDER BY `event_time` LIMIT 0,1000
開啟 general_log
我們可以透過以下 SQL 讀取/設定目前的 gerenal_logs 設定
show variables like '%log%' -- 查詢 general_logs / log_output / general_log_file 目前設定值;
要注意的有以下三個值
- general_log：
ON/OFF開啟 general_logs 與否 - general_log_file：寫入指定目的地路徑的檔案
- log_output：
FILE/TABLE寫入目的地為檔案 或資料表（mysql.general_log）
- 透過 SQL 更改 runtime 設定
設定會立即生效不需要重啟 mysql service，但重啟 mysql 後需要重新設定
set global general_log='ON' -- 開啟 general_log 設定;
set global general_log_file='/usr/local/var/mysql/general_logs.log' -- 設定 general_log 的檔案位置（注意寫入檔案的權限必須給予 mysql user）;
set global log_output='FILE' -- 寫入至檔案;
- 透過 my.cnf 設定
重啟後 mysql 預設會依照以下路徑的先後讀取設定檔（mysql -h可以看到此提示）- /etc/my.cnf
- /etc/mysql/my.cnf
- /usr/local/etc/my.cnf
- ~/.my.cnf
[mysqld]
....
general_log=1
general_log_file='/usr/local/var/mysql/general_logs.log'
log_output=FILE
- Slow Query Log：
對比於general_log提供更好的排查方便性，可以紀錄執行超過指定的時間 SQL query 並記錄下來，範例輸出如下：
# Time: 2022-08-06T13:27:00.284584Z
# User@Host: root[root] @ localhost [127.0.0.1] Id: 5
# Query_time: 10.004269 Lock_time: 0.000279 Rows_sent: 44 Rows_examined: 44
SET timestamp=1659792420;
SHOW FULL COLUMNS FROM `articles` FROM `blog`;
開啟 slow_query_log
我們可以透過以下 SQL 讀取/設定目前的 slow_query_log 設定
show variables like '%log%' -- 查詢 slow_query_log / log_output / slow_query_log_file 目前設定值;
show variables like 'long_query_time' -- 查詢 long_query_time 目前設定值;
要注意的有以下三個值
- slow_query_log：
ON/OFF開啟 general_logs 與否 - slow_query_log_file：寫入指定目的地路徑的檔案
- log_output：
FILE/TABLE寫入目的地為檔案 或資料表（mysql.slow_log） - long_query_time：執行時間超過幾秒的 SQL 視為 slow_query
- 透過 SQL 更改 runtime 設定
設定會立即生效不需要重啟 mysql service，但重啟 mysql 後需要重新設定
set global slow_query_log='ON' -- 開啟 slow_query_log 設定;
set global slow_query_log_file='/usr/local/var/mysql/slow_query.log' -- 設定 slow_query_log 的檔案位置（注意寫入檔案的權限必須給予 mysql user）;
set global log_output='FILE' -- 寫入至檔案;
set global long_query_time=10 -- 執行超過 10 秒視為 slow query;
- 透過 my.cnf 設定
重啟後 mysql 會自動讀取設定，設定路徑如general_log
[mysqld]
....
slow_query_log=1
slow_query_log_file='/usr/local/var/mysql/slow_query.log'
log_output=FILE
long_query_time=10