MySQL主從複製資料一致性校驗與修復方法及自動化實現
- 黄舟原創
- 2017-02-07 11:34:001962瀏覽
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 Sharp
在裝置上直接下載的指令如下，下載後解壓縮使用：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-formin 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
fic．脚本实现准备工作：包括账号密码的创建、建立校验结果输出表，代码可参见第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）！