作者:xuty
本文來源:原創投稿
*愛可生開源社區出品,原創內容未經授權不得隨意使用,轉載請聯繫小編並註明來源。
很多時候,當我們的業務數據產生了不正常的變化,但卻無法得知這類操作是在哪裡進行,並且如何進行,單單從程序當面排查很費力。那麼就需要通過分析數據庫日誌來得到歷史執行 SQL,根據 SQL 執行邏輯來確認代碼位置,進而確認是否是 BUG,亦或是誤操作等。
一 、binlog 簡介
binlog 是 MySQL Server 層記錄的二進制日誌文件,用於記錄 MySQL 的數據更新或者潛在更新(比如 DELETE 語句執行刪除而實際並沒有符合條件的數據),select 或 show 等不會修改數據的操作則不會記錄在 binlog 中。
通常在 binlog_format = ROW 的環境下,我們可以通過 binlog 獲取歷史的 SQL 執行記錄,前提是必須開啟
binlog_rows_query_log_events 參數(默認關閉,建議開啟),該參數可以通過
二、binlog 解析
由於 binlog 是二進制文件,所以無法直接使用文本打開,需要使用對應的解析工具才可以查看具體內容。
2.1 show binlog events
show binlog events 方式可以解析指定 binlog 日誌,但不適宜提取大量日誌,速度很慢,不建議使用。
2.2 mysqlbinlog
mysqlbinlog 是 mysql 原生自帶的 binlog 解析工具,速度快而且可以配合管道命令過濾數據,適合解析大量 binlog 文件,建議使用。
由於 windows 下面無法使用管道命令如此簡潔的提取出 SQL,所以這邊就只寫 Linux 下的使用方法。我平時的做法會將 windows 下面的 binlog 拷貝到 Linux 下,再利用 Linux 的管道命令解析。
個人常用的 Linux 下解析命令:
mysqlbinlog /data/mysql_data/bin.000008 --database EpointFrame --base64-output=decode-rows -vv --skip-gtids=true |grep -C 1 -i "delete from Audit_Orga_Specialtype" > /opt/sql.log
- /data/mysql_data/bin.000008:需要解析的 binlog 日誌。
- database:只列出該數據庫下的行數據,但無法過濾 Rows_query_event。
- base64-output=decode-rows -vv:顯示具體 SQL 語句。
- skip-gtids=true:忽略 GTID 顯示。
- grep -C 1 -i "delete from dataex_trigger_record":通過管道命令篩選出所需 SQL 及執行時間。
- /opt/sql.log:將結果導入到日誌文件,方便查看。
結果示例:
小貼士:
1. 如果不確定 SQL 格式或是無法篩選到數據,比如因為 delete from 中間冷不丁多一個空格出來,可以使用 grep 多次過濾篩選,比如 grep -C 1 -i "Rows_query" |grep -C 1 -i "Audit_Orga_Specialtype" |grep -C 1 -i "delete" 篩選對應表上的 delete 操作。
2. 觸發器執行的 SQL 不會記錄在 Rows_query_event 中,只會記錄對應的行數據。
3. --database 是無法過濾 Rows_query_event 的,只可以過濾行數據。
三、解析方式對比
對於常見的數據庫(SQL Server、Oracle、MySQL)來說,都具有類似相同的日誌來記錄歷史 SQL,不同的只是日誌的記錄方式和解析方法: