一次从日志文件抽取sql的经历

前因后果就不提了，总之是一次操作失误，需要恢复数据，但是数据库并没有备份，只好从日志里边抽取数据库操作的记录。这些脚本原型都来自于cu。

1.从日志中抽取sql语句

#!/usr/bin/perl
open FH,$ARGV[0] or die "Can't open!\n";

while ($line = ) {
        if ($line =~ /update t_trans_jnl set/) {
                chomp $line;
                sub_write_1($line);
        }
       
}
close FH;
print "Finish ...";

sub sub_write_1{
        my $i = shift;
        open TFH,">>$ARGV[1]" or die "Can't creat!\n";
        print TFH $i."\n";
        close TFH;
}

2.删除一些不需要的内容

open FH,$ARGV[0] or die "Can't open!\n";
while ($line = ) {
    if ( $line =~ /^  \[uppleRealBaseDB\.mysql\.c-205\]update db sql\[/ ) {
     	chomp $line;
        $line =~ s/^  \[uppleRealBaseDB\.mysql\.c-205\]update db sql\[//g;
      	chomp $line;
    }
    if($line=~/\]$/)
    {
      	$line =~ s/\]$/\;/g;
       	chomp $line;
    }
       
    if($line=~/t_trans_jnl/){
      	$line =~ s/t_trans_jnl/t_trans_jnl_his/g;
    }      
    chomp $line;       
  
    sub_write_1($line);
 
}
close FH;
print "Finish ...";

sub sub_write_1{
        my $i = shift;
        open TFH,">>$ARGV[1]" or die "Can't creat!\n";
        print TFH $i."\n";
        close TFH;
}

3.继续过滤，这是为了生成一个插表的文件，上面的只是update

open FH,$ARGV[0] or die "Can't open!\n";

while ($line = ) {
    if ( $line =~ /and  msgcode like \'/ ) {
             chomp $line;
             $line =~s /^.*where//g;
             chomp $line;
             sub_write_1($line);
    }
         
     
 
}
close FH;
print "Finish ...";

sub sub_write_1{
        my $i = shift;
        open TFH,">>$ARGV[1]" or die "Can't creat!\n";
        print TFH $i."\n";
        close TFH;
}

4.生成插表文件

open FH,$ARGV[0] or die "Can't open!\n";

my $cap2_flag = 0;

while ($line = ) {

        chomp $line;
        $line =~ s/termid like/\(/g;
        chomp $line;
        $line =~ s/and  mid like/,/g;
        chomp $line;
        $line =~ s/and  term_seq like/,/g;
        chomp $line;
        $line =~ s/and  msgcode like/,/g;
        chomp $line;
        $line =~ s/and b3_pcode like/,/g;
        chomp $line;
      	$line =~ s/;/\),/g;
             
       	sub_write_1($line);
 
}
close FH;
print "Finish ...";

sub sub_write_1{
               
        my $i = shift;
        open TFH,">>$ARGV[1]" or die "Can't creat!\n";
        if ($cap2_flag==0){
           	print TFH "insert into t_trans_jnl_his(termid,mid,term_seq,msgcode,b3_pcode) values\n";
                    $cap2_flag=1;     
        }else{
            print TFH $i."\n";
		}
        close TFH;
}

大体上是这样的步骤，最后还用ue做了一下替换，比方说插入语句的最后一个,替换成;