MySQL不同环境的库表结构的比对并给出修改的SQL
原创MySQL不同环境的库表结构的比对并给出修改的SQL
原创
保持热爱奔赴山海
修改于 2023-09-04 14:41:26
修改于 2023-09-04 14:41:26
之前用python写了个脚本，用于比对test和prod的表结构差异（防止出现上prod的时候，发生表或者索引遗漏的情况）。
但是还不够友好，只能找出差异但是不能自动生成fix的SQL。
这里再介绍一个小工具 skeema，它的免费版的功能已经足够强大，可以自动找出差异，并给出fix的语句。（当然这个工具在某些场景下也有致命的缺点，具体见本文的最后部分）
看下面的例子：
https://www.skeema.io/docs/
curl -LO https://github.com/skeema/skeema/releases/latest/download/skeema_amd64.rpm
yum localinstall skeema_amd64.rpm 或者直接下载二进制bin文件
我们这里是比对test环境和uat环境的表结构差异
需要的权限：
test环境：
create user user1@'%' identified by 'pass1';
grant select on *.* to user1@'%';
grant ALL PRIVILEGES on `_skeema_tmp`.* to user1@'%'; -- 比对操作需要在这个库进行临时读写操作。给了ALL后，账号就可以只创建和CRUD操作_skeema_tmp库，不会影响到其他业务库
uat环境：
create user user2@'%' identified by 'pass2';
GRANT SELECT ON *.* TO `user2`@`%`;
GRANT ALL PRIVILEGES ON `_skeema_tmp`.* TO `user2`@`%`; -- 比对操作需要在这个库进行临时读写操作。给了ALL后，账号就可以只创建和CRUD操作_skeema_tmp库，不会影响到其他业务库
# 在TEST环境执行， 获取uat数据并写到 sbtest这个文件夹下
[root@centos7-3 ~]# cd lirulei/
[root@centos7-3 lirulei]# skeema init -h 192.168.1.182 -P 3306 -u user1 -p'pass1' -d sbtest --schema sbtest
[root@centos7-3 skeema]# ll
total 12
drwxr-xr-x 2 root root 12288 Aug 31 15:52 sbtest
# 进到sbtest目录下，编辑下当前目录的隐藏文件
[root@centos7-3 skeema]# cd sbtest
[root@centos7-3 sbtest]# cat .skeema
default-character-set=utf8
default-collation=utf8_general_ci
generator=skeema:1.10.1-community
schema=sbtest
[test] -- 原先这里是production，改为了test
flavor=mysql:8.0
host=192.168.1.182
port=3306
user=user1
在sbtest目录下执行
[root@centos7-3 sbtest]# skeema add-environment uat -h 192.168.1.181 -P 3306 -u user2 -p'pass2'
2023-08-31 15:54:13 [INFO] Added environment [uat] to /root/user2/skeema/sbtest/.skeema
[root@centos7-3 sbtest]# cat .skeema
default-character-set=utf8
default-collation=utf8_general_ci
generator=skeema:1.10.1-community
schema=sbtest
[test]
flavor=mysql:8.0
host=192.168.1.182
port=3306
user=user1
[uat]
flavor=mysql:8.0
host=192.168.1.181
port=3306
user=user2
在sbtest目录下，拉取下test环境的表结构（防止在init后，又出现了改动）
[root@centos7-3 sbtest]# skeema pull test -p'pass1' # 这里输入test的密码
2023-08-31 16:11:54 [INFO] Updating /root/user2/skeema/sbtest to reflect 192.168.1.182:3306 sbtest
2023-08-31 16:02:29 [INFO] Updating /root/user2/skeema/sbtest to reflect 192.168.1.182:3306
生成差异sql文件
[root@centos7-3 sbtest]# skeema diff uat --allow-unsafe -p'pass2' > /tmp/uat_to_fixed.sql # 这里输入的是uat的密码
2023-08-31 16:02:54 [WARN] /root/user2/skeema/sbtest/tb1.sql:28: Table
tb1 is using default character set utf8, which is not
configured to be permitted. The following character sets are listed in option
allow-charset: latin1, utf8mb4.
To permit storage of all valid four-byte UTF-8 characters, use the utf8mb4
character set instead of the legacy three-byte utf8 character set.
2023-08-31 16:02:54 [WARN] /root/user2/skeema/sbtest/tb2.sql:21: Table
tb2 is using default character set utf8, which is not configured
to be permitted. The following character sets are listed in option allow-charset:
latin1, utf8mb4.
To permit storage of all valid four-byte UTF-8 characters, use the utf8mb4
character set instead of the legacy three-byte utf8 character set.
2023-08-31 16:02:54 [INFO] 192.168.1.181:3306 sbtest: diff complete
生成的差异文件类似如下：
-- instance: 192.168.1.181:3306
USE `sbtest`;
ALTER TABLE `t1` ADD COLUMN `address` varchar(255) NOT NULL DEFAULT '' COMMENT 'xxxx';
DROP TABLE `t2`;
ALTER TABLE `t3` DROP COLUMN `col1`, MODIFY COLUMN `col2` char(2) NOT NULL DEFAULT '' COMMENT '类型', MODIFY COLUMN `col3` varchar(64) NOT NULL DEFAULT '' COMMENT 'xx', ADD COLUMN `col4` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT 'xxxxx' AFTER `name`;
拿到DDL语句后，就可以提工单将这些变更同步到UAT环境了。
TIPS：
1、 我们也可以把密码写到配置文件里，这样命令行就不用输入了，例子如下：
[root@centos7-3 sbtest]# cat .skeema
default-character-set=utf8
default-collation=utf8_general_ci
generator=skeema:1.10.1-community
schema=sbtest
[test]
flavor=mysql:8.0
host=192.168.1.182
port=3306
user=user1
password='pass1'
[uat]
flavor=mysql:8.0
host=192.168.1.181
port=3306
user=user2
password='pass2'
2、官方文档上提到还支持push操作，直接把变更的命令发到目标mysql去执行，这个操作实际上还是有点危险的，不建议使用。
感兴趣的，可通过binlog分析下 skeema的过程：
执行 init后，会从src上把相关的建表语句拉到本地文件夹下（执行show create table xxx）
执行 pull后，会把src上的差异的建表语句拉到本地文件夹下（执行show create table xxx）
执行 diff后，会把src上的建表语句下发到dest的 _skeema_tmp 这个临时创建的库里，然后进行比对，并生成修改的SQL语句【具体的比对逻辑详见代码仓库 https://github.com/skeema/skeema】目前不支持的地方[非常重要]：
https://www.skeema.io/docs/requirements/
1 不支持 ALTER TABLE
Skeema 可以使用这些功能创建或删除表，但无法生成操作它们的 ALTER。skeema diff和的输出skeema push将注意到它无法为涉及这些功能的表生成或运行 ALTER TABLE，因此将跳过受影响的表，但其余操作将照常进行。
空间索引
子分区（同一个表中的两级分区）
常规表空间（除innodb_systemor之外的显式 TABLESPACE 子句innodb_file_per_table）
MariaDB 的应用程序时间段功能（PERIOD FOR子句）
非InnoDB存储引擎的一些特性
2 重命名列或表
Skeema 目前无法用于重命名表中的列，或重命名整个表。这是 Skeema 声明式方法的一个缺点：通过将所有内容表示为 a CREATE TABLE，Skeema 无法（绝对确定）知道列重命名与删除现有列和添加新列之间的区别。重命名表也存在类似的问题。
未来版本中可能会添加解决方案。优先级将取决于用户需求。无论如何，许多公司都不允许在生产中进行重命名，因为它们会带来相当大的部署顺序复杂性：不可能在数据库中的列或表重命名的同时部署应用程序代码更改。
目前，Skeema 会将重命名尝试解释为 DROP-then-ADD 操作。但由于 Skeema 自动将任何破坏性操作标记为不安全，因此将阻止执行这些操作，除非使用allow-unsafe 选项，或者表低于safe-below-size 选项中指定的大小限制。
如下例子1 （对于列的rename，会被转成drop column+add column）：
test环境执行的是： alter table t3333 change name stu_name char(120);
Skeema自动转换出来的SQL：
ALTER TABLE `t3333` DROP COLUMN `name`, ADD COLUMN `stu_name` char(120) DEFAULT NULL;
可以看到这样在生产环境是没法直接使用的，如果这样搞的话，原始name列的数据就全丢失了。
如下例子2（对于表的rename，会被转成drop +create）：
test环境执行的是： rename table t3333 to t4444;
Skeema自动转换出来的SQL：
DROP TABLE `t3333`;
CREATE TABLE `t4444` (
`id` int(11) NOT NULL,
`stu_name` char(120) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
3 不支持视图的捕获（比对会自动忽略掉视图）
4 对外键的检测基本支持有限（生产上外键一般用的很少）
5 对分区表的支持也有限，看下面的例子
源端执行：
CREATE TABLE sales (
money INT UNSIGNED NOT NULL,
`date` DATETIME
) ENGINE=INNODB
PARTITION BY RANGE (YEAR(DATE)) (
PARTITION p2008 VALUES LESS THAN (2009),
PARTITION p2009 VALUES LESS THAN (2010),
PARTITION p2010 VALUES LESS THAN (2011)
);
alter table sales drop partition p2008;
alter table sales ADD PARTITION (PARTITION p2011 VALUES LESS THAN (2012));
Skeema自动转换出来的SQL：
CREATE TABLE `sales` (
`money` int(10) unsigned NOT NULL,
`date` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (year(`date`))
(PARTITION p2008 VALUES LESS THAN (2009) ENGINE = InnoDB,
PARTITION p2009 VALUES LESS THAN (2010) ENGINE = InnoDB,
PARTITION p2010 VALUES LESS THAN (2011) ENGINE = InnoDB) */;
可以看到 虽然能捕获到 创建分区表的操作，但是后面的 drop partition的操作没有被识别捕获到。
6 社区版对触发器的支持有限（基本上生产也很少用触发器，问题不大）
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL（TencentDB for MySQL）为用户提供安全可靠，性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性，包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL，可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理，不仅经济实惠，而且稳定可靠，易于运维。