从MySQL转储中删除DEFINER子句

Question

我有一个MySQL数据库的备份文件。其中包含看起来像这样的DEFINER子句，

"DEFINER=`root`@`localhost`" 

我的CREATE VIEW和CREATE PROCEDURE语句中有这些DEFINER子句。有没有办法从我的转储文件中删除这些DEFINER子句？

- FastTrack

---

一个错误在多年前被报告，但看起来已经被放弃了： https://bugs.mysql.com/bug.php?id=24680 - 1234ru

Answer 1

我认为没有办法忽略在转储过程中添加的DEFINER，但是可以在创建转储文件后删除它们的方法。

1. 在文本编辑器中打开转储文件，并将所有出现的DEFINER=root@localhost替换为空字符串""

2. 使用perl编辑转储文件（或管道输出）：

perl -p -i.bak -e "s/DEFINER=\`\w.*\`@\`\d[0-3].*[0-3]\`//g" mydatabase.sql

通过 sed 管道输出：

mysqldump ... | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > triggers_backup.sql

- Abhay

---

如果转储文件达到10G怎么办？ - bksi

建议提到，可以将 DEFINER 设置为 CURRENT_USER，而不是删除它，方法如下： sed -E 's/DEFINER=[^]+@[^]+/DEFINER=CURRENT_USER/g' dump.sql > new_dump.sql 这样有保留限制/访问控制的优点。 - 4thfloorstudios

sed 命令无法从存储过程和函数中删除 DEFINER 子句。这里是增强版，可以处理视图、触发器、存储过程和函数：sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | sed -e 's/DEFINER[ ]*=[ ]*[^*]*PROCEDURE/PROCEDURE/' | sed -e 's/DEFINER[ ]*=[ ]*[^*]*FUNCTION/FUNCTION/' - Vladimir Strugatsky

FYI --- 尽管在回答此问题时情况并非如此，但 MySQL > 5.6 现在随附一个支持 "--skip-definer" 选项的 mysqldump(1)：https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_skip-definer - Kevin_Kinsey

不，这不是mysqldump，而是mysqlpump带有--skip-definer选项。 - Xdg

Answer 2

你可以使用SED来删除

sed -i 's/DEFINER=[^*]*\*/\*/g' mydump.sql

在MacOS中：

sed -i '' 's/DEFINER=[^*]*\*/\*/g' mydump.sql

- Ricardo Martins

---

在 MacOS 中发生了什么不同的事情？ - Alex

Mac上的sed手册指出：-i extension “就地编辑文件，并使用指定的扩展名保存备份。如果给定了长度为零的扩展名，则不会保存备份。当进行原位编辑文件时，不建议给出长度为零的扩展名，因为在磁盘空间耗尽等情况下，您会面临损坏或部分内容的风险。” - Chad

很高兴知道这个，@Chad。我在Mac上使用sed已经5年了。我从来没有遇到过空格或损坏文件的问题。但当然，这是需要考虑的事情。谢谢你的澄清。 - Ricardo Martins

我在 Mac 上也从未遇到过这些问题。只是想澄清一下，因为 @Alex 问了 :) - Chad

它对我来说运行良好，非常感谢。 - TN98

Answer 3

自从mysql版本5.7.8以后，你可以在mysqlpump（而不是mysqldump）中使用--skip-definer选项，例如：

mysqlpump --skip-definer -h localhost -u user -p yourdatabase

请查看更新后的MySQL手册，链接在http://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_skip-definer

- maxhb

---

想知道 mysqldump 和 mysqlpump 有什么区别的人可以参考这个相关问题：https://dba.stackexchange.com/questions/118846/mysqldump-vs-mysqlpump - Scadge

我已经安装了phpMyAdmin和MySQL Workbench。在Windows上如何执行此命令？ - posfan12

它使用 information_schema.user，并非所有用户都可以读取。 - bato3

Answer 4

我使用这些想法从自己的mysqldump输出中删除DEFINER子句，但我采取了更简单的方法：

只需在代码和DEFINER之前删除！，其余注释将变为常规注释。

示例：

/*!50017 DEFINER=`user`@`111.22.33.44`*/

被渲染成无助的，只是做这个..

/* 50017 DEFINER=`user`@`111.22.33.44`*/

最简单的正则表达式是去掉感叹号和数字

mysqldump | /usr/bin/perl -pe 's/\!\d+ DEFINER/DEFINER/' > dumpfile.sql

这将去除!#### DEFINER并替换为DEFINER...你也可以删除DEFINER，这并不重要——一旦"!"消失了

- SitesBySequoia.com

---

牛逼而优雅的解决方案！ - Wouter

Answer 5

根据其他人的建议，以下是一个示例，说明在转储完成后如何从转储中删除DEFINER：

mysqldump -u user --password='password' -h localhost database | grep -v "50013 DEFINER" > dump.sql

- Jonathan

---

对于触发器，这些代码看起来像
/!50003 CREATE/ /!50017 DEFINER=user@localhost/ /*!50003 trigger my_triggername BEFORE INSERT ON my_table FOR EACH ROW .... */;; 使用 grep -v 命令将完全删除这些代码行。（尽管 50017 不等于 50013） - Kenney

Answer 6

正如其他人所提到的，使用任何一种正则表达式都不太难去除定义者。但是其他示例为我剥离了视图定义。

这是对我有效的正则表达式：

sed -e 's/DEFINER=[^ ]* / /'

- Lajos Veres

---

不支持触发器 - 也会删除 */。MySQL 5.6.31。 - Franc Drobnič

Answer 7

对于自动化解决方案，您可以查看 mysqlmigrate。它是一个 Bash 包装器，可围绕 mysqldump 进行操作，允许您迁移数据库并忽略 DEFINER 语句。例如：

$ mysqlmigrate -u root -p pass --ignore-definer from_db to_db

http://thesimplesynthesis.com/post/mysqlmigrate（或GitHub）

否则，是的，像Abhay指出的那样需要一个命令：

$ mysqldump -u root -ppass the_db | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > the_db.sql

- josephdpurcell

Answer 8

你应该在这里查看答案：https://dba.stackexchange.com/questions/9249/how-do-i-change-the-definer-of-a-view-in-mysql/29079#29079 在我看来，解决这个问题的最佳方法不是使用sed、grep或其他工具添加额外的字符串解析，而是使用mysqldump通过添加--single-transaction生成一个好的转储文件。

- Jose Nobile

---

为什么被踩了？为什么这个答案不被认为是有用的？ - Jose Nobile

您的回复还需要大约90个赞才能得到它应有的位置。我已经为您点赞了 :) - Patrick van Bergen

我刚刚花了过去4个小时尝试转储数据库。没想到问题如此简单！谢谢。 - A. Fletcher

Answer 9

在OSX上删除文件中的definers的另一个选项是：

sed -i '' 's/DEFINER=`[^`][^`]*`@`[^`][^`]*`//g' file.sql

- mohrt

Answer 10

一种快速的方法是通过将mysqldump 的输出导入到 sed中，以删除包含在条件注释中的 DEFINER语句。我使用这种方法来删除 CREATE TRIGGER语句中的DEFINER，但你可以根据自己的需要调整正则表达式中的条件注释版本号。

mysqldump -u user --password='password' -h localhost database | \
  sed 's/\/\*!50017 DEFINER=`.*`@`.*`\*\///' 

- Paul Dixon

---

一个稍微更易读的sed语句：sed "s/DEFINER(.*?)FUNCTION/FUNCTION/" - reustmd