从 MySQL 转储中删除 DEFINER 子句

Remove DEFINER clause from MySQL Dumps

提问by FastTrack

I have a MySQL dump of one of my databases. In it, there are DEFINER clauses which look like,

我有我的一个数据库的 MySQL 转储。其中有一些 DEFINER 子句，它们看起来像，

"DEFINER=`root`@`localhost`" 

Namely, these DEFINER clauses are on my CREATE VIEW and CREATE PROCEDURE statements. Is there a way to remove these DEFINER clauses from my dump file?

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

采纳答案by Abhay

I don't think there is a way to ignore adding DEFINERs to the dump. But there are ways to remove them after the dump file is created.

我认为没有办法忽略将DEFINERs添加到转储。但是有一些方法可以在创建转储文件后删除它们。

1. Open the dump file in a text editor and replace all occurrences of DEFINER=root@localhostwith an empty string ""

2. Edit the dump (or pipe the output) using perl:

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

3. Pipe the output through sed:

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

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

2. 使用以下命令编辑转储（或管道输出）perl：

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

3. 通过管道输出sed：

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

回答by Ricardo Martins

You can remove using SED

您可以使用 SED 删除

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

In MacOS:

在 MacOS 中：

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

回答by maxhb

Since mysql version 5.7.8 you can use the --skip-defineroption with mysqlpump, e.g.:

从 mysql 版本 5.7.8 开始，您可以在--skip-definermysqlpump 中使用该选项，例如：

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

See updated mysql manual at http://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_skip-definer

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

回答by SitesBySequoia.com

I used these ideas to strip the DEFINER clause from my own mysqldump output, but I took a simpler approach:

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

Just remove the !before the code and DEFINER, and the rest of the comment becomes a regular comment.

只要去掉!代码和DEFINER之前的，剩下的注释就变成了普通注释。

Example:

例子：

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

is rendered helpless, as little as doing this ..

变得无助，就像这样做一样..

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

The easiest regexp, though, is to remove the ! and the numbers

不过，最简单的正则表达式是删除 ! 和数字

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

That removes !#### DEFINERand replaces with DEFINER ... you could remove DEFINER too, it doesn't really matter - once the "!" is gone

删除!#### DEFINER并替换为 DEFINER ......你也可以删除 DEFINER，这并不重要 - 一旦“！” 离开了

回答by Jonathan

As per the other's recommendations, here is an example of how to remove the DEFINER from the dump, after the dump has finished:

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

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

回答by Lajos Veres

As others mentioned stripping the definer with any kind of regular expression is not too complex. But the other examples stripped the view definitions for me.

正如其他人提到的那样，用任何类型的正则表达式剥离定义器并不太复杂。但是其他示例为我剥离了视图定义。

This is the regular expression which worked for me:

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

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

回答by josephdpurcell

For an automated solution, you could look at mysqlmigrate. It's a Bash wrapper around mysqldumpwhich allows you to migrate databases and ignore the DEFINER statements. Example:

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

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

http://thesimplesynthesis.com/post/mysqlmigrate(or GitHub)

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

Otherwise, yes, a command like Abhay points out is needed:

否则，是的，需要像 Abhay 指出的命令：

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

回答by mohrt

Another option on OSX to remove definers in-file:

OSX 上删除文件中定义器的另一个选项：

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

回答by Jose Nobile

You should look the answer here: https://dba.stackexchange.com/questions/9249/how-do-i-change-the-definer-of-a-view-in-mysql/29079#29079

您应该在这里查看答案：https: //dba.stackexchange.com/questions/9249/how-do-i-change-the-definer-of-a-view-in-mysql/29079#29079

The best way in my opinion to address this problem, is not adding an extra parse of string with sed or grep or any other, instead mysqldump is able to generate a good dump by adding --single-transaction

我认为解决此问题的最佳方法不是使用 sed 或 grep 或任何其他方法添加额外的字符串解析，而是 mysqldump 能够通过添加 --single-transaction 生成良好的转储

回答by Paul Dixon

A quick way to do this is to pipe the output of mysqldumpthrough sedto remove the DEFINERstatements wrapped in conditional comments. I use this to remove DEFINERfrom CREATE TRIGGERstatements, but you can tweak the condition comment version number in the regex to suit your purposes.

一个快速的方法是通过sed将mysqldump的输出通过管道传输，以删除包含在条件注释中的语句。我使用它来从语句中删除，但您可以调整正则表达式中的条件注释版本号以满足您的目的。DEFINERDEFINERCREATE TRIGGER

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