基于pt-table-checksum和pt-table-sync实现MySQL主从数据一致性校验

    在基于MySQL逻辑复制原理的下的主从架构，经常会由于某些缘故产生主从数据不一致，从而导致主从复制进程报错中断。而基于定期去检查从库的show slave status\G的IO线程和SQL线程的状态，只能确认当前replication是正常的，却无法确认当前主从数据是否一致。幸好percona公司提供pt工具包，其中的pt-table-checksum和pt-table-sync相互配合，在基于一定的前提条件下，可以较好的完成主从数据一致性校验和修复，而不会较大程度上影响线上数据库的性能。

创新互联公司专业为企业提供新城网站建设、新城做网站、新城网站设计、新城网站制作等企业网站建设、网页设计与制作、新城企业网站模板建站服务，10余年新城做网站经验，不只是建网站，更提供有价值的思路和整体网络服务。

    pt-table-checksum的官方文档介绍如下：   

pt-table-checksum performs an online replication consistency check by executing checksum queries on the master,
which produces different results on replicas that are inconsistent with the master. The optional DSN specifies the
master host. The tool’s “EXIT STATUS” is non-zero if any differences are found, or if any warnings or errors occur.
The following command will connect to the replication master on localhost, checksum every table, and report the
results on every detected replica:
pt-table-checksum
This tool is focused on finding data differences efficiently. If any data is different, you can resolve the problem with
pt-table-sync.

     pt-table-checksum其实作为校验工具，只负责检测数据的不一致。至于差异数据的修复，而交由pt-table-sync去处理。

    使用pt-table-checksum和pt-table-sync工具的前提条件：

    1、表必须有主键or唯一索引

    2、要求binlog格式为statement。如果线上数据库采用的是binlog日志格式是row的话，可以加  --no-check-binlog-format来规避。

    3、不能有存储过程、触发器、event

    4、不建议修复有外键约束的表

    pt-table-checksum原理可以查阅官方文档或者在测试环境下开启general_log，执行一次pt-table-checksum后翻查其生成的日志即可。基本原理就是在主库创建一个checksums表，存放每个chunk的校验值。通过将表按照主键or唯一索引进行排序，按自适应的行记录数生成若干个chunk，将每个行记录串起来转成字符串，计算CRC32值，然后将该chunk的校验值记录到checksums表中。而这些SQL操作都会以statement的方式传送到从库从而执行相同的操作，如果表的数据有不一致的情况，相应的chunk的校验值也会不一致。

    校验&修复的脚本如下：

#!/bin/sh
##单向主从架构的话，master_ip是主库的ip地址，slave_ip是从库的ip地址；双向主从架构的话，master_ip是以本库数据为准的主库ip地址，slave_ip是数据被修正的备选主库ip地址。
master_ip="192.168.124.131"     
slave_ip="192.168.124.132"
port="3306"
user="checksums"
password="checksums"
pt_sync="/usr/bin/pt-table-sync"
pt_check="/usr/bin/pt-table-checksum"
mysql="/usr/local/mysql/bin/mysql"
mysql_master="$mysql -u$user -p$password -h$master_ip -P$port"
mysql_slave="$mysql -u$user -p$password -h$slave_ip -P$port -N "
table_file="/tmp/table.txt"
diff_table="/tmp/diff.txt"
sync_sql="/tmp/sync.sql"
### 清理环境 ###
if [ -e $table_file ]
then
        rm -fr $table_file
fi
if [ -e $diff_table ]
then
        rm -fr $diff_table
fi
if [ -e $sync_sql ]
then
        rm -fr $sync_sql
fi
### 初始化checksums表 ###
$mysql_master << EOF >/dev/null 2>&1
CREATE DATABASE IF NOT EXISTS PERCONA;
USE PERCONA;
CREATE TABLE IF NOT EXISTS checksums (
db char(64) NOT NULL,
tbl char(64) NOT NULL,
chunk int NOT NULL,
chunk_time float NULL,
chunk_index varchar(200) NULL,
lower_boundary text NULL,
upper_boundary text NULL,
this_crc char(40) NOT NULL,
this_cnt int NOT NULL,
master_crc char(40) NULL,
master_cnt int NULL,
ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (db, tbl, chunk),
INDEX ts_db_tbl (ts, db, tbl)
) ENGINE=InnoDB;
EOF
### 过滤出不包含外键约束、拥有主键or唯一索引的Innodb表。而触发器、存储过程和event需要人工自行过滤掉所涉及的表 ###
$mysql_master << EOF >/dev/null 2>&1
select t.TABLE_SCHEMA,t.TABLE_NAME from information_schema.tables t
inner join information_schema.statistics s 
on t.TABLE_SCHEMA=s.TABLE_SCHEMA and t.TABLE_NAME=s.TABLE_NAME
inner join information_schema.key_column_usage k
on t.TABLE_SCHEMA=k.TABLE_SCHEMA and t.TABLE_NAME=k.TABLE_NAME
where t.TABLE_TYPE='BASE TABLE' and t.ENGINE='InnoDB' and s.NON_UNIQUE=0 and k.POSITION_IN_UNIQUE_CONSTRAINT is null and concat(k.TABLE_SCHEMA,'.',k.TABLE_NAME) not in (select concat(k.TABLE_SCHEMA,'.',k.TABLE_NAME) from information_schema.key_column_usage k where k.POSITION_IN_UNIQUE_CONSTRAINT is not null) and t.TABLE_SCHEMA not in ('mysql','percona','sys','information_schema','performance_schema') group by t.TABLE_SCHEMA,t.TABLE_NAME into outfile "$table_file" FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
EOF
### 调用pt-table-checksum，做数据差异比对,将结果写入percona.checksums表 ###
for i in $(cat $table_file)
do
        db=$(echo $i|awk -F\| '{print $1}')
        tb=$(echo $i|awk -F\| '{print $2}')
        $pt_check --set-vars innodb_lock_wait_timeout=120,binlog_format='statement' -u$user -p$password -h$master_ip -P$port --databases=$db --tables=$tb >/dev/null 2>&1
done
### 在slave端拼接生成修复的命令集，然后执行生成相应的SQL语句
$mysql_slave << EOF 1>$diff_table 2>/dev/null
SELECT concat(db,'|',tbl) FROM percona.checksums where ( master_cnt <> this_cnt or master_crc <> this_crc or ISNULL(master_crc)<>ISNULL(this_crc)) GROUP BY db, tbl ;
EOF
for i in $(cat $diff_table)
do
        db=$(echo $i|awk -F\| '{print $1}')
        tb=$(echo $i|awk -F\| '{print $2}')
        $pt_sync --print --sync-to-master h=$slave_ip,P=$port,u=$user,p="$password" --databases="$db" --tables="$tb" >> $sync_sql
done
### 在master侧执行差异SQL,通过复制修复slave侧的数据差异 ###
$mysql_master << EOF >/dev/null 2>&1
set tx_isolation="REPEATABLE-READ";
set binlog_format=statement;
source $sync_sql;
EOF
## 清理临时文件 ###
rm -fr $sync_sql $table_file $diff_table

      执行该脚本之前，需要满足几个前提：

      1、创建专用的帐号用于校验和修复。

            帐号创建语句：GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, PROCESS, FILE, SUPER, REPLICATION SLAVE ON *.* TO 'checksums'@'%'

             PS：如果checksums用户的登录IP有限制的话，可以只配置主库和从库的IP即可。

      2、目前脚本只能自动过滤出拥有唯一索引or主键、不带外键约束的innodb表，有触发器、存储过程和event所涉及的表，需要人工剔除。

      3、该脚本只需部署在主库侧即可。不需要部署在从库侧。

分享名称：基于pt-table-checksum和pt-table-sync实现MySQL主从数据一致性校验
文章转载：http://shouzuofang.com/article/jgcogp.html