专栏首页mysql-dbamysql迁移存储过程函数视图存在的问题
原创

mysql迁移存储过程函数视图存在的问题

一般情况下,我们使用逻辑导出导入迁移存储过程、函数、视图存在如下问题:

如果环境中的用户信息没有迁移过去,在创建和调用中会存在问题

创建时的用户没有相应的权限:
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SET_USER_ID privilege(s) for this operation
创建时用户不存在调用时报错:
ERROR 1449 (HY000): The user specified as a definer ('opensips'@'192.168.0.%') does not exist

在定义中有

DEFINER=`opensips`@`192.168.0.%` 定义者

SQL SECURITY DEFINER 访问安全有两种

SQL SECURITY DEFINER : 调用阶段根据调用者的权限进行判断是否可以调用,执行阶段根据DEFINER的账户权限判断操作是否可以继续执行。

SQL SECURITY INVOKER:调用阶段和执行阶段都根据调用者的权限进行判断操作是否可以继续

以上错误是我们在用myqldump导出时:

/usr/local/mysql/bin/mysqldump -S /tmp/mysql3312.sock -uroot -p'123456!' --hex-blob -R -E   
--set-gtid-purged=OFF --databases opensips > opensips_20210802.sql; 

在导出中视图过程会有:

定义的DEFINER=
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`opensips`@`192.168.0.%` SQL SECURITY DEFINER */
/*!50001 VIEW `wj_v_registrant` AS select `b`.`registrar` AS `registrar`

我们解决这个问题有如下方法

1.在导出是把过程,视图,函数中的DEFINER=替换掉可以sed命令

/usr/local/mysql/bin/mysqldump -S /tmp/mysql3312.sock -uroot -p'123456' --databases opensips \
 --single-transaction --hex-blob --no-data --routines --events --set-gtid-purged=OFF \
|sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' -e 's/DEFINER[ ]*=.*FUNCTION/FUNCTION/' -e 's/DEFINER[ ]*=.*PROCEDURE/PROCEDURE/' -e 's/DEFINER[ ]*=.*TRIGGER/TRIGGER/' -e 's/DEFINER[ ]*=.*EVENT/EVENT/' > opensips-test.sql

2.可以通过mysqlshell 的util.dumpSchemas导出

util.dumpSchemas(['definers'], '/tmp/dump', {compatibility:["strip_definers"]})

要加上{compatibility:["strip_definers"]}

可以看到导入的视图中已经把define去掉了

--
-- Temporary view structure for view `wj_v_domain_route`
--

DROP TABLE IF EXISTS wj_v_domain_route;
/*!50001 DROP VIEW IF EXISTS wj_v_domain_route*/;
SET @saved_cs_client     = @@character_set_client;
/*!50503 SET character_set_client = utf8mb4 */;
/*!50001 CREATE VIEW `wj_v_domain_route` AS SELECT 
 1 AS id,
 1 AS caller_id_dpid,
 1 AS callee_id_dpid,
 1 AS trunk_group,
 1 AS domain */;
SET character_set_client = @saved_cs_client;
~                                       

导入:
MySQL  10.1.1.201:3321 ssl  JS > util.loadDump('/tmp/opendump')
导入后查询:
mysql> select table_name,definer,SECURITY_TYPE from information_schema.views where TABLE_SCHEMA='opensips';
+-------------------+---------+---------------+
| TABLE_NAME        | DEFINER | SECURITY_TYPE |
+-------------------+---------+---------------+
| wj_v_domain_route | root@%  | INVOKER       |
| wj_v_exten_route  | root@%  | INVOKER       |
| wj_v_location     | root@%  | INVOKER       |
| wj_v_oss_line     | root@%  | INVOKER       |
| wj_v_registrant   | root@%  | INVOKER       |
+-------------------+---------+---------------+
5 rows in set (0.00 sec)              

3.导入后再进行重建,或者修改define (不推荐)

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • mysql存储过程和存储函数的使用

    create procedure name(IN | OUT |INOUT str STRING) #定义存储过程名字

    93年的老男孩
  • MySQL存储过程的权限问题

    MySQL的存储过程,没错,看起来好生僻的使用场景。问题源于一个开发同学提交了权限申请的工单,需要开通一些权限。

    jeanron100
  • 「mysql优化专题」90%程序员没听过的存储过程和存储函数教学(7)

    储存过程是一个可编程的函数,它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数,或者封装特定功能时...

    java进阶架构师
  • MySQL 之 视图、触发器、存储过程、函数、事物与数据库锁

    浏览目录: 1.视图 2.触发器 3.存储过程 4.函数 5.事物 6.数据库锁 7.数据库备份 1.视图 视图:是一个虚拟表,其内容由查询定义。同真实的表...

    人生不如戏
  • MySQL视图,存储过程和触发器的使用

    在视图创建后,可以用表的基本操作来使用视图,进行SELECT,WHERE,ORDER BY,联结等操作。视图仅仅是用来查看存储在别处的数据的一种工具而不是一个表...

    小末快跑
  • 调用PostgreSQL存储过程,找不到函数名的问题

    PostgreSQL的表,函数名称都是严格区分大小写的,所以在使用的时候没有注意大小写问题容易导致找不到函数名的错误,但最近两天我们发现,如果函数参数使用了自定...

    用户1177503
  • MySQL数据迁移那些事儿

    在平时工作中,经常会遇到数据迁移的需求,比如要迁移某个表、某个库或某个实例。根据不同的需求可能要采取不同的迁移方案,数据迁移过程中也可能会遇到各种大小问题。本篇...

    MySQL技术
  • SQL学习笔记七之MySQL视图、触发器、事务、存储过程、函数

    视图是一个虚拟表(非真实存在),其本质是【根据SQL语句获取动态的数据集,并为其命名】,用户使用时只需使用【名称】即可获取结果集,可以将该结果集当做表来使用。

    Jetpropelledsnake21
  • MySQL存储过程where条件执行失败的问题

          前几天对服务器实体做了属性缓存机制,当时测试也没有出现大的问题,昨天有人跟我说,登陆的时候角色等级显示错误,我复测了一下,发现不只是等级错误,进入...

    帘卷西风
  • 创建视图与函数,你注意过 DEFINER 是啥意思吗

    在 MySQL 数据库中,在创建视图及函数的时候,你有注意过 definer 选项吗?在迁移视图或函数后是否有过报错情况,这些其实都可能和 definer 有关...

    MySQL技术
  • 技术分享 | 改写 mysqldump 解决 DEFINER 问题

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

    爱可生开源社区
  • MYSQL之视图、触发器、存储过程、函数、事物、数据库锁和数据库备份

    一、视图 -- view 视图:是一个虚报表,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据。 视图有如下特点:   1.视图的列可以来自...

    新人小试
  • 「3306π」沪江从 SQL Server 到 MySQL(二):在线迁移,空中换发动机

    在上篇文章 从 SQL Server 到 MySQL (一):异构数据库迁移 中,我们给大家介绍了从 SQL Server 到 MySQL 异构数据库迁移的基本...

    田帅萌
  • 我的数据访问函数库的源代码(四)—— 存储过程部分,包括存储过程的参数的封装

    /* 2008 4 25 更新 */ 我的数据访问函数库的源码。整个类有1400行,原先就是分开来写的,现在更新后还是分开来发一下吧。 第四部分:存储过程部...

    用户1174620
  • TiDB 在小米的应用实践

    MIUI 是小米公司旗下基于 Android 系统深度优化、定制、开发的第三方手机操作系统,也是小米的第一个产品。MIUI 在 Android 系统基础上,针对...

    PingCAP
  • 某资产管理平台Oracle改造Mysql方略

    改造总是要付出很多代价的,肯定会跌很多坑,这是必然的... 性能问题也总会呈现先下降后再上升的一个历程(调试、磨合、找到针对性、适应性解决方案)。

    杨漆
  • 存储总量达 20T 的 MySQL 实例,如何完成迁移?

    为保证业务迁移顺利进行,对迁移流程,工具进行了前期的调查研究,并对过程中发现的 4 大问题进行及时解决,本文为实际迁移经验分享。

    王亮
  • COS 数据工作流 + Serverless云函数自定义处理能力发布!

    在工业 4.0 的浪潮下,智能和数据与物理世界结合越加紧密,多元化、灵活、高效的数据处理能力成为各行各业的热点需求。虽然对象存储 COS 已经预置电商、文创、...

    腾讯云serverless团队
  • 数据库(七)

    视图是由一张表或多张表的查询结果构成的一张虚拟表,建立一张视图后会在数据库中保留一个以 frm 后缀结尾的文件,只保留了数据结果,所有的数据都来自 sql 语句...

    py3study

扫码关注云+社区

领取腾讯云代金券