MySQL道普請便り

第68回 MySQLにおけるデータの比較

この記事を読むのに必要な時間:およそ 6 分

pt-table-checksumを利用してデータを比較する

Percona Toolkitの中にpt-table-checksumというツールがあります。pt-table-checksumが今まで紹介したツールと違う点として,レプリケーションを前提としていることと,オンラインで実行できる点があります。ただしこちらのツールは前提としてステートメントベースのレプリケーション(SBR)である必要があります。

DSNを使ったpt-table-checksumのデータ比較

デフォルトではレプリケーション先の確認として,processlistまたはhostsを利用しますが,今回はDSNを使ったデータ比較方法を紹介します。DSNを使ってpt-table-checksumを実施する場合はまず,スレーブとなっているサーバーの情報をテーブルに登録します。idとdsnカラムを持ったテーブルを作成し,dsnにスレーブの情報を記載します。

今回はマスターのmysqlデータベースにdsnsテーブルを作成し,dsnsカラムにホスト,ポート,ユーザー,パスワード情報をカンマ区切りで記述します。

> CREATE TABLE mysql.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.02 sec)
    
> INSERT INTO mysql.dsns VALUES (1,null,'h=127.0.0.1,P=3307,u=root,p=password2');
Query OK, 1 row affected (0.00 sec)

pt-table-checksumを実施するときは,--recursion-methodにどのデータベースのどのテーブルにDSNがあるかを指定します。

$ pt-table-checksum  --host 127.0.0.1 --port 3306 --user root --password passwod1 --recursion-method="dsn=D=mysql,t=dsns" --databases d1
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
03-29T01:36:57      0      0        3       1       0   0.012 d1.t1
03-29T01:36:57      0      0      100       1       0   0.263 d1.t2
03-29T01:36:57      0      1        0       1       0   0.013 d1.t3

データ比較によって,t3テーブルに差異があることがわかりました。

replicateオプションを利用してpt-table-checksumのデータ比較を実施する

DSNを利用せずにpt-table-checksumを実施する場合は,マスターのサーバーに向かって下記を実行し,比較結果のテーブルをスレーブ側で確認する必要があります。

pt-table-checksum  --host <host名> --port <port> --user <user名> --password <password> --replicate <比較結果のテーブル名> --database <比較するデータベース名>

今回はreplicateオプションを利用してmysqlデータベースにpt_tcsというpt-table-checksumの結果テーブル名を指定しました。実行が終わるとマスター,スレーブともに,mysqlデータベースにpt_tcsテーブルが作成されていることが確認できます。

$ pt-table-checksum  --host 127.0.0.1 --port 3306 --user root --password password1 --replicate mysql.pt_tcs --databases d1
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
03-28T09:51:44      0      0        3       1       0   0.013 d1.t1
03-28T09:51:44      0      0      100       1       0   0.009 d1.t2
03-28T09:51:44      0      0        0       1       0   0.009 d1.t3

> DESC mysql.pt_tcs;
+----------------+--------------+------+-----+-------------------+-----------------------------+
| Field          | Type         | Null | Key | Default           | Extra                       |
+----------------+--------------+------+-----+-------------------+-----------------------------+
| db             | char(64)     | NO   | PRI | NULL              |                             |
| tbl            | char(64)     | NO   | PRI | NULL              |                             |
| chunk          | int(11)      | NO   | PRI | NULL              |                             |
| chunk_time     | float        | YES  |     | NULL              |                             |
| chunk_index    | varchar(200) | YES  |     | NULL              |                             |
| lower_boundary | text         | YES  |     | NULL              |                             |
| upper_boundary | text         | YES  |     | NULL              |                             |
| this_crc       | char(40)     | NO   |     | NULL              |                             |
| this_cnt       | int(11)      | NO   |     | NULL              |                             |
| master_crc     | char(40)     | YES  |     | NULL              |                             |
| master_cnt     | int(11)      | YES  |     | NULL              |                             |
| ts             | timestamp    | NO   | MUL | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+----------------+--------------+------+-----+-------------------+-----------------------------+

こちらのmaster_crcがマスターで計算されたチェックサムの値,this_crcがそのデータベースで計算されたチェックサムの値となります。なので,スレーブ側で下記のようなクエリを流し,master_crcとthis_crcが同じ値であるかどうかで,データの整合性が取れているか確認することができます。

> SELECT * FROM mysql.pt_tcs WHERE this_crc <> master_crc;                                                                                               
+----+-----+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
| db | tbl | chunk | chunk_time | chunk_index | lower_boundary | upper_boundary | this_crc | this_cnt | master_crc | master_cnt | ts                  |
+----+-----+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
| d1 | t3  |     1 |   0.002825 | NULL        | NULL           | NULL           | dbc6a82c |        1 | 0          |          0 | 2018-03-29 01:47:28 |
+----+-----+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
1 rows in set (0.00 sec)

t3テーブルに差分があることが確認できました。

pt-table-checksumについては他にもさまざまなオプションがあります。詳細な使い方についてはpercona-toolkitのpt-table-checksumの公式マニュアルをご確認ください。

まとめ

今回はデータベースの比較方法として3つの方法を紹介しました。

レプリケーションを組んだ状態であればpt-table-checksumを利用し,RBRのレプリケーションで,データベースの更新がないことが確認できる場合はmysqldbcompareを利用するなど,状況に合わせて使い分けてデータを比較しましょう。

著者プロフィール

深町日出海(ふかまちひでみ)

GMOメディア株式会社のデータベースエンジニア。主にOracleとMySQLを担当。得意なプログラム言語はJava。MySQLの好きなところはTABLEやINDEXの識別子に64byteまで使えるところ。

Twitter:@lhfukamachi

コメント

コメントの記入