Data Check in the Sharding Scenario

sync-diff-inspector supports data check in the sharding scenario. Assume that you use the TiDB Data Migration tool to replicate data from multiple MySQL instances into TiDB, you can use sync-diff-inspector to check upstream and downstream data.

For scenarios where the number of upstream sharded tables is small and the naming rules of sharded tables do not have a pattern as shown below, you can use Datasource config to configure table-0, set corresponding rules and configure the tables that have the mapping relationship between the upstream and downstream databases. This configuration method requires setting all sharded tables.

shard-table-replica-1

Below is a complete example of the sync-diff-inspector configuration.

  1. # Diff Configuration.
  3. ######################### Global config #########################
  5. # The number of goroutines created to check data. The number of connections between upstream and downstream databases are slightly greater than this value
  6. check-thread-count = 4
  8. # If enabled, SQL statements is exported to fix inconsistent tables
  9. export-fix-sql = true
  11. # Only compares the table structure instead of the data
  12. check-struct-only = false
  14. ######################### Datasource config #########################
  15. [data-sources.mysql1]
  16.     host = "127.0.0.1"
  17.     port = 3306
  18.     user = "root"
  19.     password = ""
  21.     route-rules = ["rule1"]
  23. [data-sources.mysql2]
  24.     host = "127.0.0.1"
  25.     port = 3306
  26.     user = "root"
  27.     password = ""
  29.     route-rules = ["rule2"]
  31. [data-sources.tidb0]
  32.     host = "127.0.0.1"
  33.     port = 4000
  34.     user = "root"
  35.     password = ""
  37. ########################### Routes ###########################
  38. [routes.rule1]
  39. schema-pattern = "test"        # Matches the schema name of the data source. Supports the wildcards "*" and "?"
  40. table-pattern = "table-[1-2]"  # Matches the table name of the data source. Supports the wildcards "*" and "?"
  41. target-schema = "test"         # The name of the schema in the target database
  42. target-table = "table-0"       # The name of the target table
  44. [routes.rule2]
  45. schema-pattern = "test"      # Matches the schema name of the data source. Supports the wildcards "*" and "?"
  46. table-pattern = "table-3"    # Matches the table name of the data source. Supports the wildcards "*" and "?"
  47. target-schema = "test"       # The name of the schema in the target database
  48. target-table = "table-0"     # The name of the target table
  50. ######################### Task config #########################
  51. [task]
  52.     output-dir = "./output"
  54.     source-instances = ["mysql1", "mysql2"]
  56.     target-instance = "tidb0"
  58.     # The tables of downstream databases to be compared. Each table needs to contain the schema name and the table name, separated by '.'
  59.     target-check-tables = ["test.table-0"]

You can use table-rules for configuration when there are a large number of upstream sharded tables and the naming rules of all sharded tables have a pattern, as shown below:

shard-table-replica-2

Below is a complete example of the sync-diff-inspector configuration.

  1. # Diff Configuration.
  2. ######################### Global config #########################
  4. # The number of goroutines created to check data. The number of connections between upstream and downstream databases are slightly greater than this value.
  5. check-thread-count = 4
  7. # If enabled, SQL statements is exported to fix inconsistent tables.
  8. export-fix-sql = true
  10. # Only compares the table structure instead of the data.
  11. check-struct-only = false
  13. ######################### Datasource config #########################
  14. [data-sources.mysql1]
  15.     host = "127.0.0.1"
  16.     port = 3306
  17.     user = "root"
  18.     password = ""
  20. [data-sources.mysql2]
  21.     host = "127.0.0.1"
  22.     port = 3306
  23.     user = "root"
  24.     password = ""
  26. [data-sources.tidb0]
  27.     host = "127.0.0.1"
  28.     port = 4000
  29.     user = "root"
  30.     password = ""
  32. ########################### Routes ###########################
  33. [routes.rule1]
  34. schema-pattern = "test"      # Matches the schema name of the data source. Supports the wildcards "*" and "?"
  35. table-pattern = "table-*"    # Matches the table name of the data source. Supports the wildcards "*" and "?"
  36. target-schema = "test"       # The name of the schema in the target database
  37. target-table = "table-0"     # The name of the target table
  39. ######################### Task config #########################
  40. [task]
  41.     output-dir = "./output"
  42.     source-instances = ["mysql1", "mysql2"]
  44.     target-instance = "tidb0"
  46.     # The tables of downstream databases to be compared. Each table needs to contain the schema name and the table name, separated by '.'
  47.     target-check-tables = ["test.table-0"]

Note

If test.table-0 exists in the upstream database, the downstream database also compares this table.