基於pt-table-checksum和pt-table-sync實現MySQL主從資料一致性校正

標籤：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 themaster 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 theresults on every detected replica:pt-table-checksumThis tool is focused on finding data differences efficiently. If any data is different, you can resolve the problem withpt-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_filefiif [ -e $diff_table ]then        rm -fr $diff_tablefiif [ -e $sync_sql ]then        rm -fr $sync_sqlfi### 初始化checksums表 ###$mysql_master << EOF >/dev/null 2>&1CREATE 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>&1select t.TABLE_SCHEMA,t.TABLE_NAME from information_schema.tables tinner join information_schema.statistics s on t.TABLE_SCHEMA=s.TABLE_SCHEMA and t.TABLE_NAME=s.TABLE_NAMEinner join information_schema.key_column_usage kon t.TABLE_SCHEMA=k.TABLE_SCHEMA and t.TABLE_NAME=k.TABLE_NAMEwhere 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>&1done### 在slave端拼接產生修複的命令集，然後執行產生相應的SQL語句$mysql_slave << EOF 1>$diff_table 2>/dev/nullSELECT 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 ;EOFfor 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_sqldone### 在master側執行差異SQL,通過複製修複slave側的資料差異 ###$mysql_master << EOF >/dev/null 2>&1set 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主從資料一致性校正