MySQL的一致性如何檢測及實現數據同步

本文主要給大家簡單講講MySQL的一致性如何檢測及實現數據同步，相關專業術語大家可以上網查查或者找一些相關書籍補充一下，這里就不涉獵了，我們就直奔主題吧，希望MySQL的一致性如何檢測及實現數據同步這篇文章可以給大家帶來一些實際幫助。

一. 部署percona tookit

1. 下載安裝包
   ~]# 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

2. 安裝
   ~]# 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' -h292.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數據發現刪除的數據又出現了

MySQL的一致性如何檢測及實現數據同步就先給大家講到這里，對于其它相關問題大家想要了解的可以持續關注我們的行業資訊。我們的板塊內容每天都會捕捉一些行業新聞及專業知識分享給大家的。