MySQL主從復制數據一致性校驗和修復方法及自動化實現

微信掃1掃關注我的公眾號或搜索添加“MySQL技術的學習分享”，可以更快速更實時地獲得我的最新文章。

1.    引言

“MySQL主從復制”技術在互聯網行業常見高可用架構中利用非常廣泛，例如常見的1主1從復制架構、keepalived＋MySQL雙主（主從）復制架構、MHA＋1主兩從復制架構等等都利用了MySQL主從復制技術。但因主從復制是基于binlog的邏輯復制，難免出現復制數據不1致的風險，這個風險不但會引發用戶數據訪問前后不1致的風險，而且會致使后續復制出現1032、1062毛病進而引發復制架構停滯的隱患，為了及時發現并解決這個問題，我們需要定期或不定期地展開主從復制數據1致性的校驗和修復工作，那末如何實現這項工作呢？又如何實現這項工作的自動化呢？我們來探討這些問題。

2.    數據1致性校驗和修復方法

為了實現主從復制數據1致性校驗和修復，我們首先推薦兩個熱門工具，分別是percona公司的 pt-table-checksum和pt-table-sync，前者用來實現主從復制數據1致性的校驗，后者實現數據修復，將數據修復到1致。

2.1        工作原理

        pt-table-checksum通過SQL在主庫履行數據塊的校驗，再將相同的語句傳送到從庫，并在從庫上計算數據塊的校驗，最后將主從庫相同塊的校驗值進行對照，辨別主從數據是不是不1致。

pt-table-sync用來修復主從復制數據的不1致，使得它們修復到終究1致，也能夠實現多個實例或是利用雙寫或多寫的多個不相干的數據庫實例修復到1致。同時它還內部集成了pt-table-checksum的校驗功能，可以1邊校驗1邊修復，也能夠基于pt-table-checksum的計算結果來進行修復。

2.2        下載方法

     這兩個工具均包括在percona-toolkit里，線上下載地址：  https://www.percona.com/downloads/percona-toolkit/2.2.2/。

     在裝備上直接下載的指令以下，下載后解壓使用：wget https://www.percona.com/downloads/percona-toolkit/2.2.2/percona-toolkit⑵.2.2.tar.gz

2.3        校驗和修復方法

（1）在主庫創建校驗賬號

GRANTUPDATE,INSERT,DELETE,SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'hangxing'@'MasterIP'identified by 'PASSWORD';

GRANTALL ON test.* TO 'hangxing'@'MasterIP' IDENTIFIED BY 'PASSWORD';

（2）在主庫創建校驗信息表

CREATETABLE IF NOT EXISTS checksums (

db char(64)NOT NULL,

tblchar(64) NOT NULL,

chunk intNOT NULL,

chunk_timefloat NULL,

chunk_indexvarchar(200) NULL,

lower_boundarytext NULL,

upper_boundarytext NULL,

this_crcchar(40) NOT NULL,

this_cntint NOT NULL,

master_crcchar(40) NULL,

master_cntint NULL,

tstimestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY(db, tbl, chunk),

INDEXts_db_tbl (ts, db, tbl)

)ENGINE=InnoDB;

（3）判斷主鍵

若無主鍵做校驗和修復對性能影響非常重，數據校驗和修復最重要的束縛便是主健，無主鍵或唯1索引，將致使修復不成功。

主鍵判斷語句：

SELECTDISTINCT CONCAT(t.table_schema,'.',t.table_name) astbl,t.engine,IF(ISNULL(c.constraint_name),'NOPK','') AS nopk,IF(s.index_type ='FULLTEXT','FULLTEXT','') as ftidx,IF(s.index_type = 'SPATIAL','SPATIAL','') asgisidx FROM information_schema.tables AS t LEFT JOINinformation_schema.key_column_usage AS c ON (t.table_schema =c.constraint_schema AND t.table_name = c.table_name AND c.constraint_name ='PRIMARY') LEFT JOIN information_schema.statistics AS s ON (t.table_schema =s.table_schema AND t.table_name = s.table_name AND s.index_type IN('FULLTEXT','SPATIAL')) WHERE t.table_schema NOT IN('information_schema','performance_schema','mysql') AND t.table_type = 'BASETABLE' AND (t.engine <> 'InnoDB' OR c.constraint_name IS NULL ORs.index_type IN ('FULLTEXT','SPATIAL')) ORDER BY t.table_schema,t.table_name;

（4）主從數據校驗

       主從數據校驗使用pt-table-checksum實現，要在主庫上履行，履行校驗通過參數控制校驗全庫全表還是只校驗核心表。

校驗指令舉例：

./pt-table-checksum--nocheck-binlog-format --nocheck-plan --nocheck-replication-filters--replicate=test.checksums  --databases=db1--tables=tb1 -h 192.168.XXX.XX -P 3306-u'hangxing' -p'PASSOWRD' --recursion-method="processlist"

解析： 

--no-check-binlog-format 不檢查復制的binlog模式。

       --nocheck-replication-filters 不檢查復制過濾器，建議啟用。

--replicate=test.checksums 檢查結果寫入test庫的checksums表里。

--databases=db1  --tables=tb1 校驗db1庫里的tb1表，若無參數則校驗全庫全表。

-h 192.168.XXX.XX -P 3306 主庫IP地址和3306端口。

-u'hangxing' -p'PASSOWRD' 校驗賬號密碼。

--recursion-method="processlist" 用processlist的方法來發現從庫。

      履行后的輸出結果：

TS   ERRORS      DIFFS      ROWS  CHUNKS  SKIPPED   TIME  TABLE

03⑵3T15:29:17    0     1    30000       1       0  1.270 testhx1.testhx1

解析：

TS            ：完成檢查的時間。 

ERRORS     ：檢查時候產生毛病和正告的數量。 

DIFFS        ：0表示1致，大于0表示不1致。主要看這1列有沒有不1致數據。

ROWS       ：表的行數。 

CHUNKS    ：被劃分到表中的塊的數目。 

SKIPPED    ：由于毛病或正告或過大，則跳過塊的數目。 

TIME         ：履行的時間。 

TABLE       ：被檢查的表名。 

    上述輸出關鍵看DIFFS列，結果為0說明數據1致，無需進行數據修復，如果不為0則需要繼續展開數據1致性修復工作。上述語句履行后也會將詳細的內容會寫入test庫的checksums表中，可以查看這個庫表得到詳細的數據校驗信息，此表中信息內容格式舉例以下：  

        主庫的test.checksums中輸出this_crc和master_crc，無不1致。

mysql> select * fromtest.checksums;

+---------+---------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+

| db      | tbl    | chunk | chunk_time | chunk_index | lower_boundary | upper_boundary | this_crc | this_cnt | master_crc| master_cnt | ts                  |

+---------+---------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+

| testhx1 | testhx1 |     1 |  

生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈