perl：mysql binlog iud （insert、update、delete）分析 小脚本：实用程序

 1 #!/usr/bin/perl
 2 # utf-8
 3 
 4 use strict;
 5 use POSIX;
 6 use Time::HiRes qw/sleep time/;
 7 
 8 $| = 1;
 9 
10 my $line='# -----------------------------------------------------------------------';
11 my $debug= 0 ;
12 
13 ##-----------------------------------------------------------------------
14 sub now  {strftime("%Y-%m-%d %H:%M:%S", localtime());}
15 sub debug{if ($debug) {print "\n", now(), " : debug: ", (@_), "";}}
16 sub info {print "\n", now(), " : info : ", (@_), "";}
17 sub error{print "\n", now(), " : error: ", (@_), "";}
18 sub warn {print "\n", now(), " : warn : ", (@_), "";}
19 
20 sub qid{"`".lc($_[0])."`";}
21 
22 ##-----------------------------------------------------------------------
23 my %tables=();
24 my $table="DEFAULT";
25 my $top=10;
26 
27 info("开始处理，请稍等 ...");
28 
29 ##-----------------------------------------------------------------------
30 if (defined($ARGV[0])){
31     $top=abs($ARGV[0]);
32 }
33 
34 my @rows;
35 while (my $row=<STDIN>) {
36     debug $row;
37     if ((my $tab) = ($row =~ m/Table_map: (\S+)/)){
38         #debug($tab);
39 
40         $table=$tab;
41         if (not exists($tables{$table})) {$tables{$table}={('Insert'=>0,'Update'=>0,'Delete'=>0,'IUD'=>0)};}
42     }elsif ($row =~ m/(Write|Update|Delete)_rows: table id.*STMT_END_F$/) {
43         debug ("match : ", $1 );
44 
45         if    ($1 eq 'Write') {$tables{$table}{'Insert'}++;}
46         elsif ($1 eq 'Update'){$tables{$table}{'Update'}++;}
47         elsif ($1 eq 'Delete'){$tables{$table}{'Delete'}++;}
48         
49         $tables{$table}{'IUD'}++;
50     }else{
51         ;
52     }
53 }
54 
55 ##-----------------------------------------------------------------------
56 
57 info($line);
58 my $c=0;
59 for my $tab ( sort { ($tables{$a}{'IUD'} <=> $tables{$b}{'IUD'})*(-1) } keys %tables) {
60     $c++;
61     info($tab, " => { IUD => ", $tables{$tab}{'IUD'},
62                     ", Insert => ", $tables{$tab}{'Insert'},
63                     ", Update => ", $tables{$tab}{'Update'},
64                     ", Delete => ", $tables{$tab}{'Delete'},
65                     " }");
66     if ($c>$top){last;}
67 }
68 
69 ##-----------------------------------------------------------------------
70 info($line);
71 info("处理完成.\n");
72 exit 0;

使用方式：

1 # 本地，或 Remote。通过管道传递给脚本程序
2 mysqlbinlog -R -h主机 -u用户 -p密码 （其他可选项）--base64-output=decode-rows --start-datetime="2023-12-05 14:45" -t  mysql-bin.000540  | ./mysqlbinlog-iud-analysis_v1.pl 

#或者，直接"<"入 mysqlbinlog处理后的文件
./mysqlbinlog-iud-analysis_v1.pl 7 <mysqlbinlog-output.log 

结果如下：

2023-12-05 15:23:19 : info : 开始处理，请稍等 ...
2023-12-05 15:23:20 : info : # -----------------------------------------------------------------------
2023-12-05 15:23:20 : info : `test`.`t1` => { IUD => 23131, Insert => 0, Update => 23131, Delete => 0 }
2023-12-05 15:23:20 : info : `test2`.`ta` => { IUD => 4407, Insert => 1474, Update => 2933, Delete => 0 }
2023-12-05 15:23:20 : info : `test2`.`tb` => { IUD => 2961, Insert => 2940, Update => 21, Delete => 0 }
2023-12-05 15:23:20 : info : `test`.`t2` => { IUD => 2682, Insert => 2682, Update => 0, Delete => 0 }
2023-12-05 15:23:20 : info : `test`.`t3` => { IUD => 1482, Insert => 4, Update => 1478, Delete => 0 }
2023-12-05 15:23:20 : info : `test`.`td` => { IUD => 1468, Insert => 0, Update => 1468, Delete => 0 }
2023-12-05 15:23:20 : info : `test`.`t4` => { IUD => 1461, Insert => 1, Update => 1460, Delete => 0 }
2023-12-05 15:23:20 : info : `test`.`t5` => { IUD => 1017, Insert => 201, Update => 634, Delete => 182 }
2023-12-05 15:23:20 : info : # -----------------------------------------------------------------------
2023-12-05 15:23:20 : info : 处理完成.