代码拉取完成,页面将自动刷新
离线分析binlog工具,现有功能:
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.
git clone https://gitee.com/mo-shan/analysis_binlog.git
进入analysis_binlog的家目录
(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的家目录的绝对路径
chmod +x bin/analysis_binlog
echo "export PATH=$(pwd)/bin:${PATH}" >> ${HOME}/.bashrc
注: 其他参数使用请参见帮助手册 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
分析完毕会在analysis_binlog家目录下的res目录下保存一个[binlog_file_name.res]文件,使用文本工具打开即可, 建议使用cat, tail, more, 如下结果展示, 会按照表的维度做个统计, 然后按照update的次数排序, Last Time表示该表的最后一次操作
root /data/git/analysis_binlog/res >> cat mysql-bin.000798.res
Table 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 >>
注: 其他参数使用请参见帮助手册 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
root /data/git/analysis_binlog >> ll res
total 836
drwxrwxr-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.res
drwxr-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 |more
BEGIN
/*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=28
COMMIT
BEGIN
/*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=1555493798
root /data/git/analysis_binlog >> ll res/table/
total 860
drwxr-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.log
root /data/git/analysis_binlog >> cat res/table/mysql-bin.000808_moshan.t_1.log
BEGIN
/*time:190418 17:34:08*/
INSERT INTO moshan.t_1
SET
@1=1
COMMIT
BEGIN
/*time:190418 17:34:09*/
INSERT INTO moshan.t_1
SET
@1=2
COMMIT
BEGIN
/*time:190418 18:05:43*/
INSERT INTO moshan.t_1
SET
@1=3
@2='a'
COMMIT
BEGIN
/*time:190418 18:06:29*/
INSERT INTO moshan.t_1
SET
@1='b'
@2=4
COMMIT
root /data/git/analysis_binlog >>
提示:v_1.1版本引入新参数--record-type or -rt
此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。
1. 开源生态
2. 协作、人、软件
3. 评估模型