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