shell腳本 mysql-binlog分析
2021-12-13 17:50:10

一.簡介

介紹
分析binlog工具，現有功能：

1. 基于業務表分析統計各個表的dml的次數。
2. 各個業務表的最后訪問時間。
3. 各dml總的次數。
4. 該binlog的事務總數。
5. 基于業務表的binlog to sql。
6. 其他功能敬請期待。

二.使用

適用：centos6+
語言：中文
注意：請先寫一個腳本來啟動java等程序

1.下載
git clone https://gitee.com/mo-shan/analysis_binlog
cd analysis_binlog

2.更改路徑，將這里的mysqlbinlog_path改成mysqlbinlog工具的絕對路徑,否則可能會因版本太低導致錯誤
sed -i 's#^mysqlbinlog=.*#mysqlbinlog="/mysqlbinlog_path"#g' bin/analysis_binlog

將這里的analysis_binlog_path改成analysis_binlog的家目錄的絕對路徑
sed -i 's#^work_dir=.*#work_dir="/analysis_binlog_path"#g' bin/analysis_binlog

為analysis_binlog配置環境變量(選做)
chmod +x bin/analysis_binlog
echo "export PATH=$(pwd)/bin:${PATH}" >> ${HOME}/.bashrc

3.查看幫助
bash analysis_binlog -h

使用測試-1

1.統計業務表的dml情況
根據需求執行

• -bfile: 指定binlog文件, 支持多個文件并行分析, 多個文件用逗號相隔, 需要并行分析時請結合-w參數使用
• -w : 指定并行數, 當需要分析多個binlog文件時該參數有效, 默認是1
• -t : 指定顯示結果的格式/內容, 供選選項有”detail|simple”. 當指定detail的時候結果較為詳細, 會打印詳細的分析過程, 消耗時間也不直觀, simple只做了統計工作
• -s : 指定排序規則, 供選選項有”insert|update|delete”. 默認會把統計結果做一個排序, 按照表的維度統計出insert update delete的次數, 并按照次數大小排序(默認insert)

注: 其他參數使用請參見幫助手冊 bash analysis_binlog -h

2.配置了環境變量使用
analysis_binlog -bfile=/data/mysql/binlog/3306/mysql-bin.000798,/data/mysql/binlog/3306/mysql-bin.000799 -w=2 -t=simple -s=update

未配置環境變量使用
bash bin/analysis_binlog -bfile=/data/mysql/binlog/3306/mysql-bin.000798,/data/mysql/binlog/3306/mysql-bin.000799 -w=2 -t=simple -s=update

3.結果查詢
分析完畢會在analysis_binlog家目錄下的res目錄下保存一個[binlog_file_name.res]文件，使用文本工具打開即可, 建議使用cat, tail, more, 如下結果展示, 會按照表的維度做個統計, 然后按照update的次數排序, Last Time表示該表的最后一次操作
cat mysql-bin.000798.res

使用測試-2

1.binlog to sql
根據需求執行

• —binlog2sql : 表示將binlog分析成sql。
• -sw : 表示將結果按照業務表的維度保存，如果是file則將所有分析結果都保存在一個文件。
• —start-datetime : 開始時間。具體使用請參照mysqlbinlog工具的—start-datetime參數的使用
• —stop-datetime : 結束時間。具體使用，請參照mysqlbinlog工具的—stop-datetime參數的使用
• —start-position : 開始的pos值。具體使用，請參照mysqlbinlog工具的—start-position參數的使用
• —stop-position : 結束的pos值。具體使用，請參照mysqlbinlog工具的—stop-position參數的使用

注: 其他參數使用請參見幫助手冊 bash analysis_binlog -h

2.執行
bash bin/analysis_binlog -bfile=/data/mysql/binlog/3306/mysql-bin.000808 --binlog2sql -sw=table --start-datetime="2019-04-21 9:27:10" --stop-datetime="2019-04-22 10:00:00" --start-postion=1510151 --stop-position=1512137

3.結果查詢
分析完畢會在analysis_binlog家目錄下的res目錄下保存一個[binlog_file_name_to_sql.res]文件，使用文本工具打開即可, 如果【—save-way=table】，則會在res目錄下創建【table】目錄，該目錄下會出現binlog_file_db.table.res的文件，這些文件就是保存了這個該binlog分析出來的sql語句, 如下結果展示
cat res/mysql-bin.000808_to_sql.res |more

提示

v_1.1版本引入新參數—record-type or -rt

該參數表示以什么方式統計，可選的方式是兩種，一是統計sql的個數，二統計事務的個數，默認是統計sql的個數。

如：某表有十行記錄，現在執行delete from t; 如果binlog是row格式，這時候記錄到binlog會是十個delete語句，但是是一個事務。這時候這個參數就起作用了。用戶可以按照需求并參考使用手冊使用該參數。但是需要注意的是如果一個事務里面存在多種dml，比如begin;insert into t select 1;update t2 set c=c+1;commit;對于這樣的事務，會將該事務記到t2的update操作，不會記錄到t的insert操作。