2012-12-30 13:20:21 
阿炯
Mysql主从数据一致的原因也许有多个，但笔者也遇到这种情况，可能是下面几种情况：1、'粗放'的授权的模式，多见于将从的访问开放给了业务系统，由于没有写权限的控制，业务系统修改了数据，导致了数据的不一致。所以授权一定要'细'，宁少勿多。
2、业务过多，导致主或从库过于繁忙，笔者就遇到过这种现象，后在分析主从的二进制时才发现这个问题：竟然有20多秒的binlog丢失了！
3、维护人员不小心的修改。
该脚本借助了pt-table-checksum 检查数据一致性的方法：主要是通过对一张表按照一个合适的索引进行全表扫描，这个脚本很执行，索引不合适他不执行，它会把一张表分为若干个trunk,比如果一张表有200W行，分为100个trunk，那么每个trunk就是有2W行，它会锁定这个trunk,进行计算checksum并匹配。
REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT ?, ?, ?, ?, ?, ?, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `a`, `b`, CONCAT(ISNULL(`a`), ISNULL(`b`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `test`.`t` /*checksum table*/
上面这个语句会在主库和从库上都执行一下，通过计算的结果来判断这个trunk的数据是否是一样的。
它主要执行的就是这条语句，1万多行的Perl脚本，就为了执行这个语句，其他的代码主要是处理一些自适应的工作。这个SQL看起来非常复杂，解释一下他的原理吧：
它会把一个trunk里面的每一行数据所有字段拼成一个String，然后对String取32位校验码，然后这个trunk内所有计算好的校验码进行异或操作,从10进制转换成16进制。从而主库和从库上都会有一个结果，叫做CRC，通过比较这个CRC判断数据是否一致。虽然操作是一个trunk一个trunk进行的，因为会对表进行全表扫描， 所以还是建议不要在白天搞这个。
系统库里面有一张表就是专门用来存每一个trunk的校验结果的。
这里借用的了‘Tokyo Cabinet’这款k-v数据库，做为对比的中间工具，提高效率。脚本的运行原理：
同时从主从库里读取相关库表记录，并对记录处理处理，生成 key-value ，其中key的内容为(用'-'连接)
database-table-composite keys
value的内容为
将该记录所有字段内容连接起来取其md5值
将从库的kv存入hash数据库，主库的kv存入文本文件中。
注：由于业务上的需要，使用分库分表技术，待查和对比的数据都位于同种库下，所以脚本里仅多了对表的分法计算。
当上述的文件及kv数据库存好后，从文件(主库)按行读取，分坼为为key及value，以'key'为key，在kv数据库(从)中查找，会有两种结果：
1、找到该记录，进而判断其'value'是否一致，相同则主从的这条记录是完全一样的。
2、无法找到这条记录。
程序将会把不存在与不相同的信息记录到文件'/home/bin/db_comp_rs.log'中，使用了绝对路径，主要是为解决在crontab环境下的存放问题，这个文件程序下次执行会移除它。可以根据它所记录的信息可以到数据进行修改：可在主数据库服上记录下这条记录，后在删除它，在插入。从库上也会执行该操作，这样可将数据在主从上保持一致。
与'pt-table-checksum'工具相比较：
pt效率更高，但结果不很明确(如果你并不想知道具体那条数据不一致的话)，本脚本可以得到具体的库表行。在分析20G数据大小、8700多张表，耗时约40分钟。
比'pt'更简单。'pt-table-checksum'有一万多行，也是使用perl写成。引入了'TokyoCabinet'这跨kv数据库，是为提高对比的效率。
#!/usr/bin/perl
use DBI;
use TokyoCabinet;
use Digest::MD5 qw(md5 md5_hex);
use Data::Dumper;
$\="\n";
##创建时间：2012-12-10
##最后修改时间：2012-12-28
#到主从数据库的连接,'phostt'='primary host'
my $db='mysql';
my ($phost,$shost)=('192.168.5.18','127.0.0.1');
my $user='dba';
my $password='www_com';
my %mydbattr=(
RaiseError=>1,
AutoCommit=>1,
RaiseError=>1
);
#对比结果存放文件
my $rs='/home/bin/db_comp_rs.log';
#建立到主从数据库的连接
$pdbh=DBI->connect("DBI:mysql:database=$db;host=$phost",$user, $password, \%mydbattr);
$sdbh=DBI->connect("DBI:mysql:database=$db;host=$shost",$user, $password, \%mydbattr);
#由于使用了分库分表，这里仅需要对比'db_corp_x'下的几种表，每种进行了'16'或'256'分表(十六进制)，并指定了能确定惟一行的组合键。
my %nd=(
1=>{table=>'t_user_',count=>256,pkey=>['Fcid','Fuid']},
2=>{table=>'t_user_dept_',count=>256,pkey=>['Fcid','Fuid','Fdept_id']},
3=>{table=>'t_corp_',count=>16,pkey=>['Fcid']},
4=>{table=>'t_corp_dept_',count=>16,pkey=>['Fcid','Fdept_id']}
);
#创建初始hash库
my $hdb=TokyoCabinet::HDB->new();
#如果上次生成的结果文件存在的话移除它
unlink $rs if -e $rs;
#将数据(从库)结果存入hash库
sub tohdb{
my $rc=shift @_;
chomp($rc);
my ($k,$v)=();
if($rc=~/(.*)\t(.*)/){
($k,$v)=($1,$2);
}
if($hdb->putkeep($k,$v)){
}else{
print "We hand $k count error on msg: ", $hdb->errmsg($hdb->ecode());
}
}
#write to file
sub tofile{
my $rc=shift @_;
print FILE $rc;
}
#对数据库进行查询，并将结果存入文件或hash数据库。
sub query{
my ($cdb,$ctable,$pkey,$table)=(@_);
my $sql=qq{select * from $ctable;};
##对主库进行查询，写入文本中。
my $psth=$pdbh->prepare($sql);
$psth->execute();
#对取出的每行，并每个字段按其顺序连接，并计算出这个连接后的字串的md5值。
#然后生成key-value对，'key'的内容为：当前库表名、联合索引键；'value'的
#内容为：上述字串的md5值。
while(my $prow=$psth->fetchrow_hashref){
my ($prex,$md5)=();
for(@$pkey){
$prex.='-'.$prow->{$_};
}
foreach (keys %$prow){
$md5.=$prow->{$_};
}
my $rc="$cdb-$ctable$prex\t".md5_hex($md5);
tofile($rc);
}
##对从库进行查询，将结果写入hash数据库中。
my $ssth=$sdbh->prepare($sql);
$ssth->execute();
while(my $prow=$ssth->fetchrow_hashref){
my ($prex,$md5)=();
for(@$pkey){
$prex.='-'.$prow->{$_};
}
foreach (keys %$prow){
$md5.=$prow->{$_};
}
my $rc="$cdb-$ctable$prex\t".md5_hex($md5);
tohdb($rc);
}
$psth->finish();
$ssth->finish();
}
#对将查询的库表进行定位，据表定义得到其后缀。
sub getme{
my ($cdb,$table,$count,$pkey)=(@_);
for(my $c=0;$c<$count;$c++){
my $tabcnt=($count>16)?sprintf("%02x",$c):sprintf("%x",$c);
my $ctable=$table.$tabcnt;
query($cdb,$ctable,$pkey,$table);
}
}
#主库数据与库数据(hash)进行对比
sub compare{
my $tfile=shift @_;
#open kv db as only read
if(!$hdb->open($tfile.'.tch',$hdb->OREADER)){
my $ecode = $hdb->ecode();
printf STDERR ("open error: %s\n", $hdb->errmsg($ecode));
}
#open file as read only
open FILE,$tfile or die $!;
my ($k,$v)=();
while(<FILE>){
chomp($_);
if($_=~/(.*)\t(.*)/){
($k,$v)=($1,$2);
}
#将对比结果写入文件
my $value = $hdb->get($k);
if(defined($value)){
if($v eq $value){
}else{
print LOGFILE "$k Not Equal";
}
}else{
print LOGFILE "$k Not Found!";
}
}
#关闭上面打开过的文件及数据库句柄。
close FILE;
printf STDERR ("close error\n") unless $hdb->close();
}
#####main#####
##程序真正从此处运行##
foreach my $k (keys %nd){
#open log file to recode results
open LOGFILE,">>$rs" or die $!;
##open hdb file
my $tfile=$nd{$k}{table};
$tfile='/home/bin/'."$tfile";#这里写了绝对的路径，主要是在crontab环境下不能正常工作。
chop($tfile);
if(!$hdb->open($tfile.'.tch',$hdb->OWRITER|$hdb->OCREAT)){
my $ecode = $hdb->ecode();
printf STDERR ("open error: %s\n", $hdb->errmsg($ecode));
}else{
#my $dbs=$hdb->fsiz();
#print "db size:$dbs\n";
}
##open comm file
open FILE, ">>$tfile" or die $!;
#对指定了的16个库进行处理
for(my $i=0;$i<16;$i++){
my $m=sprintf("%x",$i);
my $cdb="db_corp_$m";
$pdbh->do(qq{use $cdb;});
$sdbh->do(qq{use $cdb;});
getme($cdb,$nd{$k}{table},$nd{$k}{count},$nd{$k}{pkey});
}
#当正常关闭了文件及库句柄后，开始比较数据。
if((close FILE)&&($hdb->close())){
compare($tfile);
}else{
print "In main file or db not close safely";
}
#对比结束后移除相关文件，主库文件及从库的hash数据库。
unlink $tfile,"$tfile.tch";
}
#关闭文件及到数据库的连接。
END{
$pdbh->disconnect;
$sdbh->disconnect;
close LOGFILE;
}