MySQLではレプリケーション機能を使って容易にデータベースを複製することができますが、
今回はそのような時にデータを比較するやり方をいくつか説明します。なお、
CHECKSUM TABLE構文によるテーブルの比較
MySQLではテーブル保守のためのステートメントとして、
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つのデータベースからデータを比較し、
今、
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
比較した結果、
また、
$ 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形式で差分を取得することができました。このように、
詳細な使い方については、
pt-table-checksumを利用してデータを比較する
Percona Toolkitの中にpt-table-checksumというツールがあります。pt-table-checksumが今まで紹介したツールと違う点として、
DSNを使ったpt-table-checksumのデータ比較
デフォルトではレプリケーション先の確認として、
今回はマスターのmysqlデータベースに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を実施するときは、
$ 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
データ比較によって、
replicateオプションを利用してpt-table-checksumのデータ比較を実施する
DSNを利用せずにpt-table-checksumを実施する場合は、
pt-table-checksum --host <host名> --port <port> --user <user名> --password <password> --replicate <比較結果のテーブル名> --database <比較するデータベース名>今回はreplicateオプションを利用してmysqlデータベースにpt_
$ 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_
> 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を利用し、