๐Ÿ“ฃ

TiDB Cloud Serverless ใŒ
Starter
ใซๅค‰ใ‚ใ‚Šใพใ—ใŸ๏ผใ“ใฎใƒšใƒผใ‚ธใฏ่‡ชๅ‹•็ฟป่จณใ•ใ‚ŒใŸใ‚‚ใฎใงใ™ใ€‚
ๅŽŸๆ–‡ใฏใ“ใกใ‚‰ใ‹ใ‚‰ใ”่ฆงใใ ใ•ใ„ใ€‚

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

ไธปใชๆฉŸ่ƒฝ

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 ใ‚นใƒ†ใƒผใƒˆใƒกใƒณใƒˆใ‚’ๅฎŸ่กŒใ—ใฆใƒ‡ใƒผใ‚ฟใ‚’ไฟฎๅพฉใ™ใ‚‹ๅ‰ใซ็ขบ่ชใ™ใ‚‹ๅฟ…่ฆใŒใ‚ใ‚Šใพใ™ใ€‚

ใ“ใฎใƒšใƒผใ‚ธใฏๅฝนใซ็ซ‹ใกใพใ—ใŸใ‹๏ผŸ