mysqlフラッシュバックテーブルツール

5759 ワード

use HTTP::Date qw(time2iso str2time time2iso time2isoz);  
use POSIX;
my $SDATE = strftime("%Y-%m-%d",localtime());
if ( $#ARGV < 4 ){  
        print "please input  like  binglog-0001 '2013-07-01 00:00:00' '2013-07-02 00:00:00' dml db_name table!
"; exit(-1); }; my $binlog= $ARGV[0]; my $start_dt = $ARGV[1]; my $end_dt = $ARGV[2]; my $dml =$ARGV[3]; my $database=$ARGV[4]; my $table=$ARGV[5]; unlink("$table.$SDATE.txt"); unlink("$table.$SDATE.txt.tmp.1"); unlink("$table.$SDATE.txt.tmp.2"); unlink("$table.$SDATE.txt.tmp.3"); my @binlog=`mysqlbinlog --base64-output=decode-rows -v --start-datetime='$start_dt' --stop-datetime='$end_dt' $binlog >$table.$SDATE.txt`; use DBI; my $db_name="$database"; my $ip='127.0.0.1'; my $user="root"; my $passwd="1234567"; $dbh = DBI->connect("dbi:mysql:database=$db_name;host=$ip;port=3306",$user,$passwd) or die "can't connect to database ". DBI-errstr; $UNLOAD_SRC_DBCONN = DBI->connect("dbi:mysql:database=$db_name;host=$ip;port=3306",$user,$passwd) or die "can't connect to database ". DBI-errstr; my $hostSql = qq{SELECT column_name from information_schema.columns where table_schema='$db_name' and table_name='$table'}; my $DW_DATA_DT =""; @lstRlst1=(); @lstRlst=(); my ($COLUMN_NAME); my $selStmt = $dbh->prepare($hostSql); $selStmt->execute(); $selStmt->bind_col(1, \$COLUMN_NAME);; $selStmt->execute(); while( $selStmt->fetch() ){ print "$COLUMN_NAME
"; push (@lstRlst1 ,$COLUMN_NAME); } $selStmt->finish; $dbh->disconnect; for ($m=1;$m<=@lstRlst1 ; $m++){ $hash{"\@$m"}=$lstRlst1[$m -1 ]; }; print %hash; print "
"; sub undo_update { local $/='/*!*/;'; open (A,"){ if (( $_ =~/$dml\s+$table/i ) or ($_ =~/$dml\s+`$table`/i) or ($_ =~/$dml\s+`$database`.`$table`/i) or ($_ =~/$dml\s+$database.$table/i )){ local $/="
"; #if ($_ =~/.*?(\@[0-9]+).*/){print "\$id is $1
";}; #print $_; open DATAFH,">>$table.$SDATE.txt.tmp.1" || die "open csdn file failed:$!"; print DATAFH $_; }; }; #print DATAFH "
"; close DATAFH; print "
"; local $/="
"; open (B,"){ next unless /^###/; if ($_ =~/.*?(\@[0-9]+).*/) { my $idnum=$1; #print "\$idnum is $idnum
"; my $id=$hash{"$idnum"}; $_ =~ s/$idnum/$id/; print $_; open (C,">>","$table.$SDATE.txt.tmp.2"); print C $_; close C } else { print $_; open (C,">>","$table.$SDATE.txt.tmp.2"); print C $_;}; close C; }; close B; open (C,"){ next unless /^###/; #$_ =~ s/^###//; if ($_ =~ s/WHERE/SET/i){ print "$_";} elsif ($_ =~ s/SET/WHERE/i){ print "$_";} else{print "$_";} }; close C; }; sub undo_delete { local $/='/*!*/;'; open (A,"){ if (( $_ =~/$dml\s+from\s+$table/i ) or ($_ =~/$dml\s+from\s+`$table`/i) or ($_ =~/$dml\s+from\s+`$database`.`$table`/i) or ($_ =~/$dml\s+from\s+$database.$table/i )){ local $/="
"; #if ($_ =~/.*?(\@[0-9]+).*/){print "\$id is $1
";}; #print $_; open DATAFH,">>$table.$SDATE.txt.tmp.1" || die "open csdn file failed:$!"; print DATAFH $_; }; }; #print DATAFH "
"; close DATAFH; print "
"; local $/="
"; open (B,"){ if ($_ =~/.*?(\@[0-9]+).*/) { my $idnum=$1; #print "\$idnum is $idnum
"; my $id=$hash{"$idnum"}; $_ =~ s/$idnum/$id/; print $_; open (C,">>","$table.$SDATE.txt.tmp.2"); print C $_; close C } else { print $_; open (C,">>","$table.$SDATE.txt.tmp.2"); print C $_;}; close C; }; close B; open (C,") { next unless /^###/; next if /^$/; open (D,">>","$table.$SDATE.txt.tmp.3"); if ($_ =~ s/###\s+DELETE FROM/INSERT INTO/i){ print D $_;} elsif ($_ =~ s/###\s+WHERE/values/i){ print D $_;} elsif($_ =~ s/^###\s+//g){ print D $_;} close D; }; close C; print "-----------------------------
"; @arr=(); $sql="INSERT INTO $table VALUES"; open (E,"){ if ($_ =~ /.*?=(.*)/){push (@arr,$1) }; if ( @arr + 0 == @lstRlst1 + 0){ print "\@arr is @arr
"; for (my $m=0;$m

転載先:https://www.cnblogs.com/zhaoyangjian724/p/6198923.html