pt-table-checksumによるデータ整合性の検証

41311 ワード

多くの人のオンラインはすべてMySQL主従のこのようなフレームワークを構築したことを信じて、多くの人はMySQLのサーバーSlaveから監視するだけですIOとSlave_SQLの2つのスレッドがYESかどうか、そしてSeconds_Behind_Master遅延が大きいなどの情報があります.しかし、彼らは定期的にMySQLマスターサーバーのデータとスレーブサーバーのデータが一致しているかどうかをチェックしているのではないでしょうか.データの一致性が最も重要なのは、データが一致しなければ、2つのYESの出現はないに違いありません.必ずしもslaveにエラーが発生した場合、SET GLOBAL sqlを通過することができるからです.slave_skip_counter=Nでエラーをスキップしたり、オプション--slave-skip-errors=[error_code]でエラーコードをスキップしたりして、処理後Slave_IOとSlave_SQLのステータスはYESのままですが、この時点でデータがメインライブラリと一致しない可能性があります.次はみんなといいツールを勉強しますpt-table-checksum
pt-table-checksum:MySQLレプリケーション整合性チェック(このツールのポイントは有効なデータの違いを見つけることです.データが異なる場合はpt-table-syncで問題を解決できます.)
パッケージのダウンロード先:http://www.percona.com/downloads/percona-toolkit/2.2.7/RPM/percona-toolkit-2.2.7-1.noarch.rpm
PTツールのインストールは、私のブログを参照してください.http://www.cnblogs.com/xuanzhi201111/p/4128894.html(マスターライブラリとスレーブライブラリの両方をインストール)

実験環境:master(192.168.1.28:306)
slave(192.168.1.129:3306)

注意事項:
1、テストによって、1つのすぐにマスターライブラリにログインすることができて、同じく従ライブラリのアカウントにログインすることができます;
2、hostは1つしか指定できません.メインライブラリのIPでなければなりません.
3、検査時に表にS錠をかける.
4、マスターとslaveのbinlogログがSTATEMENT形式でない場合、--no-check-binlog-formatオプション
5、実行前にライブラリからの同期IOとSQLプロセスがYES状態であること.
6、テーブルにプライマリ・キーまたはユニーク・キーのインデックスを付ける

メインライブラリ(master:306)では、次のようになります.

mysql> select * from check_sum.test1;  
+------+-------+
| id   | couse |
+------+-------+
|    1 | aa    |
|    2 | bb    |
|    3 | cc    |
+------+-------+
3 rows in set (0.00 sec)

mysql>

スレーブライブラリ(slave:306)では、次のようになります(masterデータと一致しません):

mysql> select * from check_sum.test1;
+------+-------+
| id   | couse |
+------+-------+
|    1 | aa    |
|    2 | bb    |
+------+-------+
2 rows in set (0.00 sec)

mysql>

pt-table-checksumパラメータ説明:

--nocheck-replication-filters ：        ，    。     --databases           。
--no-check-binlog-format      :       binlog  ，  binlog   ROW，    。
--replicate-check-only :         。
--replicate=    ： checksum          ，               。 
--databases=    ：           ，        。
--tables=       ：         ，       
h=192.168.1.128 ：Master   
u=root          ：   
p=123456        ：  
P=3306          ：

マスターライブラリでデータチェックコマンドを実行します.

[root ~]$ pt-table-checksum  --nocheck-replication-filters --replicate=test.checksums --databases=check_sum  h=192.168.1.128,u=admin,p=123456,P=3306
Replica slave has binlog_format MIXED which could cause pt-table-checksum to break replication.  Please read "Replicas using row-based replication" in the LIMITATIONS section of the tool's documentation.  If you 
understand the risks, specify --no-check-binlog-format to disable this check.

binlog_formatはMIXED形式で、同期を破壊します.--no-check-binlog-formatオプションを使用してチェックを閉じることができます.

--no-check-binlog-formatオプションを追加し、masterライブラリで実行し、次のエラー(エラーメッセージ)を報告します.

[root ~]$ pt-table-checksum  --nocheck-replication-filters --no-check-binlog-format  --replicate=test.checksums --databases=check_sum  h=192.168.1.128,u=admin,p=123456,P=3306
Cannot connect to P=3306,h=192.168.1.129,p=...,u=root Diffs cannot be detected because no slaves were found.  Please read the --recursion-method documentation for information.
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
04-13T15:44:23      0      0        3       1       0   0.044 check_sum.test1

上はすでにあなたにどのようにしたかを教えて、Please read the--recursion-method documentation for information、上のヒント情報はとてもはっきりしていて、slaveライブラリが見つからないので、実行に失敗しました.パラメータ--recursion-methodでモード解決を指定できます.--recursion-methodパラメータの設定については:

METHOD       USES
===========  =============================================
processlist  SHOW PROCESSLIST
hosts        SHOW SLAVE HOSTS
cluster      SHOW STATUS LIKE 'wsrep\_incoming\_addresses'
dsn=DSN      DSNs from a table
none         Do not find slaves

デフォルトはshow processlistでhostの値を見つけるかshow slave hostsでhostの値を見つけるかです

*************************** 1. row ***************************
     Id: 7
   User: admin
   Host: 192.168.1.129:60195
     db: NULL
Command: Binlog Dump
   Time: 663201
  State: Master has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL
*************************** 2. row ***************************
     Id: 37
   User: root
   Host: localhost
     db: check_sum
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
4 rows in set (0.00 sec)

もう一つの方法はshow slave hostsです.ライブラリプロファイルから自分のアドレスとポートを設定し、slaveライブラリで操作します([mysqld]でこの2行を追加します):

[root mysql-5.5.40]$ cat  my.cnf |grep "report"
report_host=192.168.1.129
report_port=3306

マスターライブラリに戻る操作:

mysql> show slave hosts;
+-----------+---------------+------+-----------+
| Server_id | Host          | Port | Master_id |
+-----------+---------------+------+-----------+
|        11 | 192.168.1.129 | 3306 |         1 |
+-----------+---------------+------+-----------+
1 row in set (0.00 sec)

mysql>

最も重要な点は、ライブラリから権限を与え、メインライブラリにアクセスさせる必要があります.hostを1つしか指定できません.メインライブラリのIPを指定し、slaveライブラリで操作する必要があります.

mysql>GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'admin'@'192.168.1.128' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>

マスターでadminユーザーをログインさせる権利がない場合、マスターライブラリでadmin権限を与えるにはrootユーザーでチェックする人が多いので、スレーブライブラリで操作すればいいです(マスターライブラリで操作):

mysql>GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'admin'@'192.168.1.128' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>

権限の説明:

select     //       ,     explain    
process    //show processlist
super      //set binlog_format='statement'
replication slave   //show slave hosts

次に、データの一貫性を検出します(masterライブラリでの操作):

[root ~]$ pt-table-checksum  --nocheck-replication-filters --no-check-binlog-format --replicate=test.checksums --databases=check_sum  h=192.168.1.128,u=admin,p=123456,P=3306  
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
12-26T15:38:30      0      1        3       1       0   0.339 check_sum.test1

主従データの不一致が検出されたのを見て、DIFFSの下の値は1で、どうして一致しないのですか?--replicate=testを指定します.checksumsパラメータは、チェック情報をchecksumsテーブルに書き込むことを意味します.
次に例の意味を説明する.

TS            ：       。
ERRORS        ：              。
DIFFS         ：0    ，1     。   --no-replicate-check ，    0，   --replicate-check-only        。
ROWS          ：    。
CHUNKS        ：           。
SKIPPED       ：          ，       。
TIME          ：     。
TABLE         ：      。

マスターに行くと思ってたchecksumsテーブルは、マスターライブラリとスレーブライブラリが一致しない情報を表示し、N久にわたって葛藤し、DIFFSの値が1であることを前に示したが、masterではテーブルの情報は以下の通りである.

mysql> select * from test.checksums\G
*************************** 1. row ***************************
            db: check_sum
           tbl: test1
         chunk: 1
    chunk_time: 0.023619
   chunk_index: NULL
lower_boundary: NULL
upper_boundary: NULL
      this_crc: d5b7731c   #slave
      this_cnt: 3               #slave
    master_crc: d5b7731c  #master
    master_cnt: 3              #master             ts: 2014-12-26 15:50:29
1 row in set (0.00 sec)

mysql>

見たでしょう、それらの情報は同じで、正常に言えば、表裏の情報はmasterとslaveが一致しないはずです.今は一致しています.test.checksumの対比は同じで、slaveライブラリの少ないデータをmasterライブラリから同期することはできません.pt-table-syncがデータを同期するときはtestを借りるからです.checksumテーブル.焦らないでください:slaveライブラリの下のtestをもう一度見に行きましょう.checksums:(slaveライブラリ操作)

mysql> select * from test.checksums\G
*************************** 1. row ***************************
            db: check_sum
           tbl: test1
         chunk: 1
    chunk_time: 0.023619
   chunk_index: NULL
lower_boundary: NULL
upper_boundary: NULL
      this_crc: 64fdcfa3
      this_cnt: 2 #slave 2       
    master_crc: d5b7731c
    master_cnt: 3 #master 3   
            ts: 2014-12-26 15:50:29
1 row in set (0.00 sec)

マスターライブラリでは--printオプションでマスター下のcheck_を印刷できます.sum.test 1とslaveライブラリのcheck_sum.test 1の不一致データは、以下の通りです.

[root ~]$  pt-table-sync --replicate=test.checksums h=192.168.1.128,u=admin,p=123456,P=3306 h=192.168.1.129,u=admin,p=123456,P=3306 --print
Can't make changes on the master because no unique index exists at /usr/bin/pt-table-sync line 10648.  while doing check_sum.test1 on 192.168.1.129

以上のレポートが間違っていて、エラー情報も明らかです.テーブルに一意のインデックスがないと言って、masterライブラリでテーブルにプライマリ・キー・インデックスまたは一意のインデックスを追加します.

mysql> alter table test1 add primary key (id);
Query OK, 3 rows affected (0.17 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show create table test1\G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `id` int(11) NOT NULL DEFAULT '0',
  `couse` char(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>

さらにmasterでpt-table-checksumを1回実行し、pt-table-syncを次のように実行します.

[root ~]$ pt-table-checksum  --nocheck-replication-filters --no-check-binlog-format --replicate=test.checksums --databases=check_sum  h=192.168.1.128,u=admin,p=123456,P=3306
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
12-26T17:16:50      0      1        3       1       0   0.151 check_sum.test1

[root ~]$ pt-table-sync --replicate=test.checksums h=192.168.1.128,u=admin,p=123456,P=3306 h=192.168.1.129,u=admin,p=123456,P=3306 --print                                   
REPLACE INTO `check_sum`.`test1`(`id`, `couse`) VALUES ('3', 'cc') /*percona-toolkit src_db:check_sum src_tbl:test1 src_dsn:P=3306,h=192.168.1.128,p=...,u=admin dst_db:check_sum dst_tbl:test1 dst_dsn:P=3306,
h=192.168.1.129,p=...,u=admin lock:1 transaction:1 changing_src:test.checksums replicate:test.checksums bidirectional:0 pid:41368 user:root host:localhost.localdomain*/;

皆さんは不一致なデータ記録を見ましたね

パラメータの説明:

--replicate=    ：    pt-table-checksum    .
--databases=    :           ，       。
--tables=       ：        ，       。
--sync-to-master ：    DSN，   IP，    show processlist show slave status       。
h=127.0.0.1     ：     ，    2 ip，       Master   ， 2  Slave   。
u=root          ：  。
p=123456        ：  。
--print         ：  ，      。
--execute       ：    。

次の操作はslave上の少ないデータをmasterから同期することです(master操作):
(--execute)を使用して、データの一貫性を維持します.

[root ~]$ pt-table-sync --replicate=test.checksums h=192.168.1.128,u=admin,p=123456,P=3306 h=192.168.1.129,u=admin,p=123456,P=3306 --execute   
INSERT,DELETE command denied to user 'admin'@'192.168.1.128' for table 'test1' [for Statement "REPLACE INTO `check_sum`.`test1`(`id`, `couse`) VALUES ('3', 'cc') 
/*percona-toolkit src_db:check_sum src_tbl:test1 src_dsn:P=3306,h=192.168.1.128,p=...,u=admin dst_db:check_sum dst_tbl:test1 dst_dsn:P=3306,h=192.168.1.129,p=...,u=admin lock:1 transaction:1 
changing_src:test.checksums replicate:test.checksums bidirectional:0 pid:41650 user:root host:localhost.localdomain*/"] at line 10707 while doing check_sum.test1 on 192.168.1.129

エラーメッセージを見たでしょう.adminユーザーにINSERT、DELETE権限がないことを示します(rootユーザーであれば、これらの小さな問題はありません).masterがadminユーザー権限を許可した後、次のコマンドを実行します(master操作).

[root ~]$ pt-table-sync --replicate=test.checksums h=192.168.1.128,u=admin,p=123456,P=3306 h=192.168.1.129,u=admin,p=123456,P=3306 --execute
 
[root ~]$ pt-table-checksum  --nocheck-replication-filters --no-check-binlog-format --replicate=test.checksums --databases=check_sum  h=192.168.1.128,u=admin,p=123456,P=3306 
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
12-26T17:57:18      0      0        3       1       0   0.073 check_sum.test1

再検査が見られる場合、DIFFSは0になっているので、slaveに行って表のデータを見てみましょう(slave操作):

mysql> select * from check_sum.test1;
+----+-------+
| id | couse |
+----+-------+
|  1 | aa    |
|  2 | bb    |
|  3 | cc    |
+----+-------+
3 rows in set (0.00 sec)

マスターのデータと一致しました.

2つ目のケース:(slaveのポートが3306でない場合は、別の方法でチェックします)
環境:192.168.1.28:3106(master)
192.168.1.129:3307(slave)
データチェックコマンドpt-table-checksumを実行します.

[root ~]$  pt-table-checksum  --nocheck-replication-filters --no-check-binlog-format --replicate=test.checksums --databases=check_sum  h=192.168.1.128,u=admin,p=123456,P=3306       
Cannot connect to P=3306,h=192.168.1.129,p=...,u=admin Diffs cannot be detected because no slaves were found.  Please read the --recursion-method documentation for information.
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
12-26T19:59:11      0      0        3       1       0   0.065 check_sum.test1

pt-table-checksumのデフォルトはslaveライブラリの3306を探しているため、上記のレポートは間違っています.現在、slaveのポートは3307です(master操作):

mysql> show slave hosts;
+-----------+---------------+------+-----------+
| Server_id | Host          | Port | Master_id |
+-----------+---------------+------+-----------+
|        11 | 192.168.1.129 | 3307 |         1 |
+-----------+---------------+------+-----------+
1 row in set (0.00 sec)

mysql>

マスター上のtestライブラリにテーブルを作成します.

mysql> use test
Database changed
mysql> CREATE TABLE `dsns` ( `id` int(11) NOT NULL AUTO_INCREMENT, `parent_id` int(11) DEFAULT NULL, `dsn` varchar(255) NOT NULL, PRIMARY KEY (`id`) );
Query OK, 0 rows affected (0.08 sec)
mysql>

スレーブ情報を書き込み、複数のスレーブがある場合は複数のレコードを挿入します.

mysql> INSERT INTO dsns (parent_id,dsn) values(1, "h=192.168.1.129,u=admin,p=123456,P=3307");                               
Query OK, 1 row affected (0.04 sec)

mysql> select * from dsns;
+----+-----------+-----------------------------------------+
| id | parent_id | dsn                                     |
+----+-----------+-----------------------------------------+
|  1 |         1 | h=192.168.1.129,u=admin,p=123456,P=3307 |
+----+-----------+-----------------------------------------+
1 row in set (0.00 sec)

次にmasterでデータ検証コマンドを実行します.

[root ~]$  pt-table-checksum  --nocheck-replication-filters --no-check-binlog-format --replicate=test.checksums --databases=check_sum  h=192.168.1.128,u=admin,p=123456,P=3306--recursion-method=dsn=h=192.168.1.128,
D=test,t=dsns
             TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
12-26T20:10:30      0      0        3       1       0   0.100 check_sum.test1

slaveライブラリでデータの削除操作を行います.

mysql> select * from test1;
+----+-------+
| id | couse |
+----+-------+
|  1 | aa    |
|  2 | bb    |
|  3 | cc    |
+----+-------+
3 rows in set (0.00 sec)

mysql> delete from test1 where id=3;
Query OK, 1 row affected (0.00 sec)

さらにmasterライブラリでデータ検証コマンドを実行します.

[root ~]$  pt-table-checksum  --nocheck-replication-filters --no-check-binlog-format --replicate=test.checksums --databases=check_sum  h=192.168.1.128,u=admin,p=123456,P=3306 --recursion-method=dsn=h=192.168.1.128,
D=test,t=dsns
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
12-26T20:25:42      0      1        3       1       0   0.020 check_sum.test1

データが一致していないことを検査して、どのようにデータをライブラリから同期するかについては、まだ研究されていません.第1の方法で、ポートを交換してもだめです.pt-table-syncはデフォルトで3306ポートを探しています.もしあなたが研究したら、共有してください.ハハ^.^

まとめ:pt-table-checksumとpt-table-syncツールは非常に使いやすいデータ検査と修復のツールであり、日常生活でよく使われ、スクリプトを書いてデータの一貫性を監視することができます

参考資料:http://www.percona.com/doc/percona-toolkit/2.2/pt-table-checksum.html
http://www.percona.com/doc/percona-toolkit/2.2/pt-table-sync.html

作者:陸炫志出典:xuanzhiのブログhttp://www.cnblogs.com/xuanzhi201111あなたの支持はブロガーに対する最大の励ましであり、真剣に読んでくれてありがとう.本文の著作権は作者の所有に帰して、転載を歓迎して、しかしこの声明を保留してください.

面接-mysqlインデックス

C++11学習ノート---非同期操作実行結果を取得