利用mk-table-checksum監測Mysql主從資料一致性操作記錄

散盡浮華發表於2017-01-12

 

前面已經提到了mysql主從環境下資料一致性檢查:mysql主從同步(3)-percona-toolkit工具(資料一致性監測、延遲監控)使用梳理
今天這裡再介紹另一種Mysql資料一致性自動檢測工具:Maatkit。(不過Maatkit工具現在已經不維護了,推薦還是使用percona-toolkit工具吧!)

Maatkit是一個開源的工具包,為mySQL日常管理提供了幫助,它包含很多工具,這裡主要說下面兩個:
1)mk-table-checksum                     用來檢測master和slave上的表結構和資料是否一致的;
2)mk-table-sync                             在主從資料不一致時,用來修復資料的;先主後從有效保證表一致的工具,不必過載從表而能夠保證一致。
上面兩個perl指令碼在執行時都會鎖表,表的大小取決於執行的快慢,勿在高峰期間執行,可選擇凌晨。
-----------------------------------------------------------------------------------------------------

下面記錄了這一操作過程:
基本資訊:
master:192.168.1.101
slave:192.168.1.102
版本:mysql5.6
同步的庫:huanqiu、huanpc

Maatkit安裝過程:(主庫和從庫伺服器上都可以安裝,因為資料一致性檢查操作在主庫或從庫機器上都可以執行;建議主從機器上都安裝
1)安裝該工具依賴的軟體包
[root@master-server src]# yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes perl perl-DBI -y

2)maatkit下載安裝    [需要FQ到官網下載:https://code.google.com/archive/p/maatkit/downloads]
百度雲盤下載地址:https://pan.baidu.com/s/1c1AufW8    (提取密碼:vbi1
[root@master-server ~]# tar -zvxf maatkit-7540.tar.gz  && cd maatkit-7540 
[root@master-server maatkit-7540]# perl Makefile.PL
Checking if your kit is complete...
Looks good
Writing Makefile for maatkit
------------------------------------------------------------------------------------------------------------
如有報錯:
Can't locate ExtUtils/MakeMaker.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at Makefile.PL line 1.
BEGIN failed--compilation aborted at Makefile.PL line 1.
解決辦法:
[root@slave-server maatkit-4334]# yum install perl-ExtUtils-Embed perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker -y
------------------------------------------------------------------------------------------------------------
[root@master-server maatkit-4334]# make && make install

[root@master-server ~]# mk-               //按TAB鍵補全,可以檢視到Maatkit的所有命令

mk-archiver               mk-find                   mk-parallel-dump          mk-show-grants            mk-table-sync
mk-checksum-filter        mk-heartbeat              mk-parallel-restore       mk-slave-delay            mk-table-usage
mk-config-diff            mk-index-usage            mk-profile-compact        mk-slave-find             mk-tcp-model
mk-deadlock-logger        mk-kill                   mk-purge-logs             mk-slave-move             mk-upgrade
mk-duplicate-key-checker  mk-loadavg                mk-query-advisor          mk-slave-prefetch         mk-variable-advisor
mk-error-log              mk-log-player             mk-query-digest           mk-slave-restart          mk-visual-explain
mk-fifo-split             mk-merge-mqd-results      mk-query-profiler         mk-table-checksum         

安裝完成後,一定要在被校驗的主從伺服器上建立只有SELECT許可權的帳號(最好保持主從上建立的賬號和密碼一樣,方便操作

mysql> GRANT SELECT ON *.* TO 'data_check'@'%' IDENTIFIED BY 'check@123';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

使用mk-table-checksum檢查主從資料一致性
1)在主庫伺服器上執行資料一致性檢查操作(也可以在從庫伺服器上進行資料一致性檢查操作,命令跟下面一樣)
[root@master-server ~]# mk-table-checksum h=192.168.1.101,u=data_check,p=check@123,P=3306 h=192.168.1.102,u=data_check,p=check@123,P=3306

DATABASE TABLE                     CHUNK HOST          ENGINE      COUNT         CHECKSUM TIME WAIT STAT  LAG
mysql    columns_priv                  0 192.168.1.101 MyISAM       NULL                0    0    0 NULL NULL
mysql    columns_priv                  0 192.168.1.102 MyISAM       NULL                0    0    0 NULL NULL
mysql    db                            0 192.168.1.101 MyISAM       NULL       4197331717    0    0 NULL NULL
mysql    db                            0 192.168.1.102 MyISAM       NULL       4197331717    0    0 NULL NULL
mysql    event                         0 192.168.1.101 MyISAM       NULL                0    0    0 NULL NULL
mysql    event                         0 192.168.1.102 MyISAM       NULL                0    0    0 NULL NULL
mysql    func                          0 192.168.1.101 MyISAM       NULL                0    0    0 NULL NULL
mysql    func                          0 192.168.1.102 MyISAM       NULL                0    0    0 NULL NULL
mysql    general_log                   0 192.168.1.101 CSV          NULL                0    0    0 NULL NULL
mysql    general_log                   0 192.168.1.102 CSV          NULL                0    0    0 NULL NULL
mysql    help_category                 0 192.168.1.101 MyISAM       NULL       2621154011    0    0 NULL NULL
mysql    help_category                 0 192.168.1.102 MyISAM       NULL       2621154011    0    0 NULL NULL
mysql    help_keyword                  0 192.168.1.101 MyISAM       NULL       2791798352    0    0 NULL NULL
mysql    help_keyword                  0 192.168.1.102 MyISAM       NULL        578891347    0    0 NULL NULL
mysql    help_relation                 0 192.168.1.101 MyISAM       NULL       3733405838    0    0 NULL NULL
mysql    help_relation                 0 192.168.1.102 MyISAM       NULL       1649042165    0    0 NULL NULL
mysql    help_topic                    0 192.168.1.101 MyISAM       NULL       1795841156    0    0 NULL NULL
mysql    help_topic                    0 192.168.1.102 MyISAM       NULL       2642710534    0    0 NULL NULL
mysql    innodb_index_stats            0 192.168.1.101 InnoDB       NULL       1853713685    0    0 NULL NULL
mysql    innodb_index_stats            0 192.168.1.102 InnoDB       NULL        213249811    0    0 NULL NULL
mysql    innodb_table_stats            0 192.168.1.101 InnoDB       NULL       2028951538    0    0 NULL NULL
mysql    innodb_table_stats            0 192.168.1.102 InnoDB       NULL       4163315044    0    0 NULL NULL
mysql    ndb_binlog_index              0 192.168.1.101 MyISAM       NULL                0    0    0 NULL NULL
mysql    ndb_binlog_index              0 192.168.1.102 MyISAM       NULL                0    0    0 NULL NULL
mysql    plugin                        0 192.168.1.101 MyISAM       NULL                0    0    0 NULL NULL
mysql    plugin                        0 192.168.1.102 MyISAM       NULL                0    0    0 NULL NULL
mysql    proc                          0 192.168.1.101 MyISAM       NULL                0    0    0 NULL NULL
mysql    proc                          0 192.168.1.102 MyISAM       NULL                0    0    0 NULL NULL
mysql    procs_priv                    0 192.168.1.101 MyISAM       NULL                0    0    0 NULL NULL
mysql    procs_priv                    0 192.168.1.102 MyISAM       NULL                0    0    0 NULL NULL
mysql    proxies_priv                  0 192.168.1.101 MyISAM       NULL       3956276869    0    0 NULL NULL
mysql    proxies_priv                  0 192.168.1.102 MyISAM       NULL       2040113179    0    0 NULL NULL
mysql    servers                       0 192.168.1.101 MyISAM       NULL                0    0    0 NULL NULL
mysql    servers                       0 192.168.1.102 MyISAM       NULL                0    0    0 NULL NULL
mysql    slave_master_info             0 192.168.1.101 InnoDB       NULL                0    0    0 NULL NULL
mysql    slave_master_info             0 192.168.1.102 InnoDB       NULL                0    0    0 NULL NULL
mysql    slave_relay_log_info          0 192.168.1.101 InnoDB       NULL                0    0    0 NULL NULL
mysql    slave_relay_log_info          0 192.168.1.102 InnoDB       NULL                0    0    0 NULL NULL
mysql    slave_worker_info             0 192.168.1.101 InnoDB       NULL                0    0    0 NULL NULL
mysql    slave_worker_info             0 192.168.1.102 InnoDB       NULL                0    0    0 NULL NULL
mysql    slow_log                      0 192.168.1.101 CSV          NULL                0    0    0 NULL NULL
mysql    slow_log                      0 192.168.1.102 CSV          NULL                0    0    0 NULL NULL
mysql    tables_priv                   0 192.168.1.101 MyISAM       NULL                0    0    0 NULL NULL
mysql    tables_priv                   0 192.168.1.102 MyISAM       NULL                0    0    0 NULL NULL
mysql    time_zone                     0 192.168.1.101 MyISAM       NULL                0    0    0 NULL NULL
mysql    time_zone                     0 192.168.1.102 MyISAM       NULL                0    0    0 NULL NULL
mysql    time_zone_leap_second         0 192.168.1.101 MyISAM       NULL                0    0    0 NULL NULL
mysql    time_zone_leap_second         0 192.168.1.102 MyISAM       NULL                0    0    0 NULL NULL
mysql    time_zone_name                0 192.168.1.101 MyISAM       NULL                0    0    0 NULL NULL
mysql    time_zone_name                0 192.168.1.102 MyISAM       NULL                0    0    0 NULL NULL
mysql    time_zone_transition          0 192.168.1.101 MyISAM       NULL                0    0    0 NULL NULL
mysql    time_zone_transition          0 192.168.1.102 MyISAM       NULL                0    0    0 NULL NULL
mysql    time_zone_transition_type     0 192.168.1.101 MyISAM       NULL                0    0    0 NULL NULL
mysql    time_zone_transition_type     0 192.168.1.102 MyISAM       NULL                0    0    0 NULL NULL
mysql    user                          0 192.168.1.101 MyISAM       NULL        882367917    0    0 NULL NULL
mysql    user                          0 192.168.1.102 MyISAM       NULL        883514797    0    0 NULL NULL
DATABASE TABLE     CHUNK HOST          ENGINE      COUNT         CHECKSUM TIME WAIT STAT  LAG
huanqiu  checksums     0 192.168.1.101 InnoDB       NULL       1230819712    0    0 NULL NULL
huanqiu  checksums     0 192.168.1.102 InnoDB       NULL       3864574229    0    0 NULL NULL
huanqiu  haha          0 192.168.1.101 InnoDB       NULL       1378115040    0    0 NULL NULL
huanqiu  haha          0 192.168.1.102 InnoDB       NULL       1378115040    0    0 NULL NULL
DATABASE TABLE     CHUNK HOST          ENGINE      COUNT         CHECKSUM TIME WAIT STAT  LAG
huanpc   checksums     0 192.168.1.101 InnoDB       NULL       2427682155    0    0 NULL NULL
huanpc   checksums     0 192.168.1.102 InnoDB       NULL        289696409    0    0 NULL NULL
huanpc   heihei        0 192.168.1.101 InnoDB       NULL       1446184115    0    0 NULL NULL
huanpc   heihei        0 192.168.1.102 InnoDB       NULL       1956266704    0    0 NULL NULL

上面命令中引數解釋:
第一個h:   填寫主庫ip
第一個u:   主庫授權的使用者名稱
第一個p:   主庫授權的密碼
第二個h:   填寫從庫ip
第二個u:   從庫授權的使用者名稱
第二個p:   從庫授權的密碼
P:   mysql的埠

上面的命令所示檢查主從的所有庫的資料一致性;一般我們檢查的是同步的庫。這就需要新增引數:
-d(或者--database)    後接資料庫名,注意後面不需要加=號,多個資料庫之間用逗號隔開,比如-dhuanqiu,huanpc;如果不加-d,校驗的是所有庫;
--count    會計算出表的行數

如下操作:
[root@master-server ~]# mk-table-checksum h=192.168.1.101,u=data_check,p=check@123,P=3306 h=192.168.1.102,u=data_check,p=check@123,P=3306 -dhuanqiu --count                //針對單個庫huanqiu,檢查主從資料一致性

DATABASE TABLE     CHUNK HOST          ENGINE      COUNT         CHECKSUM TIME WAIT STAT  LAG
huanqiu  checksums     0 192.168.1.101 InnoDB          1         705c5e2e    0    0 NULL NULL
huanqiu  checksums     0 192.168.1.102 InnoDB          1         f163e1ff    0    0 NULL NULL
huanqiu  haha          0 192.168.1.101 InnoDB          4         481dfe49    0    0 NULL NULL
huanqiu  haha          0 192.168.1.102 InnoDB          5         ecb4850b    0    0 NULL NULL

[root@master-server ~]# mk-table-checksum h=192.168.1.101,u=data_check,p=check@123,P=3306  h=192.168.1.102,u=data_check,p=check@123,P=3306 -dhuanpc --count               //針對單個庫huanpc,檢查主從資料一致性

DATABASE TABLE     CHUNK HOST          ENGINE      COUNT         CHECKSUM TIME WAIT STAT  LAG
huanpc   checksums     0 192.168.1.101 InnoDB          1          5af891e    0    0 NULL NULL
huanpc   checksums     0 192.168.1.102 InnoDB          1         96d7dfcf    0    0 NULL NULL
huanpc   heihei        0 192.168.1.101 InnoDB          6          a12d47d    0    0 NULL NULL
huanpc   heihei        0 192.168.1.102 InnoDB          7         5681c480    0    0 NULL NULL

[root@master-server ~]# mk-table-checksum h=192.168.1.101,u=data_check,p=check@123,P=3306  h=192.168.1.102,u=data_check,p=check@123,P=3306 -dhuanqiu,huanpc --count        //針對多個庫huaniu、huanpc,檢查主從資料一致性

DATABASE TABLE     CHUNK HOST          ENGINE      COUNT         CHECKSUM TIME WAIT STAT  LAG
huanqiu  checksums     0 192.168.1.101 InnoDB          1         705c5e2e    0    0 NULL NULL
huanqiu  checksums     0 192.168.1.102 InnoDB          1         f163e1ff    0    0 NULL NULL
huanqiu  haha          0 192.168.1.101 InnoDB          4         481dfe49    0    0 NULL NULL
huanqiu  haha          0 192.168.1.102 InnoDB          5         ecb4850b    0    0 NULL NULL
DATABASE TABLE     CHUNK HOST          ENGINE      COUNT         CHECKSUM TIME WAIT STAT  LAG
huanpc   checksums     0 192.168.1.101 InnoDB          1          5af891e    0    0 NULL NULL
huanpc   checksums     0 192.168.1.102 InnoDB          1         96d7dfcf    0    0 NULL NULL
huanpc   heihei        0 192.168.1.101 InnoDB          6          a12d47d    0    0 NULL NULL
huanpc   heihei        0 192.168.1.102 InnoDB          7         5681c480    0    0 NULL NULL

上述結果中的引數解釋:
DATABASE:     檢查的庫名
TABLE:           檢查的庫中的表名
CHUNK:          checksum時的近似數值
HOST:            主從MYSQL的地址
ENGINE:         表引擎
COUNT:         表的行數
CHECKSUM:  校驗值
TIME:            所用時間
WAIT:           等待時間
STAT:           MASTER_POS_WAIT()返回值
LAG:            slave的延時時間

mk-checksum-filter過濾工具的使用
如果只想知道huanqiu庫中哪些表不是一致的,那麼只需要加一個管道符即可,如下:
[root@master-server ~]# mk-table-checksum h=192.168.1.101,u=data_check,p=check@123,P=3306 h=192.168.1.102,u=data_check,p=check@123,P=3306 -dhuanqiu --count | mk-checksum-filter                   //如下表示huanqiu庫下只有haha表的資料,主從不一致

huanqiu  checksums     0 192.168.1.101 InnoDB          1         705c5e2e    0    0 NULL NULL
huanqiu  checksums     0 192.168.1.102 InnoDB          1         f163e1ff    0    0 NULL NULL
huanqiu  haha          0 192.168.1.101 InnoDB          4         481dfe49    0    0 NULL NULL
huanqiu  haha          0 192.168.1.102 InnoDB          5         ecb4850b    0    0 NULL NULL

[root@master-server ~]# mk-table-checksum h=192.168.1.101,u=data_check,p=check@123,P=3306  h=192.168.1.102,u=data_check,p=check@123,P=3306 -dhuanpc --count  | mk-checksum-filter                 //如下表示huanqiu庫下只有haha表的資料,主從不一致

huanpc   checksums     0 192.168.1.101 InnoDB          1          5af891e    0    0 NULL NULL
huanpc   checksums     0 192.168.1.102 InnoDB          1         96d7dfcf    0    0 NULL NULL
huanpc   heihei        0 192.168.1.101 InnoDB          6          a12d47d    0    0 NULL NULL
huanpc   heihei        0 192.168.1.102 InnoDB          7         5681c480    0    0 NULL NULL

[root@master-server ~]# mk-table-checksum h=192.168.1.101,u=data_check,p=check@123,P=3306  h=192.168.1.102,u=data_check,p=check@123,P=3306 -dhuanqiu,huanpc --count | mk-checksum-filter                    //如下表示huanqiu庫下只有haha表的資料,主從不一致

huanqiu  checksums     0 192.168.1.101 InnoDB          1         705c5e2e    0    0 NULL NULL
huanqiu  checksums     0 192.168.1.102 InnoDB          1         f163e1ff    0    0 NULL NULL
huanqiu  haha          0 192.168.1.101 InnoDB          4         481dfe49    0    0 NULL NULL
huanqiu  haha          0 192.168.1.102 InnoDB          5         ecb4850b    0    0 NULL NULL
huanpc   checksums     0 192.168.1.101 InnoDB          1          5af891e    0    0 NULL NULL
huanpc   checksums     0 192.168.1.102 InnoDB          1         96d7dfcf    0    0 NULL NULL
huanpc   heihei        0 192.168.1.101 InnoDB          6          a12d47d    0    0 NULL NULL
huanpc   heihei        0 192.168.1.102 InnoDB          7         5681c480    0    0 NULL NULL

使用mk-table-sync修復主從不同步的資料
顧名思義,mk-table-sync用來修復多個例項之間資料的不一致。它可以讓主從的資料修復到最終一致,也可以使通過應用雙寫或多寫的多個不相關的資料庫例項修復到一致。同時它還內部整合了pt-table-checksum的校驗功能,可以一邊校驗一邊修復,也可以基於pt-table-checksum的計算結果來進行修復。
mk-table-sync工作原理
1)單行資料checksum值的計算
計算邏輯與pt-table-checksum一樣,也是先檢查表結構,並獲取每一列的資料型別,把所有資料型別都轉化為字串,然後用concat_ws()函式進行連線,由此計算出該行的checksum值。checksum預設採用crc32計算。

2)資料塊checksum值的計算
同pt-table-checksum工具一樣,pt-table-sync會智慧分析表上的索引,然後把表的資料split成若干個chunk,計算的時候以chunk為單位。可以理解為把chunk內所有行的資料拼接起來,再計算crc32的值,即得到該chunk的checksum值。

3)壞塊檢測和修復
前面兩步,pt-table-sync與pt-table-checksum的演算法和原理一樣。再往下,就開始有所不同:
pt-table-checksum只是校驗,所以它把checksum結果儲存到統計表,然後把執行過的sql語句記錄到binlog中,任務就算完成。語句級的複製把計算邏輯傳遞到從庫,並在從庫執行相同的計算。pt-table-checksum的演算法本身並不在意從庫的延遲,延遲多少都一樣計算(有同事對此不理解,可以參考我的前一篇文章),不會影響計算結果的正確性(但是我們還是會檢測延遲,因為延遲太多會影響業務,所以總是要加上—max-lag來限流)。
pt-table-sync則不同。它首先要完成chunk的checksum值的計算,一旦發現主從上同樣的chunk的checksum值不同,就深入到該chunk內部,逐行比較並修復有問題的行。其計算邏輯描述如下(以修復主從結構的資料不一致為例,業務雙寫的情況修復起來更復雜—因為涉及到衝突解決和基準選擇的問題,限於篇幅,這裡不介紹):
    1)對每一個從庫,每一個表,迴圈進行如下校驗和修復過程。
    2)對每一個chunk,在校驗時加上for update鎖。一旦獲得鎖,就記錄下當前主庫的show master status值。
    3)在從庫上執行select master_pos_wait()函式,等待從庫sql執行緒執行到show master status得到的位置。以此保證,主從上關於這個chunk的內容均不再改變。
    4)對這個chunk執行checksum,然後與主庫的checksum進行比較。
    5)如果checksum相同,說明主從資料一致,就繼續下一個chunk。
    6)如果checksum不同,說明該chunk有不一致。深入chunk內部,逐行計算checksum並比較(單行checksum比較過程與chunk的比較過程一樣,單行實際是chunk的size為1的特例)。
    7)如果發現某行不一致,則標記下來。繼續檢測剩餘行,直到這個chunk結束。
    8)對找到的主從不一致的行,採用replace into語句,在主庫執行一遍以生成該行全量的binlog,並同步到從庫,這會以主庫資料為基準來修復從庫;對於主庫有的行而從庫沒有的行,採用replace在主庫上插入(必須不能是insert);對於從庫有而主庫沒有的行,通過在主庫執行delete來刪除(pt-table-sync強烈建議所有的資料修復都只在主庫進行,而不建議直接修改從庫資料;但是也有特例,後面會講到)。
    9)直到修復該chunk所有不一致的行。繼續檢查和修復下一個chunk。
   10)直到這個從庫上所有的表修復結束。開始修復下一個從庫。

重要選項
--print                    顯示同步需要執行的語句
--execute               執行資料同步
--charset=utf8        設定字符集,避免從庫亂碼。

例項說明:
mk-table-sync的工作方式是:先一行一行檢查主從庫的表是否一樣,如果哪裡不一樣,就執行刪除,更新,插入等操作,使其達到一致。
通過上面mk-table-checksum的檢查結果可以看出,同步的兩個庫huanqiu和huanpc的資料並不一致,這時就可以使用mk-table-sync進行資料修復了。

資料修復命令如下:(如果mysql埠是預設的3306,則下面命令中的P=3306可以省略)
由於上面在mk-table-checksum檢查時用的data_check只有select許可權,許可權太小,不能用於mk-table-sync修復資料只用。
所以還需要在主庫和從庫資料庫裡建立用於mk-table-sync修復資料之用的賬號許可權

mysql> GRANT ALL ON *.* to mksync@'%' identified by "sync@123";
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

[root@master-server lib]# mk-table-sync --execute --charset=utf8 --print --no-check-slave --databases huanqiu,huanpc h=192.168.1.101,u=mksync,p=sync@123,P=3306 h=192.168.1.102,u=mksync,p=sync@123,P=3306

UPDATE `huanpc`.`checksums` SET `chunk_time`='0.002821', `chunk_index`=NULL, `lower_boundary`=NULL, `upper_boundary`=NULL, `this_crc`='5681c480', `this_cnt`='7', `master_crc`='5681c480', `master_cnt`='7', `ts`='2017-01-12 14:29:14' WHERE `db`='huanpc' AND `tbl`='heihei' AND `chunk`='1' LIMIT 1 /*maatkit src_db:huanpc src_tbl:checksums src_dsn:A=utf8,P=3306,h=192.168.1.101,p=...,u=mksync dst_db:huanpc dst_tbl:checksums dst_dsn:A=utf8,P=3306,h=192.168.1.102,p=...,u=mksync lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:13702 user:root host:master-server*/;
DELETE FROM `huanpc`.`heihei` WHERE `member`='90' LIMIT 1 /*maatkit src_db:huanpc src_tbl:heihei src_dsn:A=utf8,P=3306,h=192.168.1.101,p=...,u=mksync dst_db:huanpc dst_tbl:heihei dst_dsn:A=utf8,P=3306,h=192.168.1.102,p=...,u=mksync lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:13702 user:root host:master-server*/;
UPDATE `huanqiu`.`checksums` SET `chunk_time`='0.002495', `chunk_index`=NULL, `lower_boundary`=NULL, `upper_boundary`=NULL, `this_crc`='ecb4850b', `this_cnt`='5', `master_crc`='ecb4850b', `master_cnt`='5', `ts`='2017-01-12 14:27:45' WHERE `db`='huanqiu' AND `tbl`='haha' AND `chunk`='1' LIMIT 1 /*maatkit src_db:huanqiu src_tbl:checksums src_dsn:A=utf8,P=3306,h=192.168.1.101,p=...,u=mksync dst_db:huanqiu dst_tbl:checksums dst_dsn:A=utf8,P=3306,h=192.168.1.102,p=...,u=mksync lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:13702 user:root host:master-server*/;
DELETE FROM `huanqiu`.`haha` WHERE `id`='90' LIMIT 1 /*maatkit src_db:huanqiu src_tbl:haha src_dsn:A=utf8,P=3306,h=192.168.1.101,p=...,u=mksync dst_db:huanqiu dst_tbl:haha dst_dsn:A=utf8,P=3306,h=192.168.1.102,p=...,u=mksync lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:13702 user:root host:master-server*/;

上面那條命令執行後,再次檢查主從資料,發現主從已經同步:

[root@master-server ~]# mk-table-checksum h=192.168.1.101,u=data_check,p=check@123,P=3306  h=192.168.1.102,u=data_check,p=check@123,P=3306 -dhuanqiu,huanpc --count 
DATABASE TABLE     CHUNK HOST          ENGINE      COUNT         CHECKSUM TIME WAIT STAT  LAG
huanqiu  checksums     0 192.168.1.101 InnoDB          1         705c5e2e    0    0 NULL NULL
huanqiu  checksums     0 192.168.1.102 InnoDB          1         705c5e2e    0    0 NULL NULL
huanqiu  haha          0 192.168.1.101 InnoDB          3         b9b29a07    0    0 NULL NULL
huanqiu  haha          0 192.168.1.102 InnoDB          3         b9b29a07    0    0 NULL NULL
DATABASE TABLE     CHUNK HOST          ENGINE      COUNT         CHECKSUM TIME WAIT STAT  LAG
huanpc   checksums     0 192.168.1.101 InnoDB          1          5af891e    0    0 NULL NULL
huanpc   checksums     0 192.168.1.102 InnoDB          1          5af891e    0    0 NULL NULL
huanpc   heihei        0 192.168.1.101 InnoDB          7         22a57b1c    0    0 NULL NULL
huanpc   heihei        0 192.168.1.102 InnoDB          7         22a57b1c    0    0 NULL NULL

或者加mk-checksum-filter
[root@master-server ~]# mk-table-checksum h=192.168.1.101,u=data_check,p=check@123,P=3306  h=192.168.1.102,u=data_check,p=check@123,P=3306 -dhuanqiu,huanpc --count | mk-checksum-filter
[root@master-server ~]#

-------------------------------------------------------------------------------------------------------------------
如有報錯:
DBD::mysql::db do failed: Access denied; you need (at least one of) the SUPER privilege(s) for this operation [for Statement "/*!50105 SET @@binlog_format="STATEMENT"*/"] at /usr/local/bin/mk-table-sync line 8568.
Issuing rollback() due to DESTROY without explicit disconnect() of DBD::mysql::db handle ;host=192.168.1.101;port=3306;mysql_read_default_group=client at /usr/local/bin/mk-table-sync line 8568.

解決辦法:安裝DBI和DBD-MySQL
下載地址:
http://ftp.cuhk.edu.hk/pub/packages/perl/CPAN/authors/id/T/TI/TIMB/
http://search.cpan.org/dist/DBD-mysql/
或者百度雲盤下載:https://pan.baidu.com/s/1miv0S8g (提取密碼:fh24

[root@master-server src]# tar -zvxf DBI-1.634.tar.gz
[root@master-server src]# cd DBI-1.634
[root@master-server DBI-1.634]# perl Makefile.PL
[root@master-server DBI-1.634]# make && make install

[root@master-server src]# tar -zvxf DBD-mysql-4.041_01.tar.gz
[root@master-server src]# cd DBD-mysql-4.041_01
[root@master-server DBD-mysql-4.041_01]# perl Makefile.PL --mysql_config=/usr/local/mysql/bin/mysql_config         //主庫mysql安裝路徑/usr/local/mysql
[root@master-server DBD-mysql-4.041_01]# make
[root@master-server DBD-mysql-4.041_01]# make install
--------------------------------------------------------------------------------------------------------------------

這裡要說明一下:
主從資料庫表和表結構必須一致,否則會報錯!
--no-check-slave這個引數,如果不加這個引數,會提示報錯,因為此工具預設不允許在從庫上修改資料,但這裡為了不在主庫上dump出來,再到從庫上匯入進去,加了這個引數。

特別注意的是:
1)如果想要看下到底哪些資料不同步,可以這樣做:(如果不跟-t引數,就是說同步整個庫的所有表的資料)
mk-table-sync --execute --charset=utf8 --print --no-check-slave --databases huanqiu,huanpc h=192.168.1.101,u=mksync,p=sync@123,P=3306 h=192.168.1.102,u=mksync,p=sync@123,P=3306 > /root/result
2)如果有好幾個從庫的話,建議分開同步,除非幾個從庫的checksum一樣!
因為每個從庫同步的步調不一定都一致,如果幾個從庫一塊同步的話,很容易造成主鍵衝突,導致主從同步中斷。
舉個例子來說:
主庫:192.168.1.101
從庫1: 192.168.1.102
從庫2: 192.168.1.103
從庫3: 192.168.1.104

建議(分開同步,一個個操作):
mk-table-sync --execute --charset=utf8 --print --no-check-slave -d huanqiu -t haha h=192.168.1.101,u=mksync,p=sync@123,P=3306 h=192.168.1.102,u=mksync,p=sync@123,P=3306 > /root/result1
mk-table-sync --execute --charset=utf8 --print --no-check-slave -d huanqiu -t haha h=192.168.1.101,u=mksync,p=sync@123,P=3306 h=192.168.1.103,u=mksync,p=sync@123,P=3306 > /root/result2
mk-table-sync --execute --charset=utf8 --print --no-check-slave -d huanqiu -t haha h=192.168.1.101,u=mksync,p=sync@123,P=3306 h=192.168.1.104,u=mksync,p=sync@123,P=3306 > /root/result3
不建議(放在一起同步):
mk-table-sync --execute --charset=utf8 --print --no-check-slave -d huanqiu -t haha h=192.168.1.101,u=mksync,p=sync@123,P=3306 h=192.168.1.102,u=mksync,p=sync@123,P=3306 h=192.168.1.103,u=mksync,p=sync@123,P=3306 h=192.168.1.104,u=data_check,p=check@123,P=3306 > /root/result

4)為了減少重複操作,也可以一次同步好幾個表,多個表時可以在-t後加=號,即-t=tables1,tables2,table3...比如:
mk-table-sync --execute --charset=utf8 --print --no-check-slave -d huanqiu -t=haha,heihei,hehe,name h=192.168.1.101,u=data_check,p=check@123,P=3306 h=192.168.1.102,u=mksync,p=sync@123,P=3306 > /root/result

相關文章