1.????引言
“MySQL主從復制”技術在互聯網行業常見高可用架構中應用非常廣泛，例如常見的一主一從復制架構、keepalived＋MySQL雙主（主從）復制架構、MHA＋一主兩從復制架構等等都應用了MySQL主從復制技術。但因主從復制是基于binlog的邏輯復制，難免出現復制數據不一致的風險，這個風險不但會引起用戶數據訪問前后不一致的風險，而且會導致后續復制出現1032、1062錯誤進而引起復制架構停滯的隱患，為了及時發現并解決這個問題，我們需要定期或不定期地開展主從復制數據一致性的校驗和修復工作，那么如何實現這項工作呢？又如何實現這項工作的自動化呢？我們來探討這些問題。
?
2.????數據一致性校驗和修復方法
為了實現主從復制數據一致性校驗和修復，我們首先推薦兩個熱門工具，分別是percona公司的?pt-table-checksum和pt-table-sync，前者用來實現主從復制數據一致性的校驗，后者實現數據修復，將數據修復到一致。
2.1????????工作原理
? ? ? ? pt-table-checksum通過SQL在主庫執行數據塊的校驗，再將相同的語句傳送到從庫，并在從庫上計算數據塊的校驗，最后將主從庫相同塊的校驗值進行對比，辨別主從數據是否不一致。
pt-table-sync用來修復主從復制數據的不一致，使得它們修復到最終一致，也可以實現多個實例或者是應用雙寫或多寫的多個不相關的數據庫實例修復到一致。同時它還內部集成了pt-table-checksum的校驗功能，可以一邊校驗一邊修復，也可以基于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.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）判斷主鍵
若無主鍵做校驗和修復對性能影響非常重，數據校驗和修復最重要的約束便是主健，無主鍵或唯一索引，將導致修復不成功。
主鍵判斷語句：
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-23T15:29:17????0?????1????30000???????1???????0??1.270?testhx1.testhx1
解析：
TS? ? ? ? ? ??：完成檢查的時間。?
ERRORS? ? ?：檢查時候發生錯誤和警告的數量。?
DIFFS? ? ? ??：0表示一致，大于0表示不一致。主要看這一列有無不一致數據。
ROWS? ? ? ?：表的行數。?
CHUNKS? ??：被劃分到表中的塊的數目。?
SKIPPED? ??：由于錯誤或警告或過大，則跳過塊的數目。?
TIME? ? ? ? ?：執行的時間。?
TABLE? ? ? ?：被檢查的表名。?
? ? 上述輸出關鍵看DIFFS列，結果為0說明數據一致，無需進行數據修復，如果不為0則需要繼續開展數據一致性修復工作。上述語句執行后也會將詳細的內容會寫入test庫的checksums表中，可以查看這個庫表得到詳細的數據校驗信息，此表中信息內容格式舉例如下：??
? ? ? ??主庫的test.checksums中輸出this_crc和master_crc，無不一致。
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?|??0.003661?|?NULL????????|?NULL???|?NULL|?cac6c46f|?4?|?cac6c46f??|??4?|?2016-03-23?15:29:16?| +---------+---------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------
————+
1 row in set (0.00 sec)
從庫的test.checksums中輸出this_crc和master_crc，不一致。
mysql>select?*?from?checksums; +---------+---------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+ |db??????|?tbl?????|?chunk?|?chunk_time?|?chunk_index?|lower_boundary?|?upper_boundary?|?this_crc?|this_cnt?|master_crc?|?master_cnt|ts????????????????| +---------+---------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+ |testhx1?|?testhx1?|?????1?|???0.003661?|?NULL????|?NULL???|?NULL??|?7c2e5f75|??5?|?cac6c46f??|??4?|?2016-03-23?15:29:16?| +---------+---------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+ 1row?in?set?(0.00?sec)
?
（5）主從數據修復
用pt-table-checksum工具確定確實存在數據不一致的情況下開始修復數據，數據修復使用工具pt-table-sync，內帶校驗功能，但前提是修復的表必須要有主鍵，這個工具也要在主庫上執行。
方法1:語句量大的情況下將修復的語句導入到sql文件中，再直接導入執行
在主庫用pt-table-sync打印出修復不一致數據的SQL，后將修復語句在從庫執行。
舉例：
pt-table-sync?--print--sync-to-master?h='SlaveIP',P=3306,u=hangxing,p='PASSWORD'?--databases=db1--tables=tb1?>?/tmp/repair.sql
方法2:語句量不大的情況下，將修復的語句print出來，再execute
舉例：
打印數據修復語句
pt-table-sync--print?--sync-to-master?h='SlaveIP',P=3306,u=hangxing,p='?PASSWORD?'--databases=testhx1?--tables=testhx1 DELETE?FROM`testhx1`.`testhx1`?WHERE?`id`='11'?LIMIT?1?/*percona-toolkit?src_db:testhx1src_tbl:testhx1?src_dsn:P=3306,h=’MasterIP’, p=...,u=checksums?dst_db:testhx1dst_tbl:testhx1?dst_dsn:P=3306,h='SlaveIP',p=...,u=checksums?lock:1transaction:1?changing_src:1?replicate:0?bidirectional:0? pid:24745?user:hangxinghost:XXXXXXXXXX*/; REPLACEINTO?`testhx1`.`testhx1`(`name`,?`age`,?`id`)?VALUES?('bobby',?'6',?'7')/*percona-toolkit? src_db:testhx1?src_tbl:testhx1?src_dsn:P=3306,h=’MasterIP’, p=...,u=hangxingdst_db:testhx1?dst_tbl:testhx1?dst_dsn:P=3306,h=’SlaveIP’,p=...,u=hangxinglock:1?transaction:1?changing_src:1?replicate:0?bidirectional:0? pid:24745user:root?host:?XXXXXXXXXX?*/;REPLACEINTO?`testhx1`.`testhx1`(`name`,?`age`,?`id`)?VALUES?('lily',?'5',?'9')/*percona-toolkit? src_db:testhx1?src_tbl:testhx1?src_dsn:P=3306,h=’MasterIP’,p=...,u=hangxing?? dst_db:testhx1?dst_tbl:testhx1dst_dsn:P=3306,h=’SlaveIP’,p=...,u=hangxing?lock:1?transaction:1?changing_src:1replicate:0?bidirectional:0? pid:24745?user:root?host:?XXXXXXXXXX?*/;
執行數據修復語句
pt-table-sync–execute –sync-to-master h=’SlaveIP’,P=3306,u=hangxing,p=’PASSWORD’–databases=testhx1 –tables=testhx1
（6）再次校驗
???上述修復完成之后，需要再次執行一次數據校驗，確保數據成功修復，校驗方法同（4）主從數據校驗。
2.4?值得注意的點
（1）校驗修復工作每月定期開展；
（2）主從復制架構在割接操作前后均需執行數據校驗和修復工作；
（3）主從復制出現故障后要開展數據校驗和修復工作；
（4）校驗修復需在業務低谷期進行，CPU利用率超過60%時不建議做數據校驗和修復；
（5）校驗和修復必須在主庫進行；
（6）數據庫的表要有主鍵，否則校驗效率極差，并且修復不成功。
?
3.數據一致性校驗和修復的自動化實現
理解上述方法后，我們可以順利完成主從復制數據一致性的校驗和修復工作，但是這項工作在MySQL主從復制架構維護中開展頻率較高，包括定期和各種不定期的情況，每次都手工開展耗時耗力，并且容易出現人為錯誤及隱患，因此，我們考慮將這項工作通過腳本實現自動化。
3.1前提準備
創建校驗賬號，創建校驗結果輸出表，配置兩臺主機的ssh免密碼登錄。
3.2自動化實現
(1)部署自動化腳本和定時任務
理解數據一致性校驗和修復的全部原理和詳細步驟，將其轉化為多個自動化腳本，分別部署在主從庫上，每月定期執行可通過在主庫制定crontab定時任務調用主從庫腳本實現，不定期執行可通過手動調用主從庫部署的自動化腳本來實現。
（2）自動化腳本實現步驟
a.將DB相關信息賦予對應參數，如賬戶密碼、IP、端口、常用指令等等
dbuser=XXXX dbpasswd="XXXXX" port=3306 mysql_commend="mysql-u${dbuser}?-p${dbpasswd}?-P${port}" master_ip=XXXXX slave_ip=XXXXX password="XXXXX" date=`date+%Y%m%d` logfile="XXXXX" hostname=`XXXXX`
b.檢查ssh免密碼登錄是否成功；
ssh_status=`XXXXX` ????if?[?$ssh_status?!=?$hostname?];?then ????echo?-e?"nthe?ssh?should?berepair"?>$logfile ????exit else ????echo?-e?"nthe?ssh?is?ok">$logfile fi
c．腳本實現準備工作：包括賬號密碼的創建、建立校驗結果輸出表，代碼可參見第2小節；
d．將主庫的腳本執行校驗和主鍵判斷寫成聯合SQL語句，實現剔除無主鍵表的所有表的自動數據校驗，并將結果存入所建表中；
selectXXXXX?NOT?IN?XXXXX
e．從庫部署檢查校驗結果輸出表的腳本，主庫執行d后自動登錄從庫調用這個腳本，實現對從庫上輸出表中校驗字段的對比如master_crc?和?this_crc，找到數據不一致的表，并且通過執行調用修復工具的指令實現不一致數據修復語句的print；
master_cnt?this_cnt?OR?master_crc??this_crc?OR?isnull(master_crc)?isnull(this_crc))
f．print結果自動存儲從庫的某個路徑文件下；
intooutfile?'/tmp/execute_sql.sh'
g．主庫自動登錄從庫scp獲取語句修復文件；
scp/tmp/execute_sql.sh?root@$master_ip:/tmp/execute_sql.sh
h．主庫上自動執行修復語句；
sh/tmp/execute_sql.sh
? ? ? ?i.清理掉各個中間文件，中間表等
??? ? ?上述內容記錄了該項工作的自動化實現思路及部分實現要點，自動化便是通過在這個思路的基礎上編寫主從庫部署的腳本來實現，目前已親測成功，已實現自動化的數據校驗和修復，說明上述思路正確。
4.結語
? ? 本文分享了MySQL復制數據一致性校驗和修復的詳細步驟及其自動化實現思路和方法，對MySQL復制架構運維中該項工作的實施及其自動化具有較好的借鑒意義。
以上就是MySQL主從復制數據一致性校驗和修復方法及自動化實現的內容，更多相關內容請關注PHP中文網（www.php.cn）！
