开源软件名称：

analysis_binlog

开源软件地址：

https://gitee.com/mo-shan/analysis_binlog

开源软件介绍：

analysis_binlog

介绍

作为一个MySQL DBA，查看分析binlog是日常工作的一部分，不知道你是否遇到过这样的需求：一个时间段，各个表的dml统计情况。如果binlog文件很多呢？又或者负责的业务线比较多，有多个业务都有这种需求呢？其实需求很简单，但是操作起来有点头疼？
本文就是针对这类需求做一个测试。如果你在工作中也有类似的烦恼，或者经常需要批量解析binlog，这篇文章或许对你有帮助。

离线分析binlog工具，现有功能：

1、分析统计各个表的dml次数。
2、各个业务表的最后更改时间。
3、对应的 dml 总次数。
4、binlog的事务总数。
5、业务表的binlog to sql。
6、多个binlog文件可并行解析。
7、其他功能敬请期待。

git 连接：https://gitee.com/mo-shan/analysis_binlog

工具使用介绍

root /data/git/analysis_binlog/bin >> bash analysis_binlog -hanalysis_binlog v_1.3, for linux. Usage: bash analysis_binlog [OPTION]...--type=value or -t=value          The value=detail | simple                                  For example: --type=detail,-t=detail,-t=simple,-t=simple,                                  The "detail": The results displayed are more detailed, but also take more time.                                  The "simple": The results shown are simple, but save time                                  The default value is "simple". --mysqlbinlog-path or -mpath      The path of 'mysqlbinlog'                                  For example: --mysqlbinlog-path=/path/mysqbinlog,-mpath=/path/mysqlbinlog                                  The default value is 'which mysqlbinlog'. --binlog-dir or -bdir             Specify a directory for the binlog dir.                                  For example: --binlog-dir=/mysql_binlog_dir,-bdir=/mysql_binlog_dir                                  If the input is a relative path, it will be automatically modified to an absolute path.                                  The default value is "Current path". --binlog-file or -bfile           Specify a file for the binlog file, multiple files separated by ",".                                  For example: --binlog-file=/path/mysql_binlog_file,-bfile=/path/mysql_binlog_file                                               --b-file=/path/mysql_binlog_file1,/path/mysql_binlog_file1                                  If the input is a relative path, it will be automatically modified to an absolute path.                                  If this parameter is used, the "--binlog-dir or -bdir" parameter will be invalid.--sort or -s                      Sort the results for "INSERT | UPDATE | DELETE | Total"                                  The value=insert | update | delete | total                                  The default value is "total".--threads or -w                   Decompress/compress the number of concurrent. For example:--threads=8                                  This parameter works only when there are multiple files.                                  If you use this parameter, specify a valid integer, and the default value is "1".--start-datetime or -stime        Start reading the binlog at first event having a datetime equal or posterior to the argument;                                  The argument must be a date and time in the local time zone,                                  in any format accepted by the MySQL server for DATETIME and TIMESTAMP types,                                  for example: -stime="2019-04-28 11:25:56" (you should probably use quotes for your shell to set it properly).. --stop-datetime or -etime         Stop reading the binlog at first event having a datetime equal or posterior to the argument;                                  The argument must be a date and time in the local time zone,                                  in any format accepted by the MySQL server for DATETIME and TIMESTAMP types,                                  for example: -etime="2019-04-28 11:25:56" (you should probably use quotes for your shell to set it properly).                                  Applies to the first binlog passed on the command line.--start-position or -spos         Start reading the binlog at position N(Integer).                                   Applies to the first binlog passed on the command line.                                  For example: --start-position=154 or -spos=154--stop-position or -epos          Stop reading the binlog at position N(Integer).                                   Applies to the last binlog passed on the command line.                                  For example: --stop-position=154 or -epos=154--database or -d                  List entries for just this database (local log only).                                   For example: --database=db_name or -d=db_name--record-type or -rt              The value=c | count | t | trans | transaction                                   For example: --record-type=c or -rt=t                                  The "c | count"              : The statistic type is the number of times a "DML SQL" has occurred.                                   The "t | trans | transaction": The statistic type is the number of times a "DML transaction" has occurred.                                   The default value is "count". --binlog2sql or -sql              Convert binlog file to sql. At this time, the "--type or -t, --sort or -s" option will be invalid.                                  For example: --binlog2sql or -sql--save-way or -sw                 The value=table | file | all. How to save the analysis results and this option needs to be used with the a option.                                  For example: --save-way=file or -sw=table, the default value is "file".                                  file : Save the results in a file.                                  table: Save the results in different files according to the table name. These file names are called "db.table".                                  all  : The effect is equivalent to file and table.--stop                            Stop all tasks and exit.--help or -h                      Display this help and exit.

工具安装

1、克隆项目

git clone https://gitee.com/mo-shan/analysis_binlog.git

进入analysis_binlog的家目录

2、更改路径(第一次使用需要配置)

(1)更改mysqlbinlog路径

sed -i 's#^mysqlbinlog="/data/mysql/base/bin/mysqlbinlog"#mysqlbinlog=\"/mysqlbinlog_path\"#g' bin/analysis_binlog #将这里的mysqlbinlog_path改成mysqlbinlog工具的绝对路径,否则可能会因版本太低导致错误

(2)更改analysis_binlog家目录路径

sed -i 's#^work_dir=.*#work_dir=\"/analysis_binlog_path\"#g' bin/analysis_binlog #将这里的analysis_binlog_path改成analysis_binlog的家目录的绝对路径

3、为analysis_binlog配置环境变量(选做)

chmod +x bin/analysis_binlog echo "export PATH=$(pwd)/bin:${PATH}" >> ${HOME}/.bashrc

使用测试

使用例子1-统计业务表的dml情况：

1、根据需求执行
-bfile: 指定binlog文件, 支持多个文件并行分析, 多个文件用逗号相隔, 需要并行分析时请结合-w参数使用
-w : 指定并行数, 当需要分析多个binlog文件时该参数有效, 默认是1
-t : 指定显示结果的格式/内容, 供选选项有"detail|simple". 当指定detail的时候结果较为详细, 会打印详细的分析过程, 消耗时间也不直观, simple只做了统计工作
-s : 指定排序规则, 供选选项有"insert|update|delete". 默认会把统计结果做一个排序, 按照表的维度统计出insert update delete的次数, 并按照次数大小排序(默认insert)

注: 其他参数使用请参见帮助手册 bash analysis_binlog -h

(1)配置了环境变量

analysis_binlog -bfile=/data/mysql/binlog/3306/mysql-bin.000798,/data/mysql/binlog/3306/mysql-bin.000799 -w=2 -t=simple -s=update

(2)未配置环境变量

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

2、结果查询

分析完毕会在analysis_binlog家目录下的res目录下保存一个[binlog_file_name.res]文件，使用文本工具打开即可, 建议使用cat, tail, more, 如下结果展示, 会按照表的维度做个统计, 然后按照update的次数排序, Last Time表示该表的最后一次操作

root /data/git/analysis_binlog/res >> cat mysql-bin.000798.resTable                                                       Last Time                     Insert(s)      Update(s)      Delete(s)      moshan.flush_                                               190311 9:28:54                0              3475           0              ultrax.dis_common_syscache                                  190312 11:31:53               0              231            0              ultrax.dis_common_cron                                      190312 11:31:53               0              194            0              ultrax.dis_common_session                                   190312 10:38:56               6              170            5              ultrax.dis_forum_forum                                      190312 9:19:10                0              129            0              moshan.money                                                190311 9:28:37                29             80             0              ultrax.dis_common_onlinetime                                190312 10:38:42               0              48             0              ultrax.dis_forum_thread                                     190312 10:38:56               4              47             0              ultrax.dis_common_member_count                              190312 10:38:53               0              47             0              ultrax.dis_common_credit_rule_log                           190312 10:38:53               0              38             0              ultrax.dis_forum_post                                       190312 9:24:30                4              34             0              ultrax.dis_common_member_status                             190312 9:04:42                0              20             0              moshan.history_                                             190308 9:28:25                0              10             0              ice_db.server_setting_tmp                                   190304 10:34:19               564            8              0              ultrax.dis_common_process                                   190312 11:31:53               201            7              201            ultrax.dis_common_setting                                   190312 9:04:42                0              7              0              moshan.tmp_table                                            190304 17:17:21               0              7              0              ultrax.dis_ucenter_failedlogins                             190306 10:07:11               0              4              0              ultrax.dis_common_member_field_home                         190311 14:54:47               0              4              0              ultrax.dis_forum_threadcalendar                             190312 9:09:56                2              2              0              ultrax.dis_forum_attachment                                 190306 11:46:56               2              2              0              moshan.use_date                                             190304 17:12:22               0              1              0              ultrax.dis_forum_threadhot                                  190312 9:09:56                4              0              0              ultrax.dis_forum_threaddisablepos                           190311 14:54:47               1              0              0              ultrax.dis_forum_statlog                                    190312 9:04:42                304            0              0              ultrax.dis_forum_sofa                                       190311 14:54:47               4              0              0              ultrax.dis_forum_post_tableid                               190311 14:54:47               4              0              0              ultrax.dis_forum_newthread                                  190311 14:54:47               4              0              6              ultrax.dis_forum_attachment_unused                          190306 11:46:56               2              0              2              ultrax.dis_forum_attachment_8                               190306 11:46:56               1              0              0              ultrax.dis_forum_attachment_0                               190306 11:46:29               1              0              0              ultrax.dis_common_statuser                                  190311 11:40:44               4              0              4              ultrax.dis_common_searchindex                               190312 10:38:53               28             0              0              ultrax.dis_common_member_action_log                         190311 14:54:47               4              0              4              test.ttt                                                    190303 11:43:36               2              0              0              test.t_test                                                 190308 16:52:35               4              0              0              test.t_message_list                                         190313 9:30:16                307544         0              0              test.t_message_content_lately                               190313 9:30:16                307544         0              0              test.admin_user                                             190308 11:51:50               3              0              3              Trans(total)                                                Insert(s)                     Update(s)      Delete(s)      312619                                                      616270                        4565           225            root /data/git/analysis_binlog/res >>

使用例子2-binlog to sql：

1、根据需求执行
--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

root /data/git/analysis_binlog >> 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[2019-04-28 19:37:07] [INFO] [192.168.1.5] Analysing --> /data/mysql/binlog/3306/mysql-bin.000808[2019-04-28 19:37:08] [INFO] [192.168.1.5] Analysis completed --> /data/mysql/binlog/3306/mysql-bin.000808

2、结果查询分析完毕会在analysis_binlog家目录下的res目录下保存一个[binlog_file_name_to_sql.res]文件，使用文本工具打开即可, 如果【--save-way=table】，则会在res目录下创建【table】目录，该目录下会出现binlog_file_db.table.res的文件，这些文件就是保存了这个该binlog分析出来的sql语句, 如下结果展示

root /data/git/analysis_binlog >> ll restotal 836drwxrwxr-x 3 moshan moshan   4096 Apr 28 19:45 ./drwxrwxr-x 7 moshan moshan   4096 Apr 28 19:18 ../-rw-rw-r-- 1 moshan moshan      0 Apr 28 19:16 .keep-rw-r--r-- 1 root   root   789967 Apr 28 19:46 mysql-bin.000808_to_sql.resdrwxr-xr-x 2 root   root     4096 Apr 28 19:46 table/root /data/git/analysis_binlog >>root /data/git/analysis_binlog >> cat res/mysql-bin.000808_to_sql.res |moreBEGIN/*time:190417 17:36:38*/UPDATE ultrax.dis_common_member_status WHERE   @1=2   @2='192.168.88.188'   @3='172.18.55.193'   @4=-13568 (51968)   @5=1555468129   @6=1555468129   @7=1555480860   @8=0   @9=0   @10=0   @11=0   @12=0   @13=0   @14=28 SET   @1=2   @2='192.168.88.188'   @3='172.18.55.193'   @4=-8416 (57120)   @5=1555493798   @6=1555468129   @7=1555480860   @8=0   @9=0   @10=0   @11=0   @12=0   @13=0   @14=28COMMITBEGIN/*time:190417 17:36:38*/UPDATE ultrax.dis_common_member_status WHERE   @1=2   @2='192.168.88.188'   @3='172.18.55.193'   @4=-8416 (57120)   @5=1555493798root /data/git/analysis_binlog >> ll res/table/total 860drwxr-xr-x 2 root   root     4096 Apr 28 19:46 ./drwxrwxr-x 3 moshan moshan   4096 Apr 28 19:45 ../-rw-r--r-- 1 root   root   287026 Apr 28 19:46 mysql-bin.000808_moshan.flush_.log-rw-r--r-- 1 root   root    17594 Apr 28 19:46 mysql-bin.000808_moshan.info.log-rw-r--r-- 1 root   root      326 Apr 28 19:46 mysql-bin.000808_moshan.t_1.log-rw-r--r-- 1 root   root     7603 Apr 28 19:46 mysql-bin.000808_ultrax.dis_common_credit_rule_log.log-rw-r--r-- 1 root   root    53099 Apr 28 19:46 mysql-bin.000808_ultrax.dis_common_cron.log-rw-r--r-- 1 root   root     1667 Apr 28 19:46 mysql-bin.000808_ultrax.dis_common_member_action_log.log-rw-r--r-- 1 root   root    15415 Apr 28 19:46 mysql-bin.000808_ultrax.dis_common_member_count.log-rw-r--r-- 1 root   root     7079 Apr 28 19:46 mysql-bin.000808_ultrax.dis_common_member_field_home.log-rw-r--r-- 1 root   root    12329 Apr 28 19:46 mysql-bin.000808_ultrax.dis_common_member.log-rw-r--r-- 1 root   root     8336 Apr 28 19:46 mysql-bin.000808_ultrax.dis_common_member_status.log-rw-r--r-- 1 root   root     4534 Apr 28 19:46 mysql-bin.000808_ultrax.dis_common_onlinetime.log-rw-r--r-- 1 root   root    32934 Apr 28 19:46 mysql-bin.000808_ultrax.dis_common_process.log-rw-r--r-- 1 root   root    10566 Apr 28 19:46 mysql-bin.000808_ultrax.dis_common_searchindex.log-rw-r--r-- 1 root   root    40506 Apr 28 19:46 mysql-bin.000808_ultrax.dis_common_session.log-rw-r--r-- 1 root   root      484 Apr 28 19:46 mysql-bin.000808_ultrax.dis_common_setting.log-rw-r--r-- 1 root   root     3739 Apr 28 19:46 mysql-bin.000808_ultrax.dis_common_stat.log-rw-r--r-- 1 root   root      496 Apr 28 19:46 mysql-bin.000808_ultrax.dis_common_statuser.log-rw-r--r-- 1 root   root    28473 Apr 28 19:46 mysql-bin.000808_ultrax.dis_common_syscache.log-rw-r--r-- 1 root   root   204213 Apr 28 19:46 mysql-bin.000808_ultrax.dis_forum_forum.log-rw-r--r-- 1 root   root      819 Apr 28 19:46 mysql-bin.000808_ultrax.dis_forum_newthread.log-rw-r--r-- 1 root   root    11655 Apr 28 19:46 mysql-bin.000808_ultrax.dis_forum_post.log-rw-r--r-- 1 root   root      485 Apr 28 19:46 mysql-bin.000808_ultrax.dis_forum_post_tableid.log-rw-r--r-- 1 root   root      495 Apr 28 19:46 mysql-bin.000808_ultrax.dis_forum_sofa.log-rw-r--r-- 1 root   root    20746 Apr 28 19:46 mysql-bin.000808_ultrax.dis_forum_statlog.log-rw-r--r-- 1 root   root     1225 Apr 28 19:46 mysql-bin.000808_ultrax.dis_forum_threadcalendar.log-rw-r--r-- 1 root   root     1110 Apr 28 19:46 mysql-bin.000808_ultrax.dis_forum_threaddisablepos.log-rw-r--r-- 1 root   root      497 Apr 28 19:46 mysql-bin.000808_ultrax.dis_forum_threadhot.log-rw-r--r-- 1 root   root    32558 Apr 28 19:46 mysql-bin.000808_ultrax.dis_forum_thread.logroot /data/git/analysis_binlog >> cat res/table/mysql-bin.000808_moshan.t_1.logBEGIN/*time:190418 17:34:08*/INSERT INTO moshan.t_1 SET   @1=1COMMITBEGIN/*time:190418 17:34:09*/INSERT INTO moshan.t_1 SET   @1=2COMMITBEGIN/*time:190418 18:05:43*/INSERT INTO moshan.t_1 SET   @1=3   @2='a'COMMITBEGIN/*time:190418 18:06:29*/INSERT INTO moshan.t_1 SET   @1='b'   @2=4COMMITroot /data/git/analysis_binlog >>

提示：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操作。