mysql改视图的DEFINER_改写 mysqldump 解决 DEFINER 问题

作者:xuty

本文来源:原创投稿 *爱可生开源社区出品,原创内容未经授权不得随意使用,转载请联系小编并注明来源。

一、背景

项目上 MySQL 还原 SQL 备份经常会碰到一个错误如下,且通常出现在导入视图、函数、存储过程、事件等对象时,其根本原因就是因为导入时所用账号并不具有SUPER 权限,所以无法创建其他账号的所属对象。

ERROR 1227 (42000) : Access denied; you need (at least one of) the SUPER privilege(s) for this operation

常见场景:

1. 还原 RDS 时经常出现,因为 RDS 不提供 SUPER 权限;

2. 由开发库还原到项目现场,账号权限等有所不同。

处理方式:

1. 在原库中批量修改对象所有者为导入账号或修改 SQL SECURITY 为 Invoker;

2. 使用 mysqldump 导出备份,然后将 SQL 文件中的对象所有者替换为导入账号。

二、问题原因

我们先来看下为啥会出现这个报错,那就得说下 MySQL 中一个很特别的权限控制机制,像视图、函数、存储过程、触发器等这些数据对象会存在一个 DEFINER 和一个 SQL SECURITY 的属性,如下所示:

--视图定义CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`%` SQL SECURITY DEFINER VIEW v_test--函数定义CREATE DEFINER=`root`@`%` FUNCTION `f_test()` RETURNS varchar(100) SQL SECURITY DEFINER--存储过程定义CREATE DEFINER=`root`@`%` PROCEDURE `p_test`() SQL SECURITY DEFINER--触发器定义CREATE DEFINER=`root`@`%` trigger t_test--事件定义CREATE DEFINER=`root`@`%` EVENT `e_test`

DEFINER:对象定义者,在创建对象时可以手动指定用户,不指定的话默认为当前连接用户;SQL SECURITY:指明以谁的权限来执行该对象,有两个选项,一个为 DEFINER,一个为INVOKER,默认情况下系统指定为 DEFINER;DEFINER:表示按定义者的权限来执行; INVOKER:表示按调用者的权限来执行。

如果导入账号具有 SUPER 权限,即使对象的所有者账号不存在,也可以导入成功,但是在查询对象时,如果对象的 SQL SECURITY 为 DEFINER,则会报账号不存在的报错。

ERROR 1449 (HY000): The user specified as a definer ('root'@'%') does not exist

三、改写内容

上述这个 DEFINER 问题,个人想到最简单的解决方式就是 mysqldump 导出时直接摘除掉相关属性,但是 mysqldump 本身并不提供对应参数,所以比较蛋疼,无论是原库走脚本变更或是备份后修改 SQL 文件都不是非常方便,尤其是触发器的 DEFINER,只能先 DROP 再 CREATE 才可以变更。只能看下是否可以从 mysqldump 源码中去掉 DEFINER 定义。

本次 mysqldump 改写主要有 2 个目的:

1. 摘取备份中视图、函数、存储过程、触发器等对象的 DEFINER 定义;

2. 尝试加上比较简单的备份进度显示(原生 mysqldump 的 verbose 参数不是非常清晰,想要实现 navicate 备份时的那种行数显示)。

a3318c941136e31422f9e0be7c9e8802.gif

改写 mysqldump 解决 DEFINER 问题-1.jpg (55.93 KB, 下载次数: 0)

2020-11-17 04:45 上传

a3318c941136e31422f9e0be7c9e8802.gif

改写 mysqldump 解决 DEFINER 问题-2.jpg (72.76 KB, 下载次数: 0)

2020-11-17 04:45 上传

改写好处:

1. 可以避免还原时遇到 DEFINER 报错相关问题;

2. 根据输出信息知道备份是否正常进行,防止备份中遇到元数据锁无法获取然后一直卡住的情况。

四、版本选择

改之前需要先选个 MySQL 版本,对比了下几个 MySQL 5.7.27 以上的版本,发现其 mysqldump.c 的源码是一样的,而从 MySQL 8.0 之后则完全变了。

因此选择 MySQL 5.7.27 版本的 mysqldump 源文件来改写,所以本次改写只适用于MySQL 5.7 版本 。

a3318c941136e31422f9e0be7c9e8802.gif

改写 mysqldump 解决 DEFINER 问题-3.jpg (66.4 KB, 下载次数: 0)

2020-11-17 04:45 上传

五、备份顺序

如下是 mysqldump 备份对象时的顺序,值得注意的是 mysqldump 在备份表的时候会连带视图也一起备份,但是只是临时视图(常量别名替换实际列),主要是为了防止后续其他视图、函数与存储过程中用到该视图,所以通过临时视图来解决依赖问题,在最后才真正备份视图,这招非常精妙!

a3318c941136e31422f9e0be7c9e8802.gif

改写 mysqldump 解决 DEFINER 问题-4.jpg (49.35 KB, 下载次数: 0)

2020-11-17 04:45 上传

a3318c941136e31422f9e0be7c9e8802.gif

改写 mysqldump 解决 DEFINER 问题-5.jpg (41.48 KB, 下载次数: 0)

2020-11-17 04:45 上传

六、源码改动

6.1 打印函数

因为要在会话窗口下模拟 verbose 一样输出备份信息,所以就照搬了原生的 verbose_msg() 函数,新建了个 print_dump_msg() 函数用于备份信息输出。

a3318c941136e31422f9e0be7c9e8802.gif

改写 mysqldump 解决 DEFINER 问题-6.jpg (33.45 KB, 下载次数: 0)

2020-11-17 04:45 上传

6.2 行数显示

dump_table 函数中增加备份行数输出,本身源码就是一行一行循环读取的,所以非常方便打印输出。

a3318c941136e31422f9e0be7c9e8802.gif

改写 mysqldump 解决 DEFINER 问题-7.jpg (37.3 KB, 下载次数: 0)

2020-11-17 04:45 上传

6.3 DEFINER 摘除

trigger 与 event 的 DEFINER 是在 dump_trigger |dump_events_for_db->cover_definer_clause 函数中通过 my_case_str 方法摘走的。

a3318c941136e31422f9e0be7c9e8802.gif

改写 mysqldump 解决 DEFINER 问题-8.jpg (36.44 KB, 下载次数: 0)

2020-11-17 04:45 上传

routines 的 DEFINER 是在 dump_routines_for_db 函数中通过 fprint 方法重新拼接定义摘走的。

a3318c941136e31422f9e0be7c9e8802.gif

改写 mysqldump 解决 DEFINER 问题-9.jpg (48.57 KB, 下载次数: 0)

2020-11-17 04:45 上传

views 的 DEFINER 是在 get_view_structure 函数中通过 my_case_str 方法摘走的。

a3318c941136e31422f9e0be7c9e8802.gif

改写 mysqldump 解决 DEFINER 问题-10.jpg (45.17 KB, 下载次数: 0)

2020-11-17 04:45 上传

七、改写效果

1. 备份过程中打印具体信息

a3318c941136e31422f9e0be7c9e8802.gif

改写 mysqldump 解决 DEFINER 问题-11.jpg (43.81 KB, 下载次数: 0)

2020-11-17 04:45 上传

2. SQL 备份中摘除了 DEFINER 属性

a3318c941136e31422f9e0be7c9e8802.gif

改写 mysqldump 解决 DEFINER 问题-12.jpg (73.75 KB, 下载次数: 0)

2020-11-17 04:45 上传

八、性能测试

通过 sysbench 造测试数据后,分别使用改写后的 mysqldump 与原生的 mysqldump 进行多次远程备份,查看平均耗时。

这里选择远程备份测试是因为很多实际使用场景也是远程备份,而且远程备份更能体现出频繁打印信息对备份性能的影响。

实际压测发现,如果每一行都打印一次,会严重影响性能,所以改成 1W 行打印一次,影响会比较小。

另外踩了个坑,一开始测试的时候是使用开启 debug 编译的 mysqldump,所以会执行很多多余的 debug 代码,备份速度非常慢,关闭 debug 重新编译后,速度就比较正常了。

a3318c941136e31422f9e0be7c9e8802.gif

改写 mysqldump 解决 DEFINER 问题-13.jpg (14.52 KB, 下载次数: 0)

2020-11-17 04:45 上传

通过测试,如果是 1W 行打印一次,对于备份的性能损耗是比较低的,在可接受范围之内。

九、小结

本次通过直接修改 mysqldump 源码的方式,比较好的解决了 DEFINER 问题,另外将改写后的 mysqldump 源码拿到 Windows 环境下编译后是可以直接使用的,亲测有效,不过 Windows 编译环境搭建繁琐多了。

其实也可以通过类似的方式来实现异构数据库的迁移,比如从 MySQL 迁移到其他数据库时,可以通过修改 mysqldump 源码来完成字段类型映射与语法转换,也是一种思路。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值