analysis_binlog对binlog的DML进行统计

analysis_binlog对binlog的DML进行统计

目录

• analysis_binlog对binlog的DML进行统计
  • 前言
  • 功能介绍
  • 工具安装
  • 工具使用介绍
  • 参数说明：
  • 使用实例，分析DML：
  • 使用例子2-binlog to sql：

前言

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

功能介绍

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

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

工具安装

1、克隆项目
git clone https://gitee.com/mo-shan/analysis_binlog.git
进入analysis_binlog的家目录

2、更改路径(第一次使用需要配置)
(1)更改mysqlbinlog路径
#将这里的mysqlbinlog_path改成mysqlbinlog工具的绝对路径,否则可能会因版本太低导致错误
sed -i 's#^mysqlbinlog="/data/mysql/base/bin/mysqlbinlog"#mysqlbinlog=\"/mysqlbinlog_path\"#g' bin/analysis_binlog 

eg:
sed -i 's#^mysqlbinlog="/data/mysql/base/bin/mysqlbinlog"#mysqlbinlog=\"/opt/analysis_binlog/bin/mysqlbinlog\"#g' bin/analysis_binlog 


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

eg:
sed -i 's#^work_dir=.*#work_dir=\"/opt/analysis_binlog\"#g' bin/analysis_binlog   

3、为analysis_binlog配置环境变量(选做)
chmod +x bin/analysis_binlog 
echo "export PATH=$(pwd)/bin:${PATH}" >> ${HOME}/.bashrc

工具使用介绍

root /data/git/analysis_binlog/bin >> bash analysis_binlog -h

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

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

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

使用实例，分析DML：

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  


bash /opt/analysis_binlog/bin/analysis_binlog -bfile=/data/mysqldata/3306/binlogs/binlog.001272,/data/mysqldata/3306/binlogs/binlog.001273,/data/mysqldata/3306/binlogs/binlog.001274,/data/mysqldata/3306/binlogs/binlog.001275,/data/mysqldata/3306/binlogs/binlog.001276,/data/mysqldata/3306/binlogs/binlog.001277 -bfile=binlog.001740 -w=2 -t=simple 

使用例子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


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