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 = 2
export-fix-sql = true
check-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`` ... equivalent
Comparing the table data of ``t`.`t`` ... equivalent
_____________________________________________________________________________
Progress [============================================================>] 100% 0/0
A 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`` ... equivalent
Comparing the table data of ``t`.`t`` ... equivalent
_____________________________________________________________________________
Progress [============================================================>] 100% 0/0
A 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`` ... equivalent
Comparing the table data of ``t`.`t`` ... equivalent
_____________________________________________________________________________
Progress [============================================================>] 100% 0/0
A 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`` ... equivalent
Comparing the table data of ``t`.`t`` ... equivalent
_____________________________________________________________________________
Progress [============================================================>] 100% 0/0
A 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`` ... failure
Comparing the table data of ``t`.`t`` ... equivalent
_____________________________________________________________________________
Progress [============================================================>] 100% 0/0
The 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`` ... failure
Comparing the table data of ``t`.`t`` ... equivalent
_____________________________________________________________________________
Progress [============================================================>] 100% 0/0
The 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`` ... failure
Comparing the table data of ``t`.`t`` ... equivalent
_____________________________________________________________________________
Progress [============================================================>] 100% 0/0
The 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 校验表结构,有以下结论:
忽略校验:
列的默认值差异
列是否可以为空的差异
表是否有主键的差异
表中唯一索引和普通索引的差异
可校验:
索引(唯一索引 + 普通索引)数量的差异
单列索引覆盖列的差异
联合索引和单列索引的差异(即使联合索引和单列索引有相同的部分覆盖列)
版权声明: 本文为 InfoQ 作者【TiDB 社区干货传送门】的原创文章。
原文链接:【http://xie.infoq.cn/article/b1854c111350a2a869d411bd6】。文章转载请联系作者。
TiDB 社区干货传送门
TiDB 社区官网:https://tidb.net/ 2021-12-15 加入
TiDB 社区干货传送门是由 TiDB 社区中布道师组委会自发组织的 TiDB 社区优质内容对外宣布的栏目,旨在加深 TiDBer 之间的交流和学习。一起构建有爱、互助、共创共建的 TiDB 社区 https://tidb.net/
评论