基于pt-table-checksum和pt-table-sync實(shí)現(xiàn)MySQL主從數(shù)據(jù)一致性校驗(yàn)

    在基于MySQL邏輯復(fù)制原理的下的主從架構(gòu)，經(jīng)常會(huì)由于某些緣故產(chǎn)生主從數(shù)據(jù)不一致，從而導(dǎo)致主從復(fù)制進(jìn)程報(bào)錯(cuò)中斷。而基于定期去檢查從庫(kù)的show slave status\G的IO線程和SQL線程的狀態(tài)，只能確認(rèn)當(dāng)前replication是正常的，卻無法確認(rèn)當(dāng)前主從數(shù)據(jù)是否一致。幸好percona公司提供pt工具包，其中的pt-table-checksum和pt-table-sync相互配合，在基于一定的前提條件下，可以較好的完成主從數(shù)據(jù)一致性校驗(yàn)和修復(fù)，而不會(huì)較大程度上影響線上數(shù)據(jù)庫(kù)的性能。

    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其實(shí)作為校驗(yàn)工具，只負(fù)責(zé)檢測(cè)數(shù)據(jù)的不一致。至于差異數(shù)據(jù)的修復(fù)，而交由pt-table-sync去處理。

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

    1、表必須有主鍵or唯一索引

    2、要求binlog格式為statement。如果線上數(shù)據(jù)庫(kù)采用的是binlog日志格式是row的話，可以加  --no-check-binlog-format來規(guī)避。

    3、不能有存儲(chǔ)過程、觸發(fā)器、event

    4、不建議修復(fù)有外鍵約束的表

    pt-table-checksum原理可以查閱官方文檔或者在測(cè)試環(huán)境下開啟general_log，執(zhí)行一次pt-table-checksum后翻查其生成的日志即可?；驹砭褪窃谥鲙?kù)創(chuàng)建一個(gè)checksums表，存放每個(gè)chunk的校驗(yàn)值。通過將表按照主鍵or唯一索引進(jìn)行排序，按自適應(yīng)的行記錄數(shù)生成若干個(gè)chunk，將每個(gè)行記錄串起來轉(zhuǎn)成字符串，計(jì)算CRC32值，然后將該chunk的校驗(yàn)值記錄到checksums表中。而這些SQL操作都會(huì)以statement的方式傳送到從庫(kù)從而執(zhí)行相同的操作，如果表的數(shù)據(jù)有不一致的情況，相應(yīng)的chunk的校驗(yàn)值也會(huì)不一致。

    校驗(yàn)&修復(fù)的腳本如下：

#!/bin/sh
##單向主從架構(gòu)的話，master_ip是主庫(kù)的ip地址，slave_ip是從庫(kù)的ip地址；雙向主從架構(gòu)的話，master_ip是以本庫(kù)數(shù)據(jù)為準(zhǔn)的主庫(kù)ip地址，slave_ip是數(shù)據(jù)被修正的備選主庫(kù)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"
### 清理環(huán)境 ###
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表。而觸發(fā)器、存儲(chǔ)過程和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
### 調(diào)用pt-table-checksum，做數(shù)據(jù)差異比對(duì),將結(jié)果寫入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端拼接生成修復(fù)的命令集，然后執(zhí)行生成相應(yīng)的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側(cè)執(zhí)行差異SQL,通過復(fù)制修復(fù)slave側(cè)的數(shù)據(jù)差異 ###
$mysql_master << EOF >/dev/null 2>&1
set tx_isolation="REPEATABLE-READ";
set binlog_format=statement;
source $sync_sql;
EOF
## 清理臨時(shí)文件 ###
rm -fr $sync_sql $table_file $diff_table

      執(zhí)行該腳本之前，需要滿足幾個(gè)前提：

      1、創(chuàng)建專用的帳號(hào)用于校驗(yàn)和修復(fù)。

            帳號(hào)創(chuàng)建語句：GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, PROCESS, FILE, SUPER, REPLICATION SLAVE ON *.* TO 'checksums'@'%'

             PS：如果checksums用戶的登錄IP有限制的話，可以只配置主庫(kù)和從庫(kù)的IP即可。

      2、目前腳本只能自動(dòng)過濾出擁有唯一索引or主鍵、不帶外鍵約束的innodb表，有觸發(fā)器、存儲(chǔ)過程和event所涉及的表，需要人工剔除。

      3、該腳本只需部署在主庫(kù)側(cè)即可。不需要部署在從庫(kù)側(cè)。