MySQL道普請便り

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

MySQLではレプリケーション機能を使って容易にデータベースを複製することができますが、構築の際にバイナリーログのポジションを間違ったり、スレーブ側の設定が間違っていたりすると、違うデータベースができあがってしまう可能性があります。そのような場合に、2つのデータベースを比較することが必要となってきます。

今回はそのような時にデータを比較するやり方をいくつか説明します。なお、MySQLのバージョンは5.7.21、各テーブルはInnoDBストレージエンジンを利用しています。

CHECKSUM TABLE構文によるテーブルの比較

MySQLではテーブル保守のためのステートメントとして、CHECKSUM TABLE構文があります。CHECKSUM TABLE構文は、各テーブルの全データでチェックサム値を取得することができます。このチェックサム値を利用して、比較したいテーブルのデータが同じかどうかを検討することができます。

CHECKSUM TABLE構文は、もしデータが存在していない場合は0を返し、テーブルそのものが存在していない場合はNULLを返します。また、カンマ区切りでテーブル名を指定することで、複数テーブルに対してチェックサム値を取得することができます。CHECKSUM TABLE構文を実行中は読み取りロックが発生するので、大きなテーブルに対しては注意が必要です。

また、比較する2つのテーブルに対して、更新、変更がないことが前提となっているので、マスター側はテーブルをロックしたり、スレーブのレプリケーションを停止する必要があります。詳細については公式ドキュメントのCHECKSUM TABLE 構文をご確認ください。

> > CHECKSUM TABLE t1,t2,t3,t4;
+-------+------------+
| Table | Checksum   |
+-------+------------+
| d1.t1 | 1778287754 |
| d1.t2 | 1591722216 |
| d1.t3 |          0 |
| d1.t4 |       NULL |
+-------+------------+
4 rows in set, 1 warning (0.00 sec)

mysqldbcompareによるテーブルの比較

MySQL Utilitiesにmysqldbcompareという2つのデータベースを比較するツールがあります。このツールは2つのデータベースからデータを比較し、もし違うデータがあった場合はどのデータが違うかを検出することが可能です。また、比較する対象はテーブル定義やデータだけでなく、triggerやviewも比較対象となります。

今、localhost:3306に構築したデータベース(server1)と同じデータをもっているlocalhost:3307(server2)のデータベースを準備し、server2に異なったデータを幾つか入れてみます。

localhost:3307 > INSERT INTO t3 VALUES (10, 'dummy_data', 1, now());
Query OK, 1 row affected (0.01 sec)

localhost:3307 > CREATE VIEW v_t2 AS SELECT id, name FROM t2;
Query OK, 0 rows affected (0.01 sec)

この状態mysqldbcompareを実行してみます。mysqldbcompareは、以下の形で実行します。

mysqldbcompare --server1 <user>:<password>@<host>:<port> --server2 <user>:<password>@<host>:<port> <比較したいserver1のdatabase名>:<比較したいserver2のdatabase名> <オプション>
$ mysqldbcompare --server1 root:password1@localhost:3306 --server2 root:password2@localhost:3307 d1:d1 --run-all-tests
# WARNING: Using a password on the command line interface can be insecure.
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Checking databases d1 on server1 and d1 on server2
#
# WARNING: Objects in server2.d1 but not in server1.d1:
#         VIEW: v_t2
#
#                                                   Defn    Row     Data
# Type      Object Name                             Diff    Count   Check
# -------------------------------------------------------------------------
# TABLE     t1                                      pass    pass    -
#           - Compare table checksum                                pass
# TABLE     t2                                      pass    pass    -
#           - Compare table checksum                                pass
# TABLE     t3                                      pass    FAIL    -
#           - Compare table checksum                                FAIL
#           - Find row differences                                  FAIL
#
# Row counts are not the same among `d1`.`t3` and `d1`.`t3`.
#

# Rows in `d1`.`t3` not in `d1`.`t3`
+-----+-------------+-----+----------------------+
| id  | c1          | c2  | c3                   |
+-----+-------------+-----+----------------------+
| 10  | dummy_data  | 1   | 2018-03-27 21:28:55  |
+-----+-------------+-----+----------------------+


# Database consistency check failed.
#
# ...done

比較した結果、viewのワーニングと差分のデータを検出することができました。

また、オプションの--difftypeをsqlにして実行してみます。

$ mysqldbcompare --server1 root:password1@localhost:3306 --server2 root:password2@localhost:3307 d1:d1 --run-all-tests --difftype=sql
# WARNING: Using a password on the command line interface can be insecure.
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Checking databases d1 on server1 and d1 on server2
#
# WARNING: Objects in server2.d1 but not in server1.d1:
#         VIEW: v_t2
#
#                                                   Defn    Row     Data
# Type      Object Name                             Diff    Count   Check
# -------------------------------------------------------------------------
# TABLE     t1                                      pass    pass    -
#           - Compare table checksum                                pass
# TABLE     t2                                      pass    pass    -
#           - Compare table checksum                                pass
# TABLE     t3                                      pass    FAIL    -
#           - Compare table checksum                                FAIL
#           - Find row differences                                  FAIL
#
# Row counts are not the same among `d1`.`t3` and `d1`.`t3`.
#
# Transformation for --changes-for=server1:
#

INSERT INTO `d1`.`t3` (`id`, `c1`, `c2`, `c3`) VALUES('10', 'dummy_data', '1', '2018-03-27 21:28:55');


# Database consistency check failed.
#
# ...done

今度はSQL形式で差分を取得することができました。このように、mysqldbcompareを使ってrowレベルで差分を確認することができます。ただし、mysqldbcompareはデータ比較中にデータの変更、更新がないことが前提となっています。そのため、もしレプリケーションを組んでいる場合は、レプリケーションを停止して実行する必要があります。

詳細な使い方については、mysqldbcompareのマニュアルをご確認ください。

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を利用するなど、状況に合わせて使い分けてデータを比較しましょう。

おすすめ記事

記事・ニュース一覧