写点什么

sync-diff-inspector 比对表结构,索引

  • 2024-08-16
    北京
  • 本文字数:10877 字

    阅读完需:约 36 分钟

作者: Brian 原文来源:https://tidb.net/blog/f36c3f38

目的

以下对列默认值,列是否可为空,表的主键,表的唯一约束 不同的情况进行上右下表结构的比对

环境

上游环境:Mysql v5.7.37


下游环境:TiDB v6.5.6


tool 版本:sync-diff-inspector v6.5.7


Sync-diff-inspector 配置文件:


[tidb@vmxx.xx.xx-25 ~]$ cat sync-diff.yaml check-thread-count = 2export-fix-sql = truecheck-struct-only = false
[data-sources][data-sources.upstream] host = "xx.xx.xx.25" # 替换为实际上游集群 ip port = 3307 user = "root" password = "123" #snapshot = "448552618951966723" # 配置为实际的备份时间点[data-sources.downstream] host = "xx.xx.xx.25" # 替换为实际下游集群 ip port = 4000 user = "root" password = "123" #snapshot = "448553665253343269" # 配置为实际的恢复时间点
[task] output-dir = "./output" source-instances = ["upstream"] target-instance = "downstream" target-check-tables = ["t.t"]
复制代码

对比默认值

上游表结构:


root@localhost : t 06:55:43>>> show create table t;+-------+---------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                |+-------+---------------------------------------------------------------------------------------------------------------------------------------------+| t     | CREATE TABLE `t` (  `id` int(11) NOT NULL,  `name` varchar(20) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |+-------+---------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
复制代码


下游表结构:


mysql> show create table t;+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                                                        |+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| t     | CREATE TABLE `t` (  `id` int(11) NOT NULL,  `name` varchar(20) DEFAULT '404',  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
复制代码


对比结果:


上下游列默认值不同,可以校验通过。


[tidb@vmxx.xx.xx-25 ~]$ tidb-community-toolkit-v6.5.7-linux-amd64/sync_diff_inspector --config=sync-diff.yaml A total of 1 tables need to be compared
Comparing the table structure of ``t`.`t`` ... equivalentComparing the table data of ``t`.`t`` ... equivalent_____________________________________________________________________________Progress [============================================================>] 100% 0/0A total of 1 table have been compared and all are equal.You can view the comparision details through './output/sync_diff.log'
复制代码

对比列是否可为空

上游表结构:


root@localhost : t 06:55:43>>> show create table t;+-------+---------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                |+-------+---------------------------------------------------------------------------------------------------------------------------------------------+| t     | CREATE TABLE `t` (  `id` int(11) NOT NULL,  `name` varchar(20) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |+-------+---------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
复制代码


下游表结构:


mysql> show create table t;+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                                                   |+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| t     | CREATE TABLE `t` (  `id` int(11) NOT NULL,  `name` varchar(20) NOT NULL,  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
复制代码


对比结果:


上下游列是否可为空不同,可以校验通过。


[tidb@vmxx.xx.xx-25 ~]$ tidb-community-toolkit-v6.5.7-linux-amd64/sync_diff_inspector --config=sync-diff.yaml A total of 1 tables need to be compared
Comparing the table structure of ``t`.`t`` ... equivalentComparing the table data of ``t`.`t`` ... equivalent_____________________________________________________________________________Progress [============================================================>] 100% 0/0A total of 1 table have been compared and all are equal.You can view the comparision details through './output/sync_diff.log'
复制代码

对比表的主键

上游表结构:


root@localhost : t 07:15:15>>> show create table t;+-------+-----------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                          |+-------+-----------------------------------------------------------------------------------------------------------------------+| t     | CREATE TABLE `t` (  `id` int(11) NOT NULL,  `name` varchar(20) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |+-------+-----------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
复制代码


下游表结构:


mysql> show create table t;+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                                                       |+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| t     | CREATE TABLE `t` (  `id` int(11) NOT NULL,  `name` varchar(20) DEFAULT NULL,  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
复制代码


对比结果:


上下游中是否有主键不会校验,可以校验通过。


[tidb@vmxx.xx.xx-25 ~]$ tidb-community-toolkit-v6.5.7-linux-amd64/sync_diff_inspector --config=sync-diff.yaml A total of 1 tables need to be compared
Comparing the table structure of ``t`.`t`` ... equivalentComparing the table data of ``t`.`t`` ... equivalent_____________________________________________________________________________Progress [============================================================>] 100% 0/0A total of 1 table have been compared and all are equal.You can view the comparision details through './output/sync_diff.log'
复制代码

对比唯一索引和普通索引

上游表结构:


root@localhost : t 07:31:26>>> show create table t;+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                       |+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+| t     | CREATE TABLE `t` (  `id` int(11) NOT NULL,  `name` varchar(20) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `name` (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
复制代码


下游表结构:


mysql> show create table t;+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                                                                                     |+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| t     | CREATE TABLE `t` (  `id` int(11) NOT NULL,  `name` varchar(20) DEFAULT NULL,  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,  UNIQUE KEY `name` (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
复制代码


对比结果:


对比唯一索引和普通索引,可以校验通过。


[tidb@vmxx.xx.xx-25 ~]$ tidb-community-toolkit-v6.5.7-linux-amd64/sync_diff_inspector --config=sync-diff.yaml A total of 1 tables need to be compared
Comparing the table structure of ``t`.`t`` ... equivalentComparing the table data of ``t`.`t`` ... equivalent_____________________________________________________________________________Progress [============================================================>] 100% 0/0A total of 1 table have been compared and all are equal.You can view the comparision details through './output/sync_diff.log'
复制代码

对比索引数量不同

上游表结构:


root@localhost : t 07:37:12>>> show create table t;+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                                                                                        |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| t     | CREATE TABLE `t` (  `id` int(11) NOT NULL,  `name` varchar(20) DEFAULT NULL,  `addr` varchar(10) DEFAULT NULL,  PRIMARY KEY (`id`),  UNIQUE KEY `name` (`name`),  KEY `addr` (`addr`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
复制代码


下游表结构:


mysql> show create table t;+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                                                                                                                        |+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| t     | CREATE TABLE `t` (  `id` int(11) NOT NULL,  `name` varchar(20) DEFAULT NULL,  `addr` varchar(10) DEFAULT NULL,  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,  UNIQUE KEY `name` (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
复制代码


对比结果:


可以校验出来上下游表中的索引数量不同


[tidb@vmxx.xx.xx-25 ~]$ tidb-community-toolkit-v6.5.7-linux-amd64/sync_diff_inspector --config=sync-diff.yaml A total of 1 tables need to be compared
Comparing the table structure of ``t`.`t`` ... failureComparing the table data of ``t`.`t`` ... equivalent_____________________________________________________________________________Progress [============================================================>] 100% 0/0The structure of `t`.`t` is not equal
The rest of tables are all equal.
A total of 1 tables have been compared, 0 tables finished, 1 tables failed, 0 tables skipped.The patch file has been generated in 'output/fix-on-downstream/'You can view the comparision details through './output/sync_diff.log'
复制代码

对比单列索引覆盖列不同

上游表结构:


root@localhost : t 07:46:43>>> show create table t;+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                                                          |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| t     | CREATE TABLE `t` (  `id` int(11) NOT NULL,  `name` varchar(20) DEFAULT NULL,  `addr` varchar(10) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `addr` (`addr`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
复制代码


下游表结构:


mysql> show create table t;+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                                                                                                                 |+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| t     | CREATE TABLE `t` (  `id` int(11) NOT NULL,  `name` varchar(20) DEFAULT NULL,  `addr` varchar(10) DEFAULT NULL,  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,  KEY `name` (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)
复制代码


对比结果:


可以校验出来单列索引覆盖列不同


[tidb@vmxx.xx.xx-25 ~]$ tidb-community-toolkit-v6.5.7-linux-amd64/sync_diff_inspector --config=sync-diff.yaml A total of 1 tables need to be compared
Comparing the table structure of ``t`.`t`` ... failureComparing the table data of ``t`.`t`` ... equivalent_____________________________________________________________________________Progress [============================================================>] 100% 0/0The structure of `t`.`t` is not equal
The rest of tables are all equal.
A total of 1 tables have been compared, 0 tables finished, 1 tables failed, 0 tables skipped.The patch file has been generated in 'output/fix-on-downstream/'You can view the comparision details through './output/sync_diff.log'
复制代码

对比联合索引和单列索引

上游表结构:


root@localhost : t 07:46:46>>> show create table t;+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                                                          |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| t     | CREATE TABLE `t` (  `id` int(11) NOT NULL,  `name` varchar(20) DEFAULT NULL,  `addr` varchar(10) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `addr` (`addr`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
复制代码


下游表结构:


mysql> show create table t;+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                                                                                                                        |+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| t     | CREATE TABLE `t` (  `id` int(11) NOT NULL,  `name` varchar(20) DEFAULT NULL,  `addr` varchar(10) DEFAULT NULL,  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,  KEY `name` (`name`,`addr`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
复制代码


对比结果:


可以校验出联合索引和单列索引的差异


[tidb@vmxx.xx.xx-25 ~]$ tidb-community-toolkit-v6.5.7-linux-amd64/sync_diff_inspector --config=sync-diff.yaml A total of 1 tables need to be compared
Comparing the table structure of ``t`.`t`` ... failureComparing the table data of ``t`.`t`` ... equivalent_____________________________________________________________________________Progress [============================================================>] 100% 0/0The structure of `t`.`t` is not equal
The rest of tables are all equal.
A total of 1 tables have been compared, 0 tables finished, 1 tables failed, 0 tables skipped.The patch file has been generated in 'output/fix-on-downstream/'You can view the comparision details through './output/sync_diff.log'
复制代码

结论

对于 sync-diff-inspector 校验表结构,有以下结论:


忽略校验:


列的默认值差异


列是否可以为空的差异


表是否有主键的差异


表中唯一索引和普通索引的差异


可校验:


索引(唯一索引 + 普通索引)数量的差异


单列索引覆盖列的差异


联合索引和单列索引的差异(即使联合索引和单列索引有相同的部分覆盖列)


发布于: 刚刚阅读数: 4
用户头像

TiDB 社区官网:https://tidb.net/ 2021-12-15 加入

TiDB 社区干货传送门是由 TiDB 社区中布道师组委会自发组织的 TiDB 社区优质内容对外宣布的栏目,旨在加深 TiDBer 之间的交流和学习。一起构建有爱、互助、共创共建的 TiDB 社区 https://tidb.net/

评论

发布
暂无评论
sync-diff-inspector比对表结构,索引_TiDB 社区干货传送门_InfoQ写作社区