本文发表在 rolia.net 枫下论坛我有2个server, 11个table 都是要保证一样的。就是两两一定要一致。如果映射的话太麻烦
用一个script, 读出2个table, 用MD5 hash 一下，比较下异同然后打印出哪行在哪个database中有出入
很粗糟不过可以参考下。

#!/usr/bin/perl
use FindBin;
use lib "$FindBin::Bin/lib";
use strict;
use File::Basename;
use Time::Local;
use Getopt::Std;
use Carp;
use English;

use vars qw($DBCONSTR1 $DBCONSTR2);

use Digest::MD5 qw(md5 md5_hex md5_base64);

$| = 1 ;

# The hash we use to compare 2 dbs
my %hData;

my $program = $0;

# -------------------------------------------------------------------------
my $BaseName = basename ($0);
my $dirName = dirname ($0);

sub Usage {
print <<EEOF;
Usage: $BaseName -1 'first db connect string'
-2 'second db connect string'
-t 'table name'
-c 'config file name'
-v print out verbose info
-p pageout if out of sync or errors

example1:
$BaseName -1 'VXML4RBC:dtuser:passwd' \\
-2 'VXML3RBX:dtuser:password'\\
-t 'dtuser.CR_TARGET' \\
-v -p
example2:
$BaseName -c 'compareDB.cfg'\\
-t 'dtuser.CR_TARGET' \\
-v -p
EEOF
exit (1);
}

# -------------------------------------------------------------------------

my %opts;
getopts('1:2:c:t:vp', \%opts) || Usage();

if (defined($opts{c})){
# Got the config file then check if it's valid
require "$dirName/$opts{c}";
if (!defined ($DBCONSTR1) || !defined ($DBCONSTR2)){
print "The config file needs to define DBCONSTRs\n";
Usage();
}
}elsif (!defined($opts{'1'}) || !defined($opts{'2'})
|| !defined($opts{t}) ){
print "Error: parameter(s) missing. Pls refer to usage.\n";
Usage();
}else {
$DBCONSTR1 = $opts{'1'};
$DBCONSTR2 = $opts{'2'};
}

my $bVerbose;
$bVerbose = (defined $opts{v});
my $PAGEOUT = (defined $opts{p});

my ($db1,$usr1,$pswd1) = split (/:/,$DBCONSTR1);
my ($db2,$usr2,$pswd2) = split (/:/,$DBCONSTR2);
if (!defined($db1) || !defined($usr1) || !defined($pswd1)){
print $opts{'1'} . " is not valid, must be database:username:password format\n";
Usage();
}
if (!defined($db2) || !defined($usr2) || !defined($pswd2)){
print $opts{'2'} . " is not valid, must be database:username:password format\n";
Usage();
}

my ($tableName) = $opts{t};

sub Main() {

my $sReturn; #return string when cmd succeed
my ($nMax,$nMin,$a,$cnt,$i,$cnt1,$cnt2);
Execute("db2 connect to $db1 user $usr1 using $pswd1");
$sReturn = DB2("select * from $tableName");
$cnt = 0;
for $i (split(/\n/,$sReturn)){
$cnt++;
#print "$cnt: $i\n";
$a = md5_hex($i);
#print "$cnt: $a\n";
$hData{$a} = "$db1:$i";
}
$cnt1=$cnt;
Execute("db2 connect reset");
Execute("db2 terminate");

Execute("db2 connect to $db2 user $usr2 using $pswd2");
$sReturn = DB2("select * from $tableName");
$cnt = 0;
for $i (split(/\n/,$sReturn)){
$cnt++;

$a = md5_hex($i);
print "$cnt: $a" if ($bVerbose);
if (!defined ($hData{$a})){
print "<=== Not there" if ($bVerbose);
$hData{$a} = "$db2:$i";
}else {
#if that key is in both side, remove it from the hash
delete ($hData{$a});
}
print "\n" if ($bVerbose);
}
$cnt2=$cnt;
Execute("db2 connect reset");
Execute("db2 terminate");

#Check if hData has any keys, if it has that means outofsync
my $cntHash = scalar keys(%hData); #cntHash should be 0 if array is empty
if ($cntHash > 0) {
print "For $db1 and $db2, $tableName has $cntHash rows out of sync. Check $program\n";
foreach $i (keys(%hData)){
print $hData{$i}."\n";
}
if ($PAGEOUT){
PageOut("$db1 and $db2,$tableName has $cntHash rows out of sync. Check $program");
}
}else {
if ($bVerbose) {
print "1st DB: $db1 $tableName ($cnt1 rows), 2nd DB: $db2 $tableName ($cnt2 rows)";
print " are in sync.\n";
}
}
exit(0);
}

Main();

exit(0);
#THE END



# -------------------------------------------------------------------------
# String Execute(String $cmd);
# @para String $cmd
# throw exception when failed
# otherwise return the command output
# -------------------------------------------------------------------------
sub Execute{
my ($cmd) = @_;
my $sReturn;
eval {
$sReturn = RunCmd("$cmd");
};
if ($EVAL_ERROR) {
onError($EVAL_ERROR);
exit(1);
}

return $sReturn;
}

# -------------------------------------------------------------------------
# String DB2(String $cmd);
# @para String $cmd
# throw exception when failed
# otherwise return the command output
# -------------------------------------------------------------------------
sub DB2{
my ($cmd) = @_;
my $sReturn;
eval {

$sReturn = `db2 -x \"$cmd\" 2>&1`;
# $? == 1 or 256 normally means select statement with no return rows
# print "return: $? $sReturn\n";
if ($? > 1 && $? != 256) {
#something wrong
croak ($sReturn);
}
return $sReturn;
};
if ($EVAL_ERROR) {
onError($EVAL_ERROR);
exit(1);
}

return $sReturn;
}

# -------------------------------------------------------------------------
# void onError($error)
# Param: $error is the error message,
# -------------------------------------------------------------------------
sub onError{
my ($error) = @_;
print("ERROR: $error\n");
if ($PAGEOUT){
PageOut($error);
}
}

# -------------------------------------------------------------------------
# void PageOut($erro)
# Param: $error is the error message,
# -------------------------------------------------------------------------
sub PageOut{
my ($error) = @_;
`echo "CRITICAL NCC $$ NA NA $error"| logger -t NCC -p crit`;
}

# -------------------------------------------------------------------------
# String RunCmd(String $cmd);
# @para String $cmd
# throw exception when failed
# otherwise return the command output
# -------------------------------------------------------------------------
sub RunCmd {
my ($cmd) = @_;
if (!defined ($cmd)) {
croak ("RunCmd: not defined cmd to run.");
}
my $output = `$cmd 2>&1`;
if ($?) {
#something wrong
croak ($output);
}
return $output;
}更多精彩文章及讨论，请光临枫下论坛 rolia.net