基于pt-table-checksum和pt-table-sync實現MySQL主從數據一致性校驗

    在基于MySQL邏輯復制原理的下的主從架構，經常會由于某些緣故產生主從數據不一致，從而導致主從復制進程報錯中斷。而基于定期去檢查從庫的show slave status\G的IO線程和SQL線程的狀態，只能確認當前replication是正常的，卻無法確認當前主從數據是否一致。幸好percona公司提供pt工具包，其中的pt-table-checksum和pt-table-sync相互配合，在基于一定的前提條件下，可以較好的完成主從數據一致性校驗和修復，而不會較大程度上影響線上數據庫的性能。

    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、該腳本只需部署在主庫側即可。不需要部署在從庫側。