MariaDB主从库一致性校验和数据重新同步

安装依赖

yum install -y perl perl-devel perl-Time-HiRes perl-DBI perl-DBD-MySQL perl-Digest-MD5

下载安装工具包

wget https://codeload.github.com/percona/percona-toolkit/tar.gz/v3.3.0
cd percona-toolkit-3.3.0
perl Makefile.PL
make
make test
make install

什么是pt-table-checksum

pt-table-checksum 是 Percona-Toolkit的组件之一，用于检测MySQL主、从库的数据是否一致。其原理是在主库执行基于statement的sql语句来生成主库数据块的checksum，把相同的sql语句传递到从库执行，并在从库上计算相同数据块的checksum，最后，比较主从库上相同数据块的checksum值，由此判断主从数据是否一致。检测过程根据唯一索引将表按row切分为块（chunk），以为单位计算，可以避免锁表。检测时会自动判断复制延迟、 master的负载， 超过阀值后会自动将检测暂停，减小对线上服务的影响。
pt-table-checksum 默认情况下可以应对绝大部分场景，官方说，即使上千个库、上万亿的行，它依然可以很好的工作，这源自于设计很简单，一次检查一个表，不需要太多的内存和多余的操作；必要时，pt-table-checksum 会根据服务器负载动态改变 chunk 大小，减少从库的延迟。

为了减少对数据库的干预，pt-table-checksum还会自动侦测并连接到从库，当然如果失败，可以指定--recursion-method选项来告诉从库在哪里。它的易用性还体现在，复制若有延迟，在从库 checksum 会暂停直到赶上主库的计算时间点（也通过选项--设定一个可容忍的延迟最大值，超过这个值也认为不一致）。

为了保证主数据库服务的安全，该工具实现了许多保护措施：
1）自动设置 innodb_lock_wait_timeout 为1s，避免引起
2）默认当数据库有25个以上的并发查询时，pt-table-checksum会暂停。可以设置 --max-load 选项来设置这个阀值
3）当用 Ctrl+C 停止任务后，工具会正常的完成当前 chunk 检测，下次使用 --resume 选项启动可以恢复继续下一个 chunk

校验主从一致性

主库创建账号

grant select, create, delete, insert, update, process, super, replication slave on *.* to 'check'@'%' identified by '123456';
flush privileges;

在主库上执行的一个检查主从数据一致性的命令，DSN指向的是主的地址，该工具的退出状态不为零，如果发现有任何差别，或者如果出现任何警告或错误。
注意：第一次运行的时候需要加上--create-replicate-table参数，生成checksums表！！如果不加这个参数，那么就需要在对应库下手工添加这张表了。

下面命令中的10.200.13.37是主库ip
检查的是test库下的test表的数据（当然，命令中也可以不跟表，直接检查某整个库的数据；如下去掉--tables=test表，直接检查test库的数据）

从库上校验一致性

pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=test.checksums --databases=test --tables=test h=10.200.13.37,u=check,p=123456,P=3306

参数解释：
--create-replicate-table    ：第一次运行的时候需要加上自动生成checksums表
--nocheck-replication-filters ：不检查复制过滤器，建议启用。后面可以用--databases来指定需要检查的数据库。
--no-check-binlog-format    :不检查复制的binlog模式，要是binlog模式是ROW，则会报错。
--replicate-check-only :只显示不同步的信息。
--replicate=   ：把checksum的信息写入到指定表中，建议直接写到被检查的数据库当中。 
--databases=   ：指定需要被检查的数据库，多个则用逗号隔开。
--tables=      ：指定需要被检查的表，多个用逗号隔开
h=127.0.0.1    ：Master的地址
u=root         ：用户名
p=123456       ：密码
P=3306         ：端口

Checking if all tables can be checksummed ...
Starting checksum ...
             TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
03-17T10:42:37      0      1        3          2       1       0   1.034 test.test

结果解析:
TS ：完成检查的时间。
ERRORS ：检查时候发生错误和警告的数量。
DIFFS ：0表示一致，1表示不一致。当指定--no-replicate-check时，会一直为0，当指定--replicate-check-only会显示不同的信息。
ROWS ：表的行数。
CHUNKS ：被划分到表中的块的数目。
SKIPPED ：由于错误或警告或过大，则跳过块的数目。
TIME ：执行的时间。
TABLE ：被检查的表名。

最重要的一点就是：
要在主库上授权，能让主库ip访问。这一点不能忘记！（实验证明从库上可以不授权，但最好还是从库也授权）
注意：
1）根据测试，需要一个即能登录主库，也能登录从库的账号；
2）只能指定一个host，必须为主库的IP；
3）在检查时会向表加S锁；
4）运行之前需要从库的同步IO和SQL进程是YES状态。

什么是pt-table-sync

如果通过pt-table-checksum 检查找到了不一致的数据表，那么如何同步数据呢？即如何修复MySQL主从不一致的数据，让他们保持一致性呢？
这时候可以利用另外一个工具pt-table-sync。
使用方法：
pt-table-sync: 高效的同步MySQL表之间的数据，他可以做单向和双向同步的表数据。他可以同步单个表，也可以同步整个库。它不同步表结构、索引、或任何其他模式对象。所以在修复一致性之前需要保证他们表存在。

如何使用pt-table-sync恢复数据

注意：要是表中没有唯一索引或则主键则会报错：
Can't make changes on the master because no unique index exists at /usr/local/bin/pt-table-sync line 10591.
我先添加个主键：

alter table test add id int(11) primary key AUTO_INCREMENT; # 添加自增主键

例如以上查询得知DIFFS为1可知主从数据不一致，需要修复
修复命令如下：
先master的ip，用户，密码，然后是slave的ip，用户，密码

pt-table-sync --replicate=test.checksums h=10.200.13.37,u=root,p=123456 h=10.200.13.21,u=root,p=123456 --print

REPLACE INTO `test`.`test`(`name`, `sex`, `birth`, `birthaddr`, `id`) VALUES ('bcd', 'f', '1993-10-22', 'china', '2') /*percona-toolkit src_db:test src_tbl:test src_dsn:h=10.210.13.37,p=...,u=check dst_db:test dst_tbl:test dst_dsn:h=10.210.13.21,p=...,u=check lock:1 transaction:1 changing_src:test.checksums replicate:test.checksums bidirectional:0 pid:27176 user:root host:s2b-pr-dr--5*/;
REPLACE INTO `test`.`test`(`name`, `sex`, `birth`, `birthaddr`, `id`) VALUES ('bcd', 'f', '1995-10-22', 'china', '3') /*percona-toolkit src_db:test src_tbl:test src_dsn:h=10.210.13.37,p=...,u=check dst_db:test dst_tbl:test dst_dsn:h=10.210.13.21,p=...,u=check lock:1 transaction:1 changing_src:test.checksums replicate:test.checksums bidirectional:0 pid:27176 user:root host:s2b-pr-dr--5*/;

参数解释：
--replicate= ：指定通过pt-table-checksum得到的表，这2个工具差不多都会一直用。
--databases= : 指定执行同步的数据库。
--tables= ：指定执行同步的表，多个用逗号隔开。
--sync-to-master ：指定一个DSN，即从的IP，他会通过show processlist或show slave status 去自动的找主。
h= ：服务器地址，命令里有2个ip，第一次出现的是Master的地址，第2次是Slave的地址。
u= ：帐号。
p= ：密码。
--print ：打印，但不执行命令。
--execute ：执行命令。

上面命令介绍完了，接下来开始执行修复：
通过（--print）打印出来了修复数据的sql语句，可以手动的在slave从库上执行，让他们数据保持一致性，这样比较麻烦！
可以直接在master主库上执行修复操作，通过--execute参数，如下：

pt-table-sync --replicate=test.checksums h=10.200.13.37,u=root,p=123456 h=10.200.13.21,u=root,p=123456 --execute

如上修复后，再次检查，发现主从库数据已经一致了！

pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=test.checksums --create-replicate-table --databases=test --tables=test h=10.210.13.37,u=check,p=123456,P=3306

Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
03-17T14:38:48      0      0        3          0       1       0   1.054 test.test

建议:
修复数据的时候，最好还是用--print打印出来的好，这样就可以知道那些数据有问题，可以人为的干预下。
不然直接执行了，出现问题之后更不好处理。总之还是在处理之前做好数据的备份工作。

为了确保主从数据的一致性，可以编写监控脚本，定时检查。当检查到主从数据不一致时，强制修复数据。

cat /home/pt_huanqiu.sh

#!/bin/bash
NUM=$(/usr/bin/pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=test.checksums --databases=test  h=10.200.13.37,u=check,p=123456,P=3306|awk -F" " '{print $3}'|sed -n '2p')
if [ $NUM -eq 1 ];then
  /usr/bin/pt-table-sync --replicate=test.checksums h=10.200.13.37,u=check,p=123456 h=10.200.13.21,u=check,p=123456 --print
  /usr/bin/pt-table-sync --replicate=test.checksums h=10.200.13.37,u=check,p=123456 h=10.200.13.21,u=check,p=123456 --execute
else
  echo "data is ok"
fi

定时任务：

crontab -l
#检查主从test库数据一致性
* * * * * /bin/bash -x /root/pt_test.sh > /dev/null 2>&1
* * * * * sleep 10;/bin/bash -x /root/pt_test.sh > /dev/null 2>&1
* * * * * sleep 20;/bin/bash -x /root/pt_test.sh > /dev/null 2>&1
* * * * * sleep 30;/bin/bash -x /root/pt_test.sh > /dev/null 2>&1
* * * * * sleep 40;/bin/bash -x /root/pt_test.sh > /dev/null 2>&1
* * * * * sleep 50;/bin/bash -x /root/pt_test.sh > /dev/null 2>&1

另外说一个问题：
在上面的操作中，在主库里添加pt-table-checksum检查的权限（从库可以不授权）后，进行数据一致性检查操作，会在操作的库（实例中是test数据库）下产生一个checksums表！
这张checksums表是pt-table-checksum检查过程中产生的。这张表一旦产生了，默认是删除不了的，并且这张表所在的库也默认删除不了，删除后过一会儿就又会出来。

要想删除的话，一定要先把pt-table-checksum检查前添加的权限收回！

revoke SELECT, INSERT, UPDATE, DELETE, CREATE, PROCESS, SUPER, REPLICATION SLAVE ON *.* FROM 'check'@'%';
show grants for 'check'@'%';
delete from mysql.user where user="root" and host="192.168.1.101";   //这一步其实不必操作，此步删除操作不能在上面revoke执行前进行，否则revoke回收权限失败！

权限删除后，就能成功删除checksums这张表和它所在的库了！
主库的checksums删除了，从库的这张表也会跟着删除
也就是说，checksums表一旦产生，不仅这张表默认删除不了，连同它所在的库，要是想删除它们，只能如上操作先撤销权限。