當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

MySQL之一致性检测及数据同步

發布時間：2025/6/16 数据库 35 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySQL之一致性检测及数据同步小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

前言

當MySQL數據庫架構使用主從時，由于事務創建和提交的順序并不一致。例如我們先創建A事務，在創建B事務，但我們在提交的時候可能先提交B事務，在提交A事務。此時我們從服務在重放二進制日志時，會先執行B，再執行A。這樣就會導致最終主從數據不一致。在一些對數據一致性要求高的場景下，我們就需要檢測主從數據的一致性。接下來我們將會介紹percona工具包中的pt-table-checksum做一致性檢測，以及pt-table-sync同步數據。

一. 部署percona tookit

下載安裝包
~]# wget https://www.percona.com/downloads/percona-toolkit/3.0.5/binary/redhat/7/x86_64/percona-toolkit-3.0.5-1.el7.x86_64.rpm

安裝
~]# yum install percona-toolkit-3.0.5-1.el7.x86_64.rpm

二. 一致性檢測工具pt-table-checksum

1. 一致性檢測原理

pt-table-checksum是percona-toolkit系列工具中的一個，用于檢測主從數據庫中的一致性。一次只工作在一張表上，會將主庫上的表切割成一個一個的chunk，這種切割要依賴于表上的index。所以在檢測時不需要大量的內存和前期工作，而且還可以在數據尖峰是通過指數衰減算法，快速選擇適合的chunk大小，減輕服務器壓力。將表切割成一個一個的chunk接下來會對chunk進行checksum，并記錄下來。并對比從庫上的checksum是否一致，從而判斷數據是否一致。并且在檢測過程中會自動判斷master負載，以及slave延遲，一旦超過閾值就會停止下來。對于線上的環境影響不大。而且他還可以隨時停止，只需在重啟時加入--resume就會從上次的檢測重新開始。接下來介紹其詳細過程。

1) 表結構的檢查

表結構的檢查也稱之為單行數據checksum值的計算，并獲取每一列的數據類型，把所有數據類型都轉化為字符串，然后用concat_ws()函數進行連接，由此計算出該行的checksum值。checksum默認采用crc32計算。

2) 數據塊checksum的計算

pt-table-sync會智能分析表上的索引，然后把表的數據split成若干個chunk，計算的時候以chunk為單位。可以理解為把chunk內所有行的數據拼接起來，再計算crc32的值，即得到該chunk的checksum值。所以它把checksum結果存儲到統計表，然后把執行過的sql語句記錄到binlog中，任務就算完成。然后從服務器會讀取到binlog的SQL語句依次執行，并將checksum保存在表中。

2. 校驗

1）授權

Create database pt CHARACTER SET utf8;
GRANT UPDATE,INSERT,DELETE,SELECT, PROCESS, SUPER, REPLICATION SLAVE ON . TO 'checksums'@'192.168.239.135' identified by 'check_pass';
GRANT ALL ON pt.* TO 'checksums'@'192.168.%';
在這里我們創建了一個數據庫，用于存儲一致性檢測生成的數據。主從同步工具pt-table-sync根據此數據庫中的數據查找有不一致的數據，并同步。其中135為主，136為從。

2）校驗（Master服務器運行）

pt-table-checksum --nocheck-binlog-format --nocheck-plan --nocheck-replication-filters --replicate=pt.checksums --set-vars innodb_lock_wait_timeout=120 --databases newtable -u'checksums' -p'checksums' -h192.168.239.135
#-h -u -p -P -S -d 連接信息
#--nocheck-replication-filters 檢測中忽略mysql 配置參數binlog_ignore_db等。
#--nocheck-binlog-format 不檢測日志格式，默認是使用statement 格式，如果binlog的日志與默認不同將會檢測失敗。所以我們會關閉binlog格式的檢測
#--replicate 指定checksum 存儲的db和表，如test.checksum
#--chunk-size， --chunk-size-limit 用于指定檢測塊的大小。可控性更強，Number of rows to select for each checksum query。默認是1000。對于--chunk-size-limit來說，他可以避免當主服務器為空，而從服務數據很大時造成的從延時過大。
#--lock-wait-timeout innodb 鎖的超時設定，默認為1
#--max-load ： Examine SHOW GLOBAL STATUS after every chunk, and pause if any status variables are higher than their thresholds
#--replicate-check-only 只輸出數據不一致的信息。
#--resume: pt-table-checksum停止后，使用此參數可以接著停止的地方開始。

注意：
pt-table-checksum前提假設主從的表和表結構是一致的，如果不一致pt-table-checksum會失敗

三. 數據同步工具pt-table-sync

pt-table-sync是MySQL數據同步工具，并不僅僅是同步主從數據，任意主機上的表都可以同步。
pt-table-checksum只是校驗，所以它把checksum結果存儲到統計表，然后把執行過的sql語句記錄到binlog中，任務就算完成。
pt-table-sync則不同，工作流程如下：

a) 連接到主庫：pt工具連接到主庫，然后自動發現主庫的所有從庫。默認采用show full processlist來查找從庫，但是這只有在主從實例端口相同的情況下才有效
b) 在主庫上對每一個chunk，在校驗時加上for update鎖。一旦獲得鎖，就記錄下當前主庫的show master status值。在從庫上執行select master_pos_wait()函數，等待從庫sql線程執行到show master status得到的位置。以此保證，主從上關于這個chunk的內容均不再改變。
c) 對這個chunk執行checksum，然后與主庫的checksum進行比較
d) 如果checksum相同，說明主從數據一致，就繼續下一個chunk
e) 如果checksum不同，說明該chunk有不一致。深入chunk內部，逐行計算checksum并比較
f) 如果發現某行不一致，則標記下來。繼續檢測剩余行，直到這個chunk結束
g) 對找到的主從不一致的行，采用replace into(如果數據不存在則插入,存在則更新,避免了主鍵約束)語句，在主庫執行一遍以生成該行全量的binlog,并同步到從庫，這會以主庫數據為基準來修復從庫;對于主庫有的行而從庫沒有的行，采用replace在主庫上插入(必須不能是insert);于從庫有而主庫沒有的行,通過在主庫執行delete來刪除(pt-table-sync強烈建議所有的數據修復都只在主庫進行,而不建議直接修改從庫數據;但是也有特例，后面會講到)。
h) 直到修復該chunk所有不一致的行。繼續檢查和修復下一個chunk
i) 直到這個從庫上所有的表修復結束。開始修復下一個從庫

四. 實驗示例

1. 實驗環境

主機IP : 192.168.239.135 192.168.239.136
主機系統 : centos7.2
MySQL版本 : 5.5.56
兩臺主機已經配置好主從，其中135為主，136為從

2. 主從數據一致性檢查

1）授權（master主機上）
mysql> CREATE DATABASE pt; #創建數據庫pt用于存放checksum的值
mysql> GRANT UPDATE,INSERT,SELECT,PROCESS,SUPER,REPLICATION SLAVE ON . 'checksum'@'192.168.%' IDENTIFIED BY "check_pass"; #創建checksum用戶用于執行檢測，以及分配檢測時要用的權限。
mysql> GRANT ALL ON pt.* TO checksum@'192.168.%'; #checksum用戶要將checksum的值寫入pt數據庫中所以需要分配權限給checksum用戶。
權限解釋： select //查看所有庫的表,原理可加 explain選項查看 process //show processlist super //set binlog_format='statement' replication slave //show slave hosts
注意 : 在master上執行一致性檢測時，master會通過show processlist查看slave主機，并通過連接master的賬號和密碼連接slave，所以master上一致性檢測的賬號在slave上一定要有。
2）在master上執行一致性檢測
首先我們需要人為創造不一致，在slave中刪除一條記錄

執行一致性檢測（主從上都可執行）
~]# pt-table-checksum --nocheck-binlog-format --nocheck-plan --nocheck-replication-filters --replicate=pt.checksums --databases=hellodb -u 'checksum' -p 'check_pass' -h 192.168.239.135 TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 03-14T16:25:21 0 1 8 1 0 0.011 hellodb.classes 03-14T16:25:21 0 0 14 1 0 0.017 hellodb.coc 03-14T16:25:21 0 0 7 1 0 0.032 hellodb.courses 03-14T16:25:21 0 0 15 1 0 0.015 hellodb.scores 03-14T16:25:21 0 0 25 1 0 0.016 hellodb.students 03-14T16:25:21 0 0 4 1 0 0.018 hellodb.teachers 03-14T16:25:21 0 0 0 1 0 0.016 hellodb.toc
顯示數據解釋
TS : 完成檢測表時的時間，不顯示年份。
ERRORS : 在checksum時發生的錯誤和警告的次數
DIFFS : 主從之間chunk不同的個數，如果不為0，表明主從數據有不一致的。
ROWS : 檢測表時一個chunk有多少行。如果使用了-where選項，一個表中的chunk可能不同
CHUNKS : 表被切割成了多少個chunk
SKIPPED : 由于某種原因跳過檢測chunk的數量
TIME : checksum此表所花的時間。
TABLE : 被checksum的表明
由上可知classes表中有數據不一致。

3. 主從同步

主從實現同步，往往都是借助pt-table-checksum產生的checksum表來說實現數據同步。

1）手動同步

~]# pt-table-sync --print --sync-to-master h=192.168.239.136,u=checksum,p=check_pass --databases=hellodb --replicate=pt.checksums
h=192.168.239.136,u=checksum,p=check_pass 指明需要同步的slave主機，以及登錄的用戶名和密碼
--databases=hellodb：指明同步的數據
--replicate=pt.checksums：同步時使用的checksum數據庫。
--sync-to-master：會通過show slave status去自動找主服務器同步數據，如果沒有此參數，我們需要通過h p u同時指明master和slave，即兩組h p u。
--print：主從不同的數據僅打印出來，并不在從上執行。
此命令在主從上都可執行。輸出信息如下：

我們只需在從服務器上執行REPLACE INTO hellodb.classes(classid, class, numofstu) VALUES ('1', 'Shaolin Pai', '10')這條sql語句即可

2）自動同步

~]# pt-table-sync --execute --sync-to-master h=192.168.239.136,u=checksum,p=check_pass --databases=hellodb --replicate=pt.checksums
--execute：自動修復主從不同的數據
自動同步出現如下錯誤：

pt-table-sync在實現同時時并不會直接在slave上進行操作，都是在master上執行命令，進而影響slave，這種修改數據的方式更加安全。所以master需要在slave上有對應的權限。
上圖顯示master在slave上沒有delete權限，查看slave分配的權限，如下圖可知確實沒有delete權限，只需在master上將checksum用戶添加delete權限即可

在master上修改checksum的權限，由于主從同步，slave也會修改對應用戶權限

mysql GRANT UPDATE,INSERT,SELECT,DELETE,PROCESS,SUPER,REPLICATION SLAVE ON . TO 'checksum'@'192.168.%';

執行數據同步，再次執行checksum檢測，可以看到沒有不同，而且查看slave中classes數據發現刪除的數據又出現了

參考

https://www.percona.com/doc/percona-toolkit/3.0/pt-table-sync.html
https://www.percona.com/doc/percona-toolkit/3.0/pt-table-checksum.html

轉載于:https://blog.51cto.com/13589448/2086866

總結

以上是生活随笔為你收集整理的MySQL之一致性检测及数据同步的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。