📚

sync-diff-inspectorの使い方：MySQL（or TiDB）でデータの差分チェック&修正ツール

2024/02/01に公開

MySQL

TiDB

tech

２つの同じデータを持つはずのDB同士を実際に比較してみたいなぁということはないでしょうか？
レプリケーションであったり、（何らかの理由で）DBを移行することを考える場合にはたまにあるかと思います（めったにない）。

この記事では、MySQL（もしくはMySQLと互換性のあるDB）同士を比較する際に利用可能なsync-diff-inspectorの使いかたを紹介します。

※sync-diff-inspectorはTiDBのコミュニティツールとして提供されているため、実際には移行元がMySQL、移行先がTiDBのときのデータチェック目的で一番利用されています（おそらく）

https://github.com/pingcap/tidb-tools

sync-diff-inspectorとは？

以下のイメージのように

比較元（MySQL）と比較先（TiDB）双方のテーブル構造を比較チェック
構造が同一のテーブルのデータを比較チェック
（2.の）差分解消のためのSQLを生成する
ツールです。実行後の出力は、上記の比較結果のサマリーと実行ログ、修正用SQLファイルの3つです。

実行時の前提として、比較対象のデータに更新がない状態が想定されています。（理想的にはある静止点で比較するイメージ）

また比較元を正として、比較先を修正するためのSQLステートメントも生成します

また、TiDBはNewSQL（シャーディング設計、実装不要でWriteスケール可能）を謳っていることもあるため、シャーディングされた複数のMySQLをひとつのTiDBへ移行する（まとめる）という発想もあり、
そういったケースにも応えられるように双方のDB間で

双方のDBで異なるスキーマ/テーブル名のデータチェック
MySQLではシャーディングされたテーブルをTiDBではひとつのテーブルにまとめる、という前提でのデータチェック
などといったパターンもひろってくれる設計になっています。

詳しくは以下のDocsを確認してください。

今回の前提

環境

sync-diff-inspectorの大枠の挙動や使い方を確認する目的のため、
今回は同一EC2インスタンス上にあるMySQL、TiDB（tiup playground）と同じマシンにsync-diff-inspectorをインストールして確認していきます。
（ちょうど手元にあった環境を利用）

MySQLとTiDBのバージョンは以下の通り。

MySQL

% mysql --version
mysql  Ver 14.14 Distrib 5.7.39, for Linux (x86_64) using  EditLine wrapper

TiDB（tiup playgroundを利用）

% tiup playground v6.5.7

※tiup playgroundについてはこちらの記事を参考にどうぞ
※ちょっと古いバージョンですが。。。

sync-diff-inspectorのインストール手順は"使い方"の段でまとめます。

比較パターン（MySQLとTiDBのデータ準備）

今回はsync-diff-inspectorの挙動をざっくり抑えるために以下のパターンで検証してみます。

Table Name	構造	データ	期待結果
`test01`	一致	一致	一致していることがわかる
`test02`	不一致	一致	テーブル構造が不一致であることがわかる
`test03`	一致	不一致	テーブル構造は一致しているが、一致していないデータの対象とその差分を修正するSQLが出力される

データが不一致となるケースであるtest03というテーブルには以下のようなパターンでMySQL、TiDB双方にレコードを入れておきます。

シナリオ	MySQL	TiDB
完全一致	`id=1,name="a"`	`id=1,name="a"`
PK一致、列不一致	`id=2,name="b1"`	`id=2,name="b2"`
PK不一致（MySQL：あり / TiDB：なし）	`id=3,name="c"`
PK不一致（MySQL：なし / TiDB：あり）		`id=4,name="d"`

ここからは上記に沿ったデータを作成していきます。

比較データの準備

テーブル構造（CREATE TABLE）

test01：MySQL/TiDB共通

CREATE TABLE test01(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(64) )DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

test02：MySQL

CREATE TABLE test02(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(64) )DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

test02：TiDB

CREATE TABLE test02(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(32) )DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

test03：MySQL/TiDB共通

CREATE TABLE test03(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(64) )DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

挿入データ（INSERT）

test01：MySQL/TiDB共通

INSERT INTO test01(name) values('AAA'),('bbb'),('CCC'),('ddd'),('eee');

test02：MySQL/TiDB共通

INSERT INTO test02(name) values('AAA'),('bbb'),('CCC'),('ddd'),('eee');

test03：MySQL

INSERT INTO test03(name) values('a'),('b1'),('c');

test03：TiDB

INSERT INTO test03(name) values('a'),('b2');
INSERT INTO test03(id, name) values(4,'d');

データ確認

若干余談ですが、GUIのSQLクライアントであるDBeaverもTiDBへの接続サポートしています。

他にもあります↓↓↓

各テーブルはこんな状態です。

`test01`：左：MySQL　右：TiDB

`test02`：左：MySQL　右：TiDB

`test03`：左：MySQL　右：TiDB

sync-diff-inspectorの使い方

sync-diff-inspectorのインストール

Docsに記載のある通り、sync-diff-inspectorはtidb-community-toolkitに含まれており、

https://download.pingcap.org/tidb-community-toolkit-{version}-linux-{arch}.tar.gz

{version}と{arch}はお使いの環境に合わせて適切なモノを指定してください。

例えば今回は以下のモノを利用しています。※だいぶ前にインストールしていたので少々古いものになっています

https://download.pingcap.org/tidb-community-toolkit-v6.2.0-linux-amd64.tar.gz

ダウンロードしたら展開し、その中にsync_diff_inspectorがあることを確認します。
以降は展開したディレクトリで作業をしていきます。

ちなみにDocker Imageはこちら。

docker pull pingcap/tidb-tools:latest

実行前の事前準備

1. Outputファイルの出力先ディレクトリの作成

冒頭で示した通り、いくつかのoutputファイルが生成されますので、その出力先ディレクトリを用意しておきます。

2. Inputファイルの作成

config.tomlファイルを用意します。
中身は概ね以下のような構成です。

Global config:一般的な設定。修正用SQLファイルの出力要否やデータチェックスキップ（≒構造のみ比較）など。
Databases config: 比較元、先含め比較対象となるデータベースのインスタンス設定。
Routes (optional): 比較元と先でのテーブルのマッピングを指定する。比較元がシャーディングされているケースで利用。
Task config:比較元と先の指定。テーブルとの関連性など。
Table config (optional):テーブルに関してより詳細な設定（範囲や条件を指定する場合）

なお、Databases configに記載する各DBへの接続ユーザーにはSELECT、SHOW_DATABASES、RELOADの権限が必要です。

このファイルの詳細やサンプルとして以下も参考になるかと思います。

今回ファイルの中身は以下のように最低限の内容にします。

# Diff Configuration.
######################### Global config #########################
export-fix-sql = true
# check data also
check-struct-only = false

######################### Datasource config #########################
[data-sources]
[data-sources.mysql1]
    host = localhost
    port = 3306
    user = "root"
    password = ""
    route-rules = ["rule1"]

[data-sources.tidb0]
    host = "127.0.0.1"
    port = 4000
    user = "root"
    password = ""

########################### Routes ###########################
# mapping rules
[routes]
[routes.rule1]
schema-pattern = "test"      # source db
target-schema = "test"         # target db

######################### Task config #########################
[task]
    output-dir = "./output/syncdifftest"
    source-instances = ["mysql1"]
    target-instance = "tidb0"
    # target tables to be checked
    target-check-tables = ["test.test*"]

実行

以下のように実行します。事前に設定したconfig.tomlファイルを指定しています。

./sync_diff_inspector --config=./config.toml

その他の実行時オプションは以下を確認してください。

結果確認

実行結果です。

A total of 3 tables need to be compared

Comparing the table structure of ``test`.`test02`` ... failure
Comparing the table structure of ``test`.`test03`` ... equivalent
Comparing the table structure of ``test`.`test01`` ... equivalent
Comparing the table data of ``test`.`test01`` ... equivalent
Comparing the table data of ``test`.`test03`` ... failure
_____________________________________________________________________________
Progress [============================================================>] 100% 0/0
The data of `test`.`test03` is not equal
The structure of `test`.`test02` is not equal, and data-check is skipped

The rest of tables are all equal.
The patch file has been generated in 
        'output/syncdifftest20240115/fix-on-tidb0/'
You can view the comparision details through './output/syncdifftest20240115/sync_diff.log'

データ構造チェックでtest02はfailure（不一致）となっており、後続のデータチェックはskipされています。
また、データ構造チェックをpassしたtest01とtest03は後続のデータチェック処理が実施されており、test03はデータチェックで不一致があるようです。

では、出力されたファイルをそれぞれ見ていきます。

`summary.txt`

各テーブルごとの比較結果のまとめが出力されています。
見た感じ期待どおりです。

Source Database

host = "172.10.60.39"
port = 3306
user = "root"

Target Databases

host = "127.0.0.1"
port = 4000
user = "root"

Comparison Result

The table structure and data in following tables are equivalent

+-----------------+---------+-----------+
|      TABLE      | UPCOUNT | DOWNCOUNT |
+-----------------+---------+-----------+
| `test`.`test01` |       5 |         5 |
+-----------------+---------+-----------+

The following tables contains inconsistent data

+-----------------+--------------------+----------------+---------+-----------+
|      TABLE      | STRUCTURE EQUALITY | DATA DIFF ROWS | UPCOUNT | DOWNCOUNT |
+-----------------+--------------------+----------------+---------+-----------+
| `test`.`test03` | true               | +2/-2          |       3 |         3 |
| `test`.`test02` | false              | +0/-0          |       0 |         0 |
+-----------------+--------------------+----------------+---------+-----------+

Time Cost: 57.703872ms
Average Speed: 0.002496MB/s

`sync_diff.log`

ここでは割愛しますが、実行ログが出力されますので問題があればこちらを参考にします。

`fix-on-tidb0`：`output`ディレクトリ配下のファイル

今回データ不一致はtest03テーブルのため、その差分解消用のsqlが出力されています。

考え方として、ソース（今回はMySQL）を正としてターゲット（TiDB）のデータを修正するためのクエリになるため、以下のようにMySQLのデータに合わせるかたちになっています。

-- table: test.test03
-- range in sequence: Full
/*
  DIFF COLUMNS ╏ `NAME`  
╍╍╍╍╍╍╍╍╍╍╍╍╍╍╍╋╍╍╍╍╍╍╍╍╍
  source data  ╏ 'b1'    
╍╍╍╍╍╍╍╍╍╍╍╍╍╍╍╋╍╍╍╍╍╍╍╍╍
  target data  ╏ 'b2'    
╍╍╍╍╍╍╍╍╍╍╍╍╍╍╍╋╍╍╍╍╍╍╍╍╍
*/
REPLACE INTO `test`.`test03`(`id`,`name`) VALUES (2,'b1');
REPLACE INTO `test`.`test03`(`id`,`name`) VALUES (3,'c');
DELETE FROM `test`.`test03` WHERE `id` = 4 AND `name` = 'd' LIMIT 1;

最後に

使い方のイメージはついたでしょうか？

また、TiDBのユーザーコミュニティ「TiUG」でも活用事例が紹介されています。