Binlog中，除了具體的SQL，其實，還包含了很多有價值的資訊，如，
- 事務的開始時間，
- 事務的結束時間，
- 事務的開始位置點，
- 事務的結束位置點，
- 操作的開始時間（一個事務通常會包含多個操作），
- 表的操作資訊，如哪些表執行了哪些操作，
拿到上面這些資訊，我們可以做哪些事情呢？
- 基于事務的開始位置點和結束位置點，我們可以得到事務的大小，知道了事務的大小，可用來判斷它是否為大事務，是否是導致主從延遲的原因，
- 基于事務的開始時間和結束時間，我們可以得到事務的持續時間，
- 通過分析表的操作資訊，可間接定位出線上的熱點表，
開發了一個簡單的Binlog分析工具-binlog_summary.py，可提取上面這些資訊，并在此基礎上，進行一些常見分析，
1. 下載地址
https://github.com/slowtech/dba-toolkit/blob/master/MySQL/binlog_summary.py
2. 引數決議
# python binlog_summary.py --help
usage: binlog_summary.py [-h] [-f BINLOG_TEXT_FILE] [--new]
[-c {tps,opr,transaction}] [--start START_DATETIME]
[--stop STOP_DATETIME] [--sort SORT_CONDITION] [-e]
[--limit LIMIT]
optional arguments:
-h, --help show this help message and exit
-f BINLOG_TEXT_FILE, --file BINLOG_TEXT_FILE
Binlog text file, not the Raw binary file
--new Make a fresh start
-c {tps,opr,transaction}, --command {tps,opr,transaction}
Command type: [tps, opr, transaction],tps: transaction
per second, opr: dml per table, transaction: show
transaction info
--start START_DATETIME
Start datetime, for example: 2004-12-25 11:25:56
--stop STOP_DATETIME Stop datetime, for example: 2004-12-25 11:25:56
--sort SORT_CONDITION
Sort condition: time or size, you can use it when
command type is transaction
-e, --extend Show transaction info in detail,you can use it when
command type is transaction
--limit LIMIT Limit the number of rows to display
其中，
-
-f：Binlog通過mysqlbinlog決議后的文本檔案，注意，是文本檔案，不是Binlog原始檔案，使用mysqlbinlog決議時，建議指定-v（顯示Pseudo SQL，即偽SQL）和–base64-output=decode-rows（不會顯示Base64的編碼結果）這兩個引數，這樣，生成的文本檔案才是最小的，相應地，binlog_summary.py決議起來也是最快的，具體命令如下：
# mysqlbinlog --base64-output=decode-rows -v mysql-bin.000001 > /tmp/mysql-bin.000001.txt -
–new：工具的分析結果默認是存盤在sqlite3資料庫中，如果指定了–new，會洗掉之前創建的sqlite3資料庫，在對一個新的Binlog進行分析時需指定該引數，
-
-c：指定命令的型別，支持的命令型別有：tps：分析實體的TPS資訊，
opr：分析表的操作情況，transaction：分析事務資訊， -
–start：開始時間，分析指定時間段的日志，
-
–stop：結束時間，
-
–sort：排序條件，當命令型別是transaction時，默認是按照事務的執行順序輸出的，可指定size，按事務大小排序，也可指定time，按事務的持續時間排序，
-
-e：當命令型別是transaction時，指定該引數會輸出每個事務的詳細操作資訊，
-
–limit：限制輸出的行數，
3. 常見用法
3.1 分析實體的TPS資訊
# python binlog_summary.py -f /tmp/mysql-bin.000001.txt -c tps --limit 5
COMMIT_TIME TPS
2021-04-17 08:12:14 1
2021-04-17 08:12:15 7
2021-04-17 08:12:16 12
2021-04-17 08:12:17 12
2021-04-17 08:12:18 9
注意，這里的TPS是基于事務的提交時間來統計的，
如此細粒度的TPS資訊，只能通過Binlog來獲取，一般的監控很難做到這一點，
如果要對TPS進行排序，可通過管道 + sort，如，
# python binlog_summary.py -f /tmp/mysql-bin.000001.txt -c tps | sort -k 3 -n
其中，-k 3是對第三列進行排序，-n是按照數值（默認是字符）的大小進行排序，也可指定-r引數，反向排序，
3.2 分析表的操作情況
# python binlog_summary.py -f /tmp/mysql-bin.000001.txt -c opr --limit 5
TABLE_NAME DML_TYPE NUMS
sbtest.sbtest4 INSERT 609526
sbtest.sbtest6 INSERT 543658
sbtest.sbtest2 INSERT 309701
sbtest.sbtest7 INSERT 309651
sbtest.sbtest5 INSERT 309606
這里的NUMS是執行次數，
3.3 分析Binlog中最大的5個事務，
# python binlog_summary.py -f /tmp/mysql-bin.000001.txt -c transaction --sort size --limit 5
TRANS_NAME BEGIN_TIME COMMIT_TIME BEGIN_LOG_POS COMMIT_LOG_POS DURATION_TIME SIZE
t62562 2021-04-17 08:30:01 2021-04-17 08:32:31 734265229 867878401 150 133613172
t62561 2021-04-17 08:29:19 2021-04-17 08:29:19 677048698 734265148 0 57216450
t62563 2021-04-17 08:33:26 2021-04-17 08:33:50 867878482 925094932 24 57216450
t62564 2021-04-17 08:34:21 2021-04-17 08:34:21 925095013 971504525 0 46409512
t62565 2021-04-17 08:34:58 2021-04-17 08:34:58 971504606 1016178117 0 44673511
其中，
- TRANS_NAME：事務編號，
- BEGIN_TIME：事務開始時間，
- COMMIT_TIME：事務提交時間，
- BEGIN_LOG_POS：事務的開始位置點，
- COMMIT_LOG_POS：事務的結束位置點，
- DURATION_TIME：事務的持續時間，單位秒，其中，DURATION_TIME = COMMIT_TIME - BEGIN_TIME，
- SIZE：事務的大小，單位位元組，其中，SIZE = COMMIT_LOG_POS - BEGIN_LOG_POS，
拿到事務的大小，我們可以粗略地判斷這個Binlog中是否存在大事務，如果要進一步分析事務中包含哪些操作，需加上–extend，如，
# python binlog_summary.py -f /tmp/mysql-bin.000001.txt -c transaction --sort size --extend --limit 5
TRANS_NAME BEGIN_TIME COMMIT_TIME BEGIN_LOG_POS COMMIT_LOG_POS DURATION_TIME SIZE
t62562 2021-04-17 08:30:01 2021-04-17 08:32:31 734265229 867878401 150 133613172
├── sbtest.sbtest2 DELETE 200000
├── sbtest.sbtest3 UPDATE 100000
├── sbtest.sbtest4 INSERT 300000
t62561 2021-04-17 08:29:19 2021-04-17 08:29:19 677048698 734265148 0 57216450
├── sbtest.sbtest1 DELETE 300000
t62563 2021-04-17 08:33:26 2021-04-17 08:33:50 867878482 925094932 24 57216450
├── sbtest.sbtest6 DELETE 300000
t62564 2021-04-17 08:34:21 2021-04-17 08:34:21 925095013 971504525 0 46409512
├── sbtest.sbtest5 UPDATE 121324
t62565 2021-04-17 08:34:58 2021-04-17 08:34:58 971504606 1016178117 0 44673511
├── sbtest.sbtest6 INSERT 234234
4. 實作思路
binlog_summary.py是分析Binlog經過mysqlbinlog決議后的文本檔案，具體來說，
-
逐行分析決議后的文本，
為了保證分析的高效，只會分析含有以下關鍵詞的行，其它行則直接跳過，
match_sub_strings=["use", "# at", "server id", "BEGIN", "insert", "delete", "update", "DELETE", "INSERT", "UPDATE","COMMIT"] if not any(each_str in line for each_str in match_sub_strings): continue -
分析后的基礎資料，會存在sqlite3資料庫中，為什么要這么設計呢？
- 基礎資料可重復使用，畢竟binlog_summary.py支持多種維度的分析，如果每進行一次分析，都要重新決議一次文本，不夠高效也沒必要，
- sqlite3支持SQL，簡單的分組聚合還是用SQL比較方便，而且，sqlite3本身就足夠輕量級，無需安裝部署，同時，Python中通過sqlite3庫就可直接操作，
接下來，再來說說，為什么是分析Binlog經過mysqlbinlog決議后的文本檔案，而不是基于MySQL復制協議，直接分析Binlog呢？基于MySQL復制協議，這種方式有個弊端，就是通用性不夠，每出一個新的版本，都要進行相應的適配，
基于文本來分析，很多人可能會覺得不高效，
實際測驗了下，分析一個1G的Binlog，大概3min，也不算慢，
# time mysqlbinlog --base64-output=decode-rows -v mysql-bin.000001 > /tmp/mysql-bin.000001.txt
real 0m28.148s
user 0m13.821s
sys 0m2.588s
# time python binlog_summary.py -f /tmp/mysql-bin.000001.txt --new -c tps --limit 1
COMMIT_TIME TPS
2021-04-17 08:12:14 1
real 2m31.682s
user 2m2.253s
sys 0m2.269s
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/292676.html
標籤：其他
上一篇：最新版Spring Cloud Alibaba微服務架構-Ribbon負載均衡篇
下一篇：交換機和路由器有什么區別