sync-diff-inspector ใฆใผใถใผใฌใคใ
ๅๆๅทฎๅใคใณในใใฏใฟใผใใผใฟใใผในใซไฟๅญใใใฆใใใใผใฟใ MySQL ใใญใใณใซใจๆฏ่ผใใใใใซไฝฟ็จใใใใใผใซใงใใใใจใใฐใMySQL ใฎใใผใฟใจ TiDB ใฎใใผใฟใMySQL ใฎใใผใฟใจ MySQL ใฎใใผใฟใTiDB ใฎใใผใฟใจ TiDB ใฎใใผใฟใๆฏ่ผใงใใพใใใใใซใใใฎใใผใซใไฝฟ็จใใฆใๅฐ้ใฎใใผใฟใซไธ่ฒซๆงใใชใใทใใชใชใงใใผใฟใไฟฎๅพฉใใใใจใใงใใพใใ
ใใฎใฌใคใใงใฏใsync-diff-inspector ใฎไธปใชๆฉ่ฝใ็ดนไปใใใใฎใใผใซใๆงๆใใฆไฝฟ็จใใๆนๆณใซใคใใฆ่ชฌๆใใพใใ sync-diff-inspector ใใใฆใณใญใผใใใใซใฏใๆฌกใฎใใใใใฎๆนๆณใไฝฟ็จใใพใใ
ใใคใใชใใใฑใผใธใ sync-diff-inspector ใใคใใช ใใใฑใผใธใฏTiDB Toolkitใซๅซใพใใฆใใพใใ TiDB Toolkitใใใฆใณใญใผใใใใซใฏใ TiDB ใใผใซใใใฆใณใญใผใใๅ็ งใใฆใใ ใใใ
Docker ใคใกใผใธใๆฌกใฎใณใใณใใๅฎ่กใใฆใใฆใณใญใผใใใพใใ
docker pull pingcap/tidb-enterprise-tools:nightly
ไธปใชๆฉ่ฝ
- ใใผใใซ ในใญใผใใจใใผใฟใๆฏ่ผใใ
- ใใผใฟใฎไธๆดๅใๅญๅจใใๅ ดๅใซใใผใฟใไฟฎๅพฉใใใใใซไฝฟ็จใใใ SQL ในใใผใใกใณใใ็ๆใใ
- ใตใใผใในใญใผใใพใใฏใใผใใซๅใ็ฐใชใใใผใใซใฎใใผใฟ ใใงใใฏ
- ใตใใผใใทใฃใผใใฃใณใฐ ใทใใชใชใงใฎใใผใฟ ใใงใใฏ
- ใตใใผใTiDB ใขใใในใใชใผใ -ใใฆใณในใใชใผใ ใฏใฉในใฟใฎใใผใฟ ใใงใใฏ
- ใตใใผใDM ใฌใใชใฑใผใทใงใณ ใทใใชใชใงใฎใใผใฟ ใใงใใฏ
sync-diff-inspector ใฎๅถ้ไบ้
MySQL ใจ TiDB ้ใฎใใผใฟ็งป่กใงใฏใใชใณใฉใคใณ ใใงใใฏใฏใตใใผใใใใฆใใพใใใไธๆตไธๆตใใงใใฏใชในใใซใใผใฟใๆธใ่พผใพใใฆใใชใใใจใใใใณ็นๅฎใฎ็ฏๅฒใฎใใผใฟใๅคๆดใใใฆใใชใใใจใ็ขบ่ชใใพใใ
range
ใ่จญๅฎใใใจใใใฎ็ฏๅฒใฎใใผใฟใ็ขบ่ชใงใใพใใTiDB ใจ MySQL ใงใฏใ
FLOAT
ใDOUBLE
ใใใใณใใฎไปใฎๆตฎๅๅฐๆฐ็นๅใฎๅฎ่ฃ ใ็ฐใชใใพใใFLOAT
ใจDOUBLE
ใใใงใใฏใตใ ใฎ่จ็ฎใซใใใใ 6 ๆกใจ 15 ๆกใฎๆๅนๆฐๅญใไฝฟ็จใใพใใใใฎๆฉ่ฝใไฝฟ็จใใชใๅ ดๅใฏใignore-columns
ใ่จญๅฎใใฆใใใใฎๅใฎใใงใใฏใในใญใใใใพใใไธปใญใผใพใใฏไธๆใฎใคใณใใใฏในใๅซใพใชใใใผใใซใฎใใงใใฏใใตใใผใใใพใใใใ ใใใใผใฟใซไธ่ฒซๆงใใชใๅ ดๅใ็ๆใใใ SQL ในใใผใใกใณใใฏใใผใฟใๆญฃใใไฟฎๅพฉใงใใชใๅฏ่ฝๆงใใใใพใใ
sync-diff-inspector ใฎใใผใฟใใผในๆจฉ้
sync-diff-inspector ใฏใใใผใใซ ในใญใผใใฎๆ ๅ ฑใๅๅพใใใใผใฟใใฏใจใชใใๅฟ ่ฆใใใใพใใๅฟ ่ฆใชใใผใฟใใผในๆจฉ้ใฏๆฌกใฎใจใใใงใใ
- ใขใใในใใชใผใ ใใผใฟใใผใน
SELECT
(ๆฏ่ผใฎใใใซใใผใฟใใใงใใฏ)SHOW_DATABASES
(ใใฅใผ ใใผใฟใใผในๅ)RELOAD
(ใใฅใผ ใใผใใซ ในใญใผใ)
- ใใฆใณในใใชใผใ ใใผใฟใใผใน
SELECT
(ๆฏ่ผใฎใใใซใใผใฟใใใงใใฏ)SHOW_DATABASES
(ใใฅใผ ใใผใฟใใผในๅ)RELOAD
(ใใฅใผ ใใผใใซ ในใญใผใ)
ใณใณใใฃใฐใฌใผใทใงใณใใกใคใซใฎ่ชฌๆ
sync-diff-inspector ใฎๆงๆใฏใๆฌกใฎ้จๅใงๆงๆใใใพใใ
Global config
: ใใงใใฏใใในใฌใใใฎๆฐใ็็พใใใใผใใซใไฟฎๆญฃใใใใใซ SQL ในใใผใใกใณใใใจใฏในใใผใใใใใฉใใใใใผใฟใใญใฃใณใใใใใฉใใใชใฉใฎไธ่ฌ็ใชๆงๆใDatabases config
: ใขใใในใใชใผใ ใใผใฟใใผในใจใใฆใณในใใชใผใ ใใผใฟใใผในใฎใคใณในใฟใณในใๆงๆใใพใใRoutes
: ใขใใในใใชใผใ ใฎ่คๆฐใฎในใญใผใๅใใใฆใณในใใชใผใ ใฎๅไธใฎในใญใผใๅใจไธ่ดใใใใใฎใซใผใซ(ใชใใทใงใณ) ใTask config
: ใใงใใฏ็จใฎใใผใใซใๆงๆใใพใใใขใใในใใชใผใ ใใผใฟใใผในใจใใฆใณในใใชใผใ ใใผใฟใใผในใฎ้ใซ็นๅฎใฎใใใใณใฐ้ขไฟใใใใใผใใซใใ็นๅฅใช่ฆไปถใใใใใผใใซใใใๅ ดๅใฏใใใใใฎใใผใใซใๆงๆใใๅฟ ่ฆใใใใพใใTable config
: ๆๅฎใใใ็ฏๅฒใๅใ็ก่ฆใใใชใฉใ็นๅฎใฎใใผใใซใฎ็นๅฅใชๆงๆ(ใชใใทใงใณ) ใ
ไปฅไธใฏใๅฎๅ จใชๆงๆใใกใคใซใฎ่ชฌๆใงใใ
- ๆณจ: ๅๅใฎๅพใซ
s
ใไปใใฆใใๆงๆใฏ่คๆฐใฎๅคใๆใคใใจใใงใใใใใ่งๆฌๅผง[]
ใไฝฟ็จใใฆๆงๆๅคใๅซใใๅฟ ่ฆใใใใพใใ
# Diff Configuration.
######################### Global config #########################
# The number of goroutines created to check data. The number of connections between sync-diff-inspector and upstream/downstream databases is slightly greater than this value.
check-thread-count = 4
# If enabled, SQL statements is exported to fix inconsistent tables.
export-fix-sql = true
# Only compares the table structure instead of the data.
check-struct-only = false
######################### Datasource config #########################
[data-sources]
[data-sources.mysql1] # mysql1 is the only custom ID for the database instance. It is used for the following `task.source-instances/task.target-instance` configuration.
host = "127.0.0.1"
port = 3306
user = "root"
password = "" # The password for connecting to the upstream database. It can be plain text or Base64-encoded.
# (optional) Use mapping rules to match multiple upstream sharded tables. Rule1 and rule2 are configured in the following Routes section.
route-rules = ["rule1", "rule2"]
[data-sources.tidb0]
host = "127.0.0.1"
port = 4000
user = "root"
password = "" # The password for connecting to the downstream database. It can be plain text or Base64-encoded.
# (optional) Use TLS to connect TiDB.
# security.ca-path = ".../ca.crt"
# security.cert-path = ".../cert.crt"
# security.key-path = ".../key.crt"
# (optional) Use the snapshot feature. If enabled, historical data is used for comparison.
# snapshot = "386902609362944000"
# When "snapshot" is set to "auto", the last syncpoints generated by TiCDC in the upstream and downstream are used for comparison. For details, see <https://github.com/pingcap/tidb-tools/issues/663>.
# snapshot = "auto"
########################### Routes ##############################
# To compare the data of a large number of tables with different schema names or table names, or check the data of multiple upstream sharded tables and downstream table family, use the table-rule to configure the mapping relationship. You can configure the mapping rule only for the schema or table. Also, you can configure the mapping rules for both the schema and the table.
[routes]
[routes.rule1] # rule1 is the only custom ID for the configuration. It is used for the above `data-sources.route-rules` configuration.
schema-pattern = "test_*" # Matches the schema name of the data source. Supports the wildcards "*" and "?"
table-pattern = "t_*" # Matches the table name of the data source. Supports the wildcards "*" and "?"
target-schema = "test" # The name of the schema in the target database
target-table = "t" # The name of the target table
[routes.rule2]
schema-pattern = "test2_*" # Matches the schema name of the data source. Supports the wildcards "*" and "?"
table-pattern = "t2_*" # Matches the table name of the data source. Supports the wildcards "*" and "?"
target-schema = "test2" # The name of the schema in the target database
target-table = "t2" # The name of the target table
######################### task config #########################
# Configures the tables of the target database that need to be compared.
[task]
# output-dir saves the following information:
# 1 sql: The SQL file to fix tables that is generated after error is detected. One chunk corresponds to one SQL file.
# 2 log: sync-diff.log
# 3 summary: summary.txt
# 4 checkpoint: a dir
output-dir = "./output"
# The upstream database. The value is the unique ID declared by data-sources.
source-instances = ["mysql1"]
# The downstream database. The value is the unique ID declared by data-sources.
target-instance = "tidb0"
# The tables of downstream databases to be compared. Each table needs to contain the schema name and the table name, separated by '.'
# Use "?" to match any character and "*" to match characters of any length.
# For detailed match rules, refer to golang regexp pkg: https://github.com/google/re2/wiki/Syntax.
target-check-tables = ["schema*.table*", "!c.*", "test2.t2"]
# (optional) Extra configurations for some tables, Config1 is defined in the following table config example.
target-configs = ["config1"]
######################### Table config #########################
# Special configurations for specific tables. The tables to be configured must be in `task.target-check-tables`.
[table-configs.config1] # config1 is the only custom ID for this configuration. It is used for the above `task.target-configs` configuration.
# The name of the target table, you can use regular expressions to match multiple tables, but one table is not allowed to be matched by multiple special configurations at the same time.
target-tables = ["schema*.test*", "test2.t2"]
# (optional) Specifies the range of the data to be checked
# It needs to comply with the syntax of the WHERE clause in SQL.
range = "age > 10 AND age < 20"
# (optional) Specifies the column used to divide data into chunks. If you do not configure it,
# sync-diff-inspector chooses an appropriate column (primary key, unique key, or a field with index).
index-fields = ["col1","col2"]
# (optional) Ignores checking some columns such as some types (json, bit, blob, etc.)
# that sync-diff-inspector does not currently support.
# The floating-point data type behaves differently in TiDB and MySQL. You can use
# `ignore-columns` to skip checking these columns.
ignore-columns = ["",""]
# (optional) Specifies the size of the chunk for dividing the table. If not specified, this configuration can be deleted or be set as 0.
chunk-size = 0
# (optional) Specifies the "collation" for the table. If not specified, this configuration can be deleted or be set as an empty string.
collation = ""
sync-diff-inspector ใๅฎ่กใใ
ๆฌกใฎใณใใณใใๅฎ่กใใพใใ
./sync_diff_inspector --config=./config.toml
ใใฎใณใใณใใฏใ output-dir
of config.toml
ใจใญใฐsync_diff.log
ใงใใงใใฏ ใฌใใผใsummary.txt
ใๅบๅใใพใใ output-dir
ใงใฏใ config. toml
ใใกใคใซใฎใใใทใฅๅคใงๅๅใไปใใใใใใฉใซใใผใ็ๆใใใพใใใใฎใใฉใซใใผใซใฏใใใฌใผใฏใใคใณใใฎใใงใใฏใใคใณใ ใใผใๆ
ๅ ฑใจใใใผใฟใ็็พใใฆใใๅ ดๅใซ็ๆใใใ SQL ใใกใคใซใๅซใพใใพใใ
้ฒๆๆ ๅ ฑ
sync-diff-inspector ใฏใๅฎ่กๆใซ้ฒๆๆ
ๅ ฑใstdout
ใซ้ไฟกใใพใใ้ฒ่ก็ถๆณๆ
ๅ ฑใซใฏใใใผใใซๆง้ ใฎๆฏ่ผ็ตๆใใใผใใซ ใใผใฟใฎๆฏ่ผ็ตๆใ้ฒ่ก็ถๆณใใผใๅซใพใใพใใ
ใใผใ๏ผ
่กจ็คบๅนๆใ็ขบไฟใใใซใฏใ่กจ็คบใฆใฃใณใใฆใฎๅน ใ 80 ๆๅญไปฅไธใซใใฆใใ ใใใ
A total of 2 tables need to be compared
Comparing the table structure of ``sbtest`.`sbtest96`` ... equivalent
Comparing the table structure of ``sbtest`.`sbtest99`` ... equivalent
Comparing the table data of ``sbtest`.`sbtest96`` ... failure
Comparing the table data of ``sbtest`.`sbtest99`` ...
_____________________________________________________________________________
Progress [==========================================================>--] 98% 193/200
A total of 2 tables need to be compared
Comparing the table structure of ``sbtest`.`sbtest96`` ... equivalent
Comparing the table structure of ``sbtest`.`sbtest99`` ... equivalent
Comparing the table data of ``sbtest`.`sbtest96`` ... failure
Comparing the table data of ``sbtest`.`sbtest99`` ... failure
_____________________________________________________________________________
Progress [============================================================>] 100% 0/0
The data of `sbtest`.`sbtest99` is not equal
The data of `sbtest`.`sbtest96` is not equal
The rest of tables are all equal.
The patch file has been generated in
'output/fix-on-tidb2/'
You can view the comparision details through 'output/sync_diff.log'
ๅบๅใใกใคใซ
ๅบๅใใกใคใซใฎใใฃใฌใฏใใชๆง้ ใฏๆฌกใฎใจใใใงใใ
output/
|-- checkpoint # Saves the breakpoint information
| |-- bbfec8cc8d1f58a5800e63aa73e5 # Config hash. The placeholder file which identifies the configuration file corresponding to the output directory (output/)
โ |-- DO_NOT_EDIT_THIS_DIR
โ โ-- sync_diff_checkpoints.pb # The breakpoint information
|
|-- fix-on-target # Saves SQL files to fix data inconsistency
| |-- xxx.sql
| |-- xxx.sql
| โ-- xxx.sql
|
|-- summary.txt # Saves the summary of the check results
โ-- sync_diff.log # Saves the output log informatiion when sync-diff-inspector is running
ใญใฐ
sync-diff-inspector ใฎใญใฐใฏ${output}/sync_diff.log
ใซไฟๅญใใใใใฎใใก${output}
ใฏconfig.toml
ใใกใคใซใฎoutput-dir
ใฎๅคใงใใ
้ฒๆ
ๅฎ่กไธญใฎ sync-diff-inspector ใฏๅฎๆ็ใซ (10 ็งใใจใซ) ใใงใใฏใใคใณใใฎ้ฒ่ก็ถๆณใๅบๅใใใใฏ${output}/checkpoint/sync_diff_checkpoints.pb
ใซใใใใใฎใใก${output}
ใฏconfig.toml
ใใกใคใซใฎoutput-dir
ใฎๅคใงใใ
็ตๆ
ใใงใใฏใๅฎไบใใใจใsync-diff-inspector ใใฌใใผใใๅบๅใใพใใใใใฏ${output}/summary.txt
ใซใใใ ${output}
ใฏconfig.toml
ใใกใคใซใฎoutput-dir
ใฎๅคใงใใ
+---------------------+--------------------+----------------+
| TABLE | STRUCTURE EQUALITY | DATA DIFF ROWS |
+---------------------+--------------------+----------------+
| `sbtest`.`sbtest99` | true | +97/-97 |
| `sbtest`.`sbtest96` | true | +0/-101 |
+---------------------+--------------------+----------------+
Time Cost: 16.75370462s
Average Speed: 113.277149MB/s
TABLE: ๅฏพๅฟใใใใผใฟใใผในๅใจใใผใใซๅ
STRUCTURE EQUALITY: ใใผใใซๆง้ ใๅใใใฉใใใใใงใใฏใใพใ
ใใผใฟๅทฎๅ่ก:
rowAdd
/rowDelete
.ใใผใใซใไฟฎๆญฃใใใใใซ่ฟฝๅ /ๅ้คใใๅฟ ่ฆใใใ่กใฎๆฐใ็คบใใพใ
ไธๆดๅใใผใฟใไฟฎๆญฃใใใใใฎ SQL ในใใผใใกใณใ
ใใผใฟ ใใงใใฏ ใใญใปในไธญใซ็ฐใชใ่กใๅญๅจใใๅ ดๅใใใใใไฟฎๆญฃใใใใใซ SQL ในใใผใใกใณใใ็ๆใใใพใใใใฃใณใฏๅ
ใซใใผใฟใฎไธๆดๅใๅญๅจใใๅ ดๅใ chunk.Index
ใจใใๅๅใฎ SQL ใใกใคใซใ็ๆใใใพใใ SQL ใใกใคใซใฏ${output}/fix-on-${instance}
ใซใใใ ${instance}
ใฏconfig.toml
ใใกใคใซใฎtask.target-instance
ใฎๅคใงใใ
SQL ใใกใคใซใซใฏใใใฃใณใฏใๅฑใใ tale ใจ็ฏๅฒๆ ๅ ฑใๅซใพใใฆใใพใใ SQL ใใกใคใซใซใคใใฆใฏใๆฌกใฎ 3 ใคใฎ็ถๆณใ่ๆ ฎใใๅฟ ่ฆใใใใพใใ
- ใใฆใณในใใชใผใ ใใผใฟใใผในใฎ่กใๆฌ ่ฝใใฆใใๅ ดๅใREPLACE ในใใผใใกใณใใ้ฉ็จใใใพใใ
- ใใฆใณในใใชใผใ ใใผใฟใใผในใฎ่กใๅ้ทใชๅ ดๅใDELETE ในใใผใใกใณใใ้ฉ็จใใใพใใ
- ใใฆใณในใใชใผใ ใใผใฟใใผในใฎ่กใฎไธ้จใฎใใผใฟใซไธ่ฒซๆงใใชใๅ ดๅใREPLACE ในใใผใใกใณใใ้ฉ็จใใใไธ่ฒซๆงใฎใชใๅใฏ SQL ใใกใคใซๅ ใฎๆณจ้ใงใใผใฏใใใพใใ
-- table: sbtest.sbtest99
-- range in sequence: (3690708) < (id) <= (3720581)
/*
DIFF COLUMNS โ `K` โ `C` โ `PAD`
โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ
source data โ 2501808 โ 'hello' โ 'world'
โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ
target data โ 5003616 โ '0709824117-9809973320-4456050422' โ '1714066100-7057807621-1425865505'
โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ
*/
REPLACE INTO `sbtest`.`sbtest99`(`id`,`k`,`c`,`pad`) VALUES (3700000,2501808,'hello','world');
ใใผใ
- sync-diff-inspector ใฏใใใผใฟใใใงใใฏใใใจใใซไธๅฎ้ใฎใตใผใใผใชใฝใผในใๆถ่ฒปใใพใใๅถๆฅญๆ้ใฎใใผใฏๆใซ sync-diff-inspector ใไฝฟ็จใใฆใใผใฟใใใงใใฏใใใใจใฏ้ฟใใฆใใ ใใใ
- MySQL ใฎใใผใฟใจ TiDB ใฎใใผใฟใๆฏ่ผใใๅใซใใใผใใซใฎ็
งๅ้ ๅบๆงๆใซๆณจๆใใฆใใ ใใใไธปใญใผใพใใฏใฆใใผใฏใญใผใ
varchar
ๅใงใMySQL ใจ TiDB ใฎ็ งๅ้ ๅบๆงๆใ็ฐใชใๅ ดๅใ็ งๅ้ ๅบใฎๅ้กใซใใใๆ็ต็ใชใใงใใฏ็ตๆใๆญฃใใใชใๅฏ่ฝๆงใใใใพใใ sync-diff-inspector ๆงๆใใกใคใซใซ็ งๅ้ ๅบใ่ฟฝๅ ใใๅฟ ่ฆใใใใพใใ - sync-diff-inspector ใฏใๆๅใซ TiDB ็ตฑ่จใซๅพใฃใฆใใผใฟใใใฃใณใฏใซๅๅฒใใพใใ็ตฑ่จใฎๆญฃ็ขบๆงใไฟ่จผใใๅฟ
่ฆใใใใพใใ TiDB ใตใผใใผใฎใฏใผใฏใญใผใใ่ปฝใๅ ดๅใฏใ
analyze table {table_name}
ใณใใณใใๆๅใงๅฎ่กใงใใพใใ table-rules
ใซ็นใซๆณจๆใใฆใใ ใใใschema-pattern="test1"
target-schema="test2"
ใใใณtarget-table = "t_2"
ใ่จญๅฎใใtable-pattern = "t_1"
ใtest1
.ใฝใผใน ใใผใฟใใผในใฎt_1
ในใญใผใใจtest2
.ใฟใผใฒใใ ใใผใฟใใผในใฎt_2
ในใญใผใใๆฏ่ผใใใพใใใทใฃใผใใฃใณใฐใฏ sync-diff-inspector ใงใใใฉใซใใงๆๅนใซใชใฃใฆใใใใใใฝใผใน ใใผใฟใใผในใซtest2
ใใใๅ ดๅใt_2
ใใผใใซใtest1
ใt_1
ใใผใใซใจtest2
ใใทใฃใผใใฃใณใฐใจใใฆๆฉ่ฝใใใฝใผใน ใใผใฟใใผในใฎt_2
ใใผใใซใtest2
ใจๆฏ่ผใใใพใใใฟใผใฒใใ ใใผใฟใใผในใฎt_2
ใใผใใซใ- ็ๆใใใ SQL ใใกใคใซใฏใใใผใฟใไฟฎๅพฉใใใใใฎๅ็ งใจใใฆใฎใฟไฝฟ็จใใใใใใใฎ SQL ในใใผใใกใณใใๅฎ่กใใฆใใผใฟใไฟฎๅพฉใใๅใซ็ขบ่ชใใๅฟ ่ฆใใใใพใใ