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