MySQL道普請便り

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

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

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のマニュアルをご確認ください。

著者プロフィール

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

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

Twitter:@lhfukamachi

コメント

コメントの記入