• 增量mysql 校验程序


    cd /root/check
    v_date=`date +%Y-%m-%d`
    echo $v_date
    sed -i s/v_date/$v_date/g query.scr
    cat query.scr  | while read table str
    do
    perl unload_master.pl  "$table"  "$str"
    
    perl unload_slave.pl  "$table" "$str"
    
    var1=`sum  master-$table.TXT | awk '{print $1}'`
    var2=`sum  slave-$table.TXT | awk '{print $1}'`
    echo $var1 is $var1
    echo $var2 is $var2
              if [ "$var1" -eq "$var2" ]
                 then 
                 perl  sms.pl "master $table sum is $var1 || slave $table sum is $var2"
                 else
                 perl  sms.pl "Warning--master $table sum is $var1 || slave $table sum is $var2"
            fi
    done
    cp -r -f .query.scr query.scr 
    
    
    
    2.
    slave:/root/check# cat query.scr 
    Client              lastLoginTime >=timestamp'v_date 00:00:00' and  lastLoginTime <=timestamp'v_date 23:59:59'  order by lastLoginTime desc
    ClientSignIn        lastVisitTime  >=timestamp'v_date 00:00:00'  and lastVisitTime  <=timestamp'v_date 23:59:59'  order by lastVisitTime desc
    ClientRechargeOrder updatedTime  >=timestamp'v_date 00:00:00'  and updatedTime  <=timestamp'v_date 23:59:59'  order by updatedTime desc
    
    3.
    #!/usr/bin/perl 
    use DBI;
    $db_name='zjzc';
    $ip='x.x.x.x';
    $user="xx";
    $passwd="xx";
    $dbh="";
    $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;
       if ( $#ARGV < 1 ){
            print "please input your table name and query condition!
    ";
                    exit(-1);
                        }
    my $table_name= "$ARGV[0]"; 
    my $str= "$ARGV[1]"; 
    my $hostSql = qq{SELECT column_name  from information_schema.columns where table_schema='$db_name' and  table_name='$table_name'};
    my $DW_DATA_DT ="";
    my $datafile="master-$table_name.TXT";
    use HTTP::Date qw(time2iso str2time time2iso time2isoz);
    
    
    my @lstRlst1;
    my @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;
    my @lstRlst =  (@lstRlst1);
    ##########################################
    #=================全局变量区==========================#
    if ($#ARGV <0){
            print "请输入一个表名参数";
            exit(-1);
        }
    sub printlog
    {
      my ($LogInfo)= @_;
      my $CurrTime = time2iso(time());                   # 当前时间
      if(!defined($LogInfo) ){$LogInfo="";}
      my $StrLog="【${CurrTime}】 	 ${LogInfo} 
    "; 
      
      print $StrLog;
      #print LOGFILE $StrLog;
      }
      
    my $exportOracleSql="SELECT ";  #数据导出的sql
    for (my $m=0;$m<@lstRlst + 0 ;$m++){
     if  ($m != @lstRlst + 0 - 1){
      $exportOracleSql = "$exportOracleSql trim($lstRlst[$m])".", "
    }
    else{
    $exportOracleSql = "$exportOracleSql trim($lstRlst[$m])"}
    print "$exportOracleSql
    ";
    }
    my $exportOracleSql="$exportOracleSql from $db_name.$table_name where $str";
    print "$exportOracleSql
    ";
    
    sub Exportdata{
    	    
    	    printlog "开始导出数据!";
    	    my $exportsql=$exportOracleSql;
    	    if($exportsql eq "error"){
    	    	return -1;
    	    	}
    	     $stmt=$UNLOAD_SRC_DBCONN->prepare($exportsql);
    	    unless ($stmt){
    			printlog "
    执行prepare SQL语句出错:
    ";
    			printlog $DBI::errstr;
    			return -1;
    		}
    	       $stmt->execute;
    		if ($UNLOAD_SRC_DBCONN->err) {
    			printlog "
    执行SQL语句出错:
    "; 
    			printlog $DBI::errstr;
    			
    			return -1;
    		}
    	     my $row=0;
    	     my $size=0;
    	     my $curtime;
    
    	     
    	     
    	     my $writeflagsql;
    	     my $tmpstr="";
    	     $row=0;
    	     my $m=0;              
     open(DATAFILE,">", $datafile) || die (print "Open DATA file failed!!!
    ");
    	     while(my $Rows = $stmt->fetchrow_arrayref){
    	     	$m=0;
    	     	$tmpstr="";
    	     	foreach(@$Rows){
    	     		$tmpstr=$tmpstr.$Rows->[$m]."|";
    	     		$m++;
    	     	}
    	     	print DATAFILE $tmpstr.$DW_DATA_DT."
    ";
    	     	$row++;
    	     	if(($row%10000) == 0){
    	     		printlog "已导出数据$row条!";
    	     	}	     	     	
    	    }
         	
            	$stmt->finish;
            #	print FLAGFILE $datafile,"
    ";
            #	print FLAGFILE $row,"
    ";
            	close(DATAFILE);
            #  close(FLAGFILE);
    
            	$curtime=time2iso(time());
              printlog "数据已成功导出!";
              printlog "一共导出数据${row}条";
                    
              return 1;	
    	
    	}
    Exportdata

  • 相关阅读:
    在mysql中计算百分比
    给指定的div增加滚动条
    Java高效编程之三【类和接口】
    Linux(CentOS) 如何查看当前占用CPU或内存最多的K个进程
    MapReduce:详解Shuffle过程
    Java高效编程之二【对所有对象都通用的方法】
    Java高效编程之一【创建和销毁对象】
    ANT命令总结(转载)
    linux 压缩文件的命令总结
    Cloudera CDH 、Impala本地通过Parcel安装配置详解
  • 原文地址:https://www.cnblogs.com/hzcya1995/p/13351607.html
Copyright © 2020-2023  润新知