mysql数据一致性检查报错及解决办法

pt-table-checksum报错：
1、Can't locate Digest/MD5.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at ./p
BEGIN failed--compilation aborted at ./pt-table-checksum line 789.
缺少perl-Digest-MD5
安装perl-Digest-MD5
centos下：
yum install perl-Digest-MD5 -y
ubuntu下：
apt-get install libdigest-perl-md5-perl

2、05-30T18:11:31 Cannot connect to MySQL because the Perl DBI module is not installed or not found.  Run 'perl -MDBI' to see the directories that Perl searches for DBI.

 If DBI is not installed, try:
  Debian/Ubuntu  apt-get install libdbi-perl
  RHEL/CentOS    yum install perl-DBI
  OpenSolaris    pkg install pkg:/SUNWpmdbi

3、05-30T18:12:04 Cannot connect to MySQL because the Perl DBD::mysql module is not installed or not found.  Run 'perl -MDBD::mysql' to see the directories that Perl searches for DBD::mysql.  

If DBD::mysql is not installed, try:
  Debian/Ubuntu  apt-get install libdbd-mysql-perl
  RHEL/CentOS    yum install perl-DBD-MySQL
  OpenSolaris    pgk install pkg:/SUNWapu13dbd-mysql

解决完以上错误之后，完整的pt-table-checksum语句，首次加--create-replicate-table创建checksums表，其他参数网上都有介绍：

这是指定了单张表，去掉--tables可以对整个库进行检查

pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=test\-1.checksums --create-replicate-table --databases=test\-1 --tables=ceshibiao h=172.31.73.11,u=root,p=1234,P=3306

pt-table-sync报错：
1、DBD::mysql::db selectall_arrayref failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1.checksums WHERE master_cnt <> this_cnt OR master_crc <> this_crc OR ISNUL' at line 1 [for Statement "SELECT db, tbl, CONCAT(db, '.', tbl) AS `table`, chunk, chunk_index, lower_boundary, upper_boundary, COALESCE(this_cnt-master_cnt, 0) AS cnt_diff, COALESCE(this_crc <> master_crc OR ISNULL(master_crc) <> ISNULL(this_crc), 0) AS crc_diff, this_cnt, master_cnt, this_crc, master_crc FROM mppc-cloud.checksums WHERE master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)"] at ./pt-table-sync line 5054.
Issuing rollback() due to DESTROY without explicit disconnect() of DBD::mysql::db handle ;host=172.31.73.11;mysql_read_default_group=client at ./pt-table-sync line 5054.
Issuing rollback() due to DESTROY without explicit disconnect() of DBD::mysql::db handle ;host=172.31.73.11;mysql_read_default_group=client at ./pt-table-sync line 5054.

使用--replicate=库名.checksums，库名带减号-等特殊字符，提示语法错误

解决办法：
将库名转义，例如库名为：test-1，带减号，--replicate的正确写法是：--replicate=\`test\-1\`
用esc键下面的英文点号将整个库名括起来，并将英文点号和特殊字符都加反斜杠“\”转义
这个错误在pt-table-checksum中的--databases中不必使用英文点号，直接转义特殊字符即可，例如：--databases=test\-1

2、Failed to prepare TableSyncChunk plugin: Cannot chunk table `test-1`.`ceshibiao` using the character column testid, most likely because all values start with the same character. This table must be synced separately by specifying a list of --algorithms without the Chunk algorithm at ./pt-table-sync line 4136.  while doing test-1.ceshibiao on 172.31.73.12
在默认的算法中，要保证主键字段的数据前一位有不一样字符出现，而该表的主键数据第一个字符是一样的
使用--algorithms=参数指定算法
  --algorithms=s            Algorithm to use when comparing the tables, in
                            order of preference (default Chunk,Nibble,GroupBy,
                            Stream)
解决办法：使用Nibble算法同步，亲测可行，其他算法没测试过

--algorithms=Nibble

3、Wide character in print at ./pt-table-sync line 11042.  while doing test-1.ceshibiao on 172.31.73.12

中文字符处理问题，指定字符集为utf8

完整的语句，--print打印出待同步的数据，结构为replace into的SQL语句，可以写入文本，方便验证及备份，也可以使用--execute直接执行写入从库。
pt-table-sync --algorithms=Nibble --charset=utf8 --replicate=\`test\-1\`.checksums h=172.31.73.11,u=root,p=1234 h=172.31.73.12,u=root,p=123 --print

记录一下，主要是库名带特殊字符，太坑人了。