blocks|key|640801|text|您可以使用SED删除|type|unstyled|depth|inlineStyleRanges|entityRanges|data|640802|sed+-i+'s/DEFINER=[%5E*]*\*/\*/g'+mydump.sql|code-block|syntax|javascript|640803|在MacOS中：|640804|sed+-i+''+'s/DEFINER=[%5E*]*\*/\*/g'+mydump.sql|640805|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

You can remove using SED

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

In MacOS:

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

blocks|key|426696|text|从mysql版本5.7.8开始，您可以将--skip-definer选项与mysqlpump一起使用，例如：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|426697|mysqlpump+--skip-definer+-h+localhost+-u+user+-p+yourdatabase|code-block|syntax|javascript|426698|请访问http://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_skip-definer查看更新的mysql手册。|426699|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_skip-definer^0|K|E|0|0|3|2B|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|X|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|Y|8|@]|D|@$9|Z|A|10|1|11]]|E|$]]|$1|M|3|-4|5|6|7|12|8|@]|D|@]|E|$]]]|N|$O|$5|P|Q|R|E|$S|T]]]]

Since mysql version 5.7.8 you can use the <code>--skip-definer</code> option with mysqlpump, e.g.:

<pre><code>mysqlpump --skip-definer -h localhost -u user -p yourdatabase
</code></pre>

See updated mysql manual at <a href="http://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_skip-definer" rel="noreferrer">http://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_skip-definer</a>

blocks|key|426278|text|我使用这些想法从我自己的mysqldump输出中去掉了DEFINER子句，但我采用了一种更简单的方法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|426279|只需删除代码和定义器之前的!，其余的注释就会变成常规注释。|offset|length|style|CODE|426280|示例：|426281|/*!50017+DEFINER=`user`@`111.22.33.44`*/|code-block|syntax|javascript|426282|会变得无能为力，就像这样做..|426283|/*+50017+DEFINER=`user`@`111.22.33.44`*/|426284|不过，最简单的regexp是删除！以及这些数字|426285|mysqldump+%7C+/usr/bin/perl+-pe+'s/\!\d%2B+DEFINER/DEFINER/'+>+dumpfile.sql|426286|删除!####+DEFINER并用DEFINER取而代之...你也可以删除定义器，这真的无关紧要-一旦"!“已经消失了|426287|entityMap^0|0|D|1|0|0|0|0|0|0|0|2|D|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|11|8|@$D|12|E|13|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|14|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|15|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|16|8|@]|9|@]|A|$]]|$1|Q|3|R|5|L|7|17|8|@]|9|@]|A|$M|N]]|$1|S|3|T|5|6|7|18|8|@]|9|@]|A|$]]|$1|U|3|V|5|L|7|19|8|@]|9|@]|A|$M|N]]|$1|W|3|X|5|6|7|1A|8|@$D|1B|E|1C|F|G]]|9|@]|A|$]]|$1|Y|3|-4|5|6|7|1D|8|@]|9|@]|A|$]]]|Z|$]]

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

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

Example:

<pre><code>/*!50017 DEFINER=`user`@`111.22.33.44`*/
</code></pre>

is rendered helpless, as little as doing this ..

<pre><code>/* 50017 DEFINER=`user`@`111.22.33.44`*/
</code></pre>

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

<pre><code>mysqldump | /usr/bin/perl -pe 's/\!\d+ DEFINER/DEFINER/' &gt; dumpfile.sql
</code></pre>

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

blocks|key|426130|text|根据其他人的建议，下面是一个示例，说明如何在转储完成后从转储中删除定义器：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|426131|mysqldump+-u+user+--password='password'+-h+localhost+database+%7C+grep+-v+"50013+DEFINER"+>+dump.sql|code-block|syntax|javascript|426132|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>mysqldump -u user --password='password' -h localhost database | grep -v "50013 DEFINER" &gt; dump.sql
</code></pre>

blocks|key|426589|text|正如其他人提到的，使用任何类型的正则表达式剥离定义器并不太复杂。但其他示例为我剥离了视图定义。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|426590|这就是我使用的正则表达式：|426591|sed+-e+'s/DEFINER=[%5E+]*+/+/'|code-block|syntax|javascript|426592|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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:

<pre><code>sed -e 's/DEFINER=[^ ]* / /'
</code></pre>

blocks|key|640669|text|对于自动化的解决方案，您可以查看mysqlmigrate。它是一个围绕mysqldump的Bash包装器，允许您迁移数据库并忽略定义器语句。示例：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|640670|$+mysqlmigrate+-u+root+-p+pass+--ignore-definer+from_db+to_db|code-block|syntax|javascript|640671|http://thesimplesynthesis.com/post/mysqlmigrate+(或GitHub)|640672|否则，是的，需要一个像Abhay指出的命令：|640673|$+mysqldump+-u+root+-ppass+the_db+%7C+sed+-e+'s/DEFINER[+]*=[+]*[%5E*]*\*/\*/'+>+the_db.sql|640674|entityMap|0|LINK|mutability|MUTABLE|url|http://thesimplesynthesis.com/post/mysqlmigrate|1|https://github.com/josephdpurcell/mysqlmigrate^0|G|C|Z|9|0|0|0|1B|0|1E|6|1|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@$9|11|A|12|B|C]|$9|13|A|14|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|15|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|16|8|@]|D|@$9|17|A|18|1|19]|$9|1A|A|1B|1|1C]]|E|$]]|$1|M|3|N|5|6|7|1D|8|@]|D|@]|E|$]]|$1|O|3|P|5|H|7|1E|8|@]|D|@]|E|$I|J]]|$1|Q|3|-4|5|6|7|1F|8|@]|D|@]|E|$]]]|R|$S|$5|T|U|V|E|$W|X]]|Y|$5|T|U|V|E|$W|Z]]]]

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

<pre><code>$ mysqlmigrate -u root -p pass --ignore-definer from_db to_db
</code></pre>

<a href="http://thesimplesynthesis.com/post/mysqlmigrate" rel="noreferrer">http://thesimplesynthesis.com/post/mysqlmigrate</a> (or <a href="https://github.com/josephdpurcell/mysqlmigrate" rel="noreferrer">GitHub</a>)

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

<pre><code>$ mysqldump -u root -ppass the_db | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' &gt; the_db.sql
</code></pre>

blocks|key|640982|text|你应该在这里找到答案：https://dba.stackexchange.com/questions/9249/how-do-i-change-the-definer-of-a-view-in-mysql/29079#29079|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|640983|在我看来，解决这个问题的最好方法不是使用sed或grep或任何其他方法添加额外的字符串解析，而是mysqldump能够通过添加--single-transaction生成一个很好的转储|640984|entityMap|0|LINK|mutability|MUTABLE|url|https://dba.stackexchange.com/questions/9249/how-do-i-change-the-definer-of-a-view-in-mysql/29079#29079^0|B|2V|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]]|C|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]]]

You should look the answer here: <a href="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</a>

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

blocks|key|426661|text|在OSX上删除文件中定义器的另一个选项：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|426662|sed+-i+''+'s/DEFINER=`[%5E`][%5E`]*`@`[%5E`][%5E`]*`//g'+file.sql|code-block|syntax|javascript|426663|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Another option on OSX to remove definers in-file:

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

blocks|key|426184|text|一种快速的方法是通过sed管道mysqldump的输出，以删除包含在条件注释中的DEFINER语句。我使用它从CREATE+TRIGGER语句中删除DEFINER，但是您可以调整正则表达式中的条件注释版本号以满足您的需要。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|426185|mysqldump+-u+user+--password='password'+-h+localhost+database+%7C+\
++sed+'s/\/\*!50017+DEFINER=`.*`@`.*`\*\///'+|code-block|syntax|javascript|426186|entityMap|0|LINK|mutability|MUTABLE|url|http://www.gnu.org/software/sed/manual/sed.html|1|http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html^0|14|7|1J|E|22|7|A|3|0|F|9|1|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]|$9|X|A|Y|B|C]|$9|Z|A|10|B|C]]|D|@$9|11|A|12|1|13]|$9|14|A|15|1|16]]|E|$]]|$1|F|3|G|5|H|7|17|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|18|8|@]|D|@]|E|$]]]|L|$M|$5|N|O|P|E|$Q|R]]|S|$5|N|O|P|E|$Q|T]]]]

A quick way to do this is to pipe the output of <a href="http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html" rel="noreferrer">mysqldump</a> through <a href="http://www.gnu.org/software/sed/manual/sed.html" rel="noreferrer">sed</a> to remove the <code>DEFINER</code> statements wrapped in conditional comments. I use this to remove <code>DEFINER</code> from <code>CREATE TRIGGER</code> statements, but you can tweak the condition comment version number in the regex to suit your purposes.

<pre class="lang-sh prettyprint-override"><code>mysqldump -u user --password='password' -h localhost database | \
 sed 's/\/\*!50017 DEFINER=`.*`@`.*`\*\///' 
</code></pre>

blocks|key|426417|text|我不确定它是否有帮助，但我使用SQLyog社区版，可在以下位置获得：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|426418|https://code.google.com/p/sqlyog/wiki/Downloads|offset|length|426419|当转储sql或复制到另一个数据库时，它允许您“忽略定义器”|426420|它是免费的，并提供了许多人所需的基本功能|426421|此外，以下网址亦有收费版本可供选择：|426422|https://www.webyog.com/product/sqlyog|426423|干杯|426424|entityMap|0|LINK|mutability|MUTABLE|url|1^0|0|0|1B|0|0|0|0|0|0|11|1|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Y|8|@]|9|@$D|Z|E|10|1|11]]|A|$]]|$1|F|3|G|5|6|7|12|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|13|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|14|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|15|8|@]|9|@$D|16|E|17|1|18]]|A|$]]|$1|N|3|O|5|6|7|19|8|@]|9|@]|A|$]]|$1|P|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]]|Q|$R|$5|S|T|U|A|$V|C]]|W|$5|S|T|U|A|$V|M]]]]

Not sure if it helps but I use SQLyog Community Edition, available at: -

<a href="https://code.google.com/p/sqlyog/wiki/Downloads" rel="nofollow">https://code.google.com/p/sqlyog/wiki/Downloads</a>

When dumping sql or copying to another database it allows you to 'Ignore DEFINER'

It is free and provides the basic functionality that a lot of people require

There is also a paid version available from: -

<a href="https://www.webyog.com/product/sqlyog" rel="nofollow">https://www.webyog.com/product/sqlyog</a>

Cheers

blocks|key|641328|text|对我来说，这行得通：perl+-p+-i.bak+-e+"s/DEFINER=[%5E+%7C\s]*//g"+yourdump.dmp。这将在每个create+procedure语句上从转储中删除DEFINER=root@localhost|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|641329|entityMap^0|A|1I|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

For me, this works: <code>perl -p -i.bak -e "s/DEFINER=[^ |\s]*//g" yourdump.dmp</code>.
This removed DEFINER=root@localhost from my dump, on each create procedure statment

blocks|key|426083|text|将所有的定义器用户替换为CURRENT_USER。在创建备份的gradle脚本中，replace脚本如下所示：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|426084|ant.replaceregexp(file:+"$buildDir/sql/setupDB.sql",+match:+"`${project.ext.prod_db_username}`@`%25`",+replace:+"CURRENT_USER",+byline:+true);|code-block|syntax|javascript|426085|这实际上就是调用ANT。那你就不用担心了。|426086|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Replace all of your definer users with CURRENT_USER. In my gradle script that creates the backup, my replace script looks like:

<pre><code>ant.replaceregexp(file: "$buildDir/sql/setupDB.sql", match: "`${project.ext.prod_db_username}`@`%`", replace: "CURRENT_USER", byline: true);
</code></pre>

which really is just calling ANT. Then you won't have to worry about it.

blocks|key|426760|text|这是一个完整的工作解决方案，可以删除MySQL+5.6.x和Linux的定义器信息。(在CentOS+6.5上测试)。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|426761|通常，我们必须替换Mysql转储中的以下条目(如果与数据和触发器/例程/函数一起使用)。|426762|/*!50013+DEFINER=`MYSQLUSER`@`localhost`+SQL+SECURITY+DEFINER+*/
/*!50013+DEFINER=`MYSQLUSER`@`%25`+SQL+SECURITY+DEFINER+*/
CREATE+DEFINER=`MYSQLUSER`@`%25`+PROCEDURE+`PROCEDURENAME`(
CREATE+DEFINER=`MYSQLUSER`@`localhost`+PROCEDURE+`PROCEDURENAME`(
CREATE+DEFINER=`MYSQLUSER`@`%25`+FUNCTION+`FUNCTIONNAME`(
CREATE+DEFINER=`MYSQLUSER`@`localhost`+FUNCTION+`FUNCTIONNAME`(
/*!50003+CREATE*/+/*!50017+DEFINER=`MYSQLUSER`@`%25`*/+/*!50003+TRIGGER+`TRIGGERNAME`
/*!50003+CREATE*/+/*!50017+DEFINER=`MYSQLUSER`@`localhost`*/+/*!50003+TRIGGER+`TRIGGERNAME`|code-block|syntax|javascript|426763|转储是使用下面的mysqldump命令获取的。|426764|mysqldump+-uMYSQLUSER+-pPASSWORD+DATABASENAME+-R+>+dbdump.sql|426765|所需的转储文件不包含定义器信息，可以使用以下三个命令获取。|426766|Command-1++++
sed+-i+'s%7CDEFINER=[%5E*]*\*%7C\*%7Cg'+[PATH/]dbdump.sql

Command-2
find+-name+[PATH/]dbdump.sql+%7C+xargs+perl+-pi+-e+"s/+DEFINER=\`MYSQLUSER\`@\`localhost\`//"

Command-3
find+-name+[PATH/]dbdump.sql+%7C+xargs+perl+-pi+-e+"s/+DEFINER=\`MYSQLUSER\`@\`%25\`//"|426767|如果转储文件在当前文件夹中，则忽略PATH/。|426768|如果表中的数据非常大，则将转储放在两个文件中，一个转储文件中包含数据，另一个转储文件中仅包含脚本的转储(触发器/函数/过程)。并在第二个转储(脚本)文件上运行上述三个命令。|426769|entityMap^0|I|B|18|A|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@$9|11|A|12|B|C]|$9|13|A|14|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|15|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|16|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|17|8|@]|D|@]|E|$]]|$1|O|3|P|5|J|7|18|8|@]|D|@]|E|$K|L]]|$1|Q|3|R|5|6|7|19|8|@]|D|@]|E|$]]|$1|S|3|T|5|J|7|1A|8|@]|D|@]|E|$K|L]]|$1|U|3|V|5|6|7|1B|8|@]|D|@]|E|$]]|$1|W|3|X|5|6|7|1C|8|@]|D|@]|E|$]]|$1|Y|3|-4|5|6|7|1D|8|@]|D|@]|E|$]]]|Z|$]]

Here is a complete working solution to remove DEFINER information for <code>MySQL 5.6.x</code> and Linux.(Tested on <code>CentOS 6.5</code>).

Usaually we have to replace following entries from Mysql dump(if taken along with data and triggers/routines/functions).

<pre><code>/*!50013 DEFINER=`MYSQLUSER`@`localhost` SQL SECURITY DEFINER */
/*!50013 DEFINER=`MYSQLUSER`@`%` SQL SECURITY DEFINER */
CREATE DEFINER=`MYSQLUSER`@`%` PROCEDURE `PROCEDURENAME`(
CREATE DEFINER=`MYSQLUSER`@`localhost` PROCEDURE `PROCEDURENAME`(
CREATE DEFINER=`MYSQLUSER`@`%` FUNCTION `FUNCTIONNAME`(
CREATE DEFINER=`MYSQLUSER`@`localhost` FUNCTION `FUNCTIONNAME`(
/*!50003 CREATE*/ /*!50017 DEFINER=`MYSQLUSER`@`%`*/ /*!50003 TRIGGER `TRIGGERNAME`
/*!50003 CREATE*/ /*!50017 DEFINER=`MYSQLUSER`@`localhost`*/ /*!50003 TRIGGER `TRIGGERNAME`
</code></pre>

The dump was taken with below mysqldump command.

<pre><code>mysqldump -uMYSQLUSER -pPASSWORD DATABASENAME -R &gt; dbdump.sql
</code></pre>

The required dump file with no DEFINER information can be obtained with below three commands.

<pre><code>Command-1 
sed -i 's|DEFINER=[^*]*\*|\*|g' [PATH/]dbdump.sql

Command-2
find -name [PATH/]dbdump.sql | xargs perl -pi -e "s/ DEFINER=\`MYSQLUSER\`@\`localhost\`//"

Command-3
find -name [PATH/]dbdump.sql | xargs perl -pi -e "s/ DEFINER=\`MYSQLUSER\`@\`%\`//"
</code></pre>

If the dump file is in your current folder then ignore [PATH/].

If data in tables is very huge then take the dump in two files, in one dump file take the dump with data and in other dump file on take the dump of the scripts only (Triggers/Functions/Procedures.) and run the above three commands on 2nd dump(scripts) file.

blocks|key|427104|text|无问题转储和恢复的最佳方法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|427105|MYSQL转储|427106|mysqldump+-h+HOST+-u+USER+-pPASSWORD+--single-transaction+--quick+--skip-lock-tables+--triggers+--routines+--events+DATABASE+%7C+gzip+>+"DATABASE.sql.gz"+&|code-block|syntax|javascript|427107|MYSQL恢复|427108|gunzip+DATABASE.sql.gz

sed+-i+'s/DEFINER=[%5E+]*+/+/'+DATABASE.sql
sed+-i+'s/SET+@MYSQLDUMP_TEMP_LOG_BIN+=+@@SESSION.SQL_LOG_BIN;/+/'+DATABASE.sql
sed+-i+'s/SET+@@SESSION.SQL_LOG_BIN=+0;/+/'+DATABASE.sql
sed+-i+'s/SET+@@GLOBAL.GTID_PURGED='';/+/'+DATABASE.sql
sed+-i+'s/SET+@@SESSION.SQL_LOG_BIN+=+@MYSQLDUMP_TEMP_LOG_BIN;/+/'+DATABASE.sql

mysql+-h+HOST+-u+USER+-pPASSWORD+<+DATABASE.sql|427109|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Best way of dumping and restoring without problems:
<h3>MYSQL DUMP</h3>
<pre><code>mysqldump -h HOST -u USER -pPASSWORD --single-transaction --quick --skip-lock-tables --triggers --routines --events DATABASE | gzip &gt; &quot;DATABASE.sql.gz&quot; &amp;
</code></pre>
<h3>MYSQL RESTORE</h3>
<pre><code>gunzip DATABASE.sql.gz

sed -i 's/DEFINER=[^ ]* / /' DATABASE.sql
sed -i 's/SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;/ /' DATABASE.sql
sed -i 's/SET @@SESSION.SQL_LOG_BIN= 0;/ /' DATABASE.sql
sed -i 's/SET @@GLOBAL.GTID_PURGED='';/ /' DATABASE.sql
sed -i 's/SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;/ /' DATABASE.sql

mysql -h HOST -u USER -pPASSWORD &lt; DATABASE.sql
</code></pre>

blocks|key|640768|text|在Linux机器上，您可以使用以下一行程序：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|640769|mysql+-uuser+-ppwd+-A+--skip-column-names+-e"SELECT+CONCAT('SHOW+CREATE+VIEW+',table_schema,'.',table_name,';')+FROM+information_schema.tables+WHERE+engine+IS+NULL+and+table_schema+like+'mydb%25'"+%7C+mysql+-uuser+-ppwd+-A+--skip-column-names+%7C+sed+-rn+'s/.*?VIEW+([%5E\s]%2B?)+(AS+.*?)\s([%5E\s]%2B?)\s([%5E\s]%2B?)/DROP+VIEW+\1;\nCREATE+VIEW+\1+\2;/p'+%7C+mysql+-uuser+-ppwd+-A+--skip-column-names|code-block|syntax|javascript|640770|您只需将粗体字符串替换为您的DB用户凭证和数据库名称/+like模式。|640771|更多信息请点击此处：https://blog.novoj.net/posts/2014-05-16-recreate-mysql-views-without-definer-one-liner-solution-linux/|offset|length|640772|entityMap|0|LINK|mutability|MUTABLE|url|https://blog.novoj.net/posts/2014-05-16-recreate-mysql-views-without-definer-one-liner-solution-linux/^0|0|0|0|A|2U|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|X|8|@]|9|@$K|Y|L|Z|1|10]]|A|$]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

On Linux machines you can use this one-liner:
<pre><code>mysql -uuser -ppwd -A --skip-column-names -e&quot;SELECT CONCAT('SHOW CREATE VIEW ',table_schema,'.',table_name,';') FROM information_schema.tables WHERE engine IS NULL and table_schema like 'mydb%'&quot; | mysql -uuser -ppwd -A --skip-column-names | sed -rn 's/.*?VIEW ([^\s]+?) (AS .*?)\s([^\s]+?)\s([^\s]+?)/DROP VIEW \1;\nCREATE VIEW \1 \2;/p' | mysql -uuser -ppwd -A --skip-column-names
</code></pre>
You have only to replace strings in bold with your DB user credentials and database name / like pattern.
More info here: <a href="https://blog.novoj.net/posts/2014-05-16-recreate-mysql-views-without-definer-one-liner-solution-linux/" rel="nofollow noreferrer">https://blog.novoj.net/posts/2014-05-16-recreate-mysql-views-without-definer-one-liner-solution-linux/</a>

blocks|key|640921|text|谢谢你们所有人的提示。由于懒惰，我写了一个名为"MYsqldump“的脚本：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|640922|DB=$1
HOST=$2
USER=$3
MYSQLDUMP='/usr/bin/mysqldump'
PARAMS="--complete-insert+--disable-keys=0+--extended-insert=0+--routines=0+--skip-comments"
DAY=`date+%2B%25d`
MONTH=`date+%2B%25m`
YEAR=`date+%2B%25Y`
FILE=$DB.$DAY-$MONTH-$YEAR.sql

++++if+((+$#+<+3+))+;+then
++++++++echo+""
++++++++echo+"usage:+MYsqldump+<db>+<host>+<user>"
++++++++echo+""
++++++++exit+1
++++fi

++++$MYSQLDUMP+-h+$HOST+-u+$USER+-p++$PARAMS+$DB+%7C+grep+-v+'/*!50013+DEFINER'+>+$FILE|code-block|syntax|javascript|640923|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Thank you all for the hints.
Being lazy, I wrote a script named: "MYsqldump" :

<pre><code>DB=$1
HOST=$2
USER=$3
MYSQLDUMP='/usr/bin/mysqldump'
PARAMS="--complete-insert --disable-keys=0 --extended-insert=0 --routines=0 --skip-comments"
DAY=`date +%d`
MONTH=`date +%m`
YEAR=`date +%Y`
FILE=$DB.$DAY-$MONTH-$YEAR.sql

 if (( $# &lt; 3 )) ; then
 echo ""
 echo "usage: MYsqldump &lt;db&gt; &lt;host&gt; &lt;user&gt;"
 echo ""
 exit 1
 fi

 $MYSQLDUMP -h $HOST -u $USER -p $PARAMS $DB | grep -v '/*!50013 DEFINER' &gt; $FILE
</code></pre>

blocks|key|426825|text|适用于我所有对象的最简单的正则表达式是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|426826|sed+'s/DEFINER=[%5E+]*`//'+...|code-block|syntax|javascript|426827|请注意，quote-names必须为TRUE+(默认情况下)。|offset|length|style|CODE|426828|希望在较新的版本中，mysqlpump在5.7版本中引入的开关--skip-definer也适用于mysqldump。|426829|entityMap^0|0|0|4|B|I|4|0|V|E|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@$I|T|J|U|K|L]|$I|V|J|W|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|X|8|@$I|Y|J|Z|K|L]]|9|@]|A|$]]|$1|O|3|-4|5|6|7|10|8|@]|9|@]|A|$]]]|P|$]]

The simplest regex that works for me with all objects is:

<pre><code>sed 's/DEFINER=[^ ]*`//' ...
</code></pre>

Note that <code>quote-names</code> has to be <code>TRUE</code> (which is by default).

Hope that in newer versions the switch <code>--skip-definer</code> introduced in mysqlpump in version 5.7 comes to mysqldump, too.

blocks|key|426913|text|我已经使用PowerShell脚本删除了DEFINER中的所有行|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|426914|get-content+$file_in_full_path+%7C+select-string+-pattern+$line_string_delete+-notmatch+%7C+Out-File+-width+1000000000+-Encoding+Default+$file_out_full_path|426915|entityMap^0|K|7|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|M|8|@]|D|@]|E|$]]|$1|H|3|-4|5|6|7|N|8|@]|D|@]|E|$]]]|I|$]]

I have used PowerShell script to remove all the lines with <code>DEFINER</code>:

get-content $file_in_full_path | select-string -pattern $line_string_delete -notmatch | Out-File -width 1000000000 -Encoding Default $file_out_full_path

blocks|key|641399|text|remove_definers.bat：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|641400|@echo+off
(for+/f+"tokens=1,2*"+%25%25a+in+(dumpfile.sql)+do+(
+if+"%25%25b"==""+(echo+%25%25a)+else+(
++echo+%25%25b+%7C+find+"DEFINER"+>+null+&&+echo+%25%25a+%25%25c+%7C%7C+echo+%25%25a+%25%25b+%25%25c
+)
))+>+dumpfile_nodefiners.sql|code-block|syntax|javascript|641401|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

remove_definers.bat:

<pre><code>@echo off
(for /f "tokens=1,2*" %%a in (dumpfile.sql) do (
 if "%%b"=="" (echo %%a) else (
 echo %%b | find "DEFINER" &gt; null &amp;&amp; echo %%a %%c || echo %%a %%b %%c
 )
)) &gt; dumpfile_nodefiners.sql
</code></pre>

blocks|key|427002|text|提到https://stackoverflow.com/a/19707173/1488762，我怀疑下面的方法可能在非常大的数据库上执行得更好--在最后修改定义器，而不是在每一行上执行额外的正则表达式处理：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|427003|mysqldump+myDB+>+dump.sql
cat+>>+dump.sql+<<+END
SELECT+CONCAT("ALTER+DEFINER=`youruser`@`host`+VIEW+",+
table_name,+"+AS+",+view_definition,+";")+
FROM+information_schema.views+
WHERE+table_schema='myDB';
END|code-block|syntax|javascript|427004|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/19707173/1488762^0|2|18|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

Referring to <a href="https://stackoverflow.com/a/19707173/1488762">https://stackoverflow.com/a/19707173/1488762</a>, I suspect the following may perform better on very large databases - modify the definer at the end, rather than performing additional regex processing on every line:
<pre><code>mysqldump myDB &gt; dump.sql
cat &gt;&gt; dump.sql &lt;&lt; END
SELECT CONCAT(&quot;ALTER DEFINER=`youruser`@`host` VIEW &quot;, 
table_name, &quot; AS &quot;, view_definition, &quot;;&quot;) 
FROM information_schema.views 
WHERE table_schema='myDB';
END
</code></pre>

blocks|key|427054|text|仅供我将来参考，sd版本。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|427055|mysqldump+...+%7C+sd+'DEFINER=.*?\*'+'*'+>+file.sql|code-block|syntax|javascript|427056|entityMap^0|8|2|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

Just for my future reference, <code>sd</code> version.
<pre><code>mysqldump ... | sd 'DEFINER=.*?\*' '*' &gt; file.sql
</code></pre>

blocks|key|641629|text|我删除在我的MariaDB+10.3上测试的DEFINER的解决方案：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|641630|mysqldump+%7C+grep+-v+-P+'SQL+SECURITY+DEFINER+\*/$'+%7C+perl+-pe+'s/(?<=%5E\/\*!50003+CREATE\*\/+)\/\*!50017+DEFINER=`.%2B`@`.%2B`\*\/+(?=\/\*!50003+TRIGGER)//'|code-block|syntax|javascript|641631|对于视图，grep会删除它；对于触发器，perl会删除它|641632|这个解决方案是防攻击的，因为它匹配行首和行尾。因此，即使攻击者知道您正在进行转储上的DEFINER替换，他在数据中排列的DEFINER也不会被替换。|641633|entityMap^0|M|7|0|0|5|4|K|4|0|16|7|1O|7|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@$9|V|A|W|B|C]|$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|M|3|N|5|6|7|Z|8|@$9|10|A|11|B|C]|$9|12|A|13|B|C]]|D|@]|E|$]]|$1|O|3|-4|5|6|7|14|8|@]|D|@]|E|$]]]|P|$]]

My solution for removing <code>DEFINER</code> tested on my MariaDB 10.3:
<pre><code>mysqldump | grep -v -P 'SQL SECURITY DEFINER \*/$' | perl -pe 's/(?&lt;=^\/\*!50003 CREATE\*\/ )\/\*!50017 DEFINER=`.+`@`.+`\*\/ (?=\/\*!50003 TRIGGER)//'
</code></pre>
<code>grep</code> removes it for views, <code>perl</code> removes it for triggers
This solution is is attacker-proof, because it matches start and end of lines. So even if attacker knows you are doing replace of <code>DEFINER</code> on your dumps, his <code>DEFINER</code> arranged in the data won't be replaced.

blocks|key|641682|text|正如其他人所说，一种可能的解决方案是将DEFINER子句中的用户名替换为CURRENT_USER。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|641683|请注意，我们必须在普通SQL语句和特殊注释中都考虑DEFINER，如以下示例所示：|641684|CREATE+DEFINER=`root`@`localhost`+FUNCTION...
/*!50013+DEFINER=`MYSQLUSER`@`%25`+SQL+SECURITY+DEFINER+*/
/*!50017+DEFINER=`root`@`localhost`*/|code-block|syntax|javascript|641685|所以，对我来说，有效的方法是：|641686|mysqldump+<my-options>+%7C+sed+-E+'s/DEFINER=[%5E+*]%2B/DEFINER=CURRENT_USER/g'+>+dumpfile.sql|641687|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

As others have said, one possible solution is to replace the usernames in DEFINER clauses to CURRENT_USER. 
Note that we must take into account DEFINER in both plain SQL statements and in special comments, like in the following examples:
<pre><code>CREATE DEFINER=`root`@`localhost` FUNCTION...
/*!50013 DEFINER=`MYSQLUSER`@`%` SQL SECURITY DEFINER */
/*!50017 DEFINER=`root`@`localhost`*/
</code></pre>
So, for me, what works is this:
<pre><code>mysqldump &lt;my-options&gt; | sed -E 's/DEFINER=[^ *]+/DEFINER=CURRENT_USER/g' &gt; dumpfile.sql
</code></pre>

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

<pre><code>"DEFINER=`root`@`localhost`" 
</code></pre>

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?

Remove DEFINER clause from MySQL Dumps

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋 

腾讯云代码助手

CODING DevOps

Cloud Studio

SDK中心

API中心

命令行工具

我有一个数据库的MySQL转储。在它里面，有定义子句，看起来像，"DEFINER=`root`@`localhost`" 也就是说，这些定义器子句位于我的CREATE VIEW和CREATE PROCEDURE语句中。有没有办法从我的转储文件中删除这些定义器子句？

问从MySQL转储中删除定义器子句
EN

回答 23

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从MySQL转储中删除定义器子句EN

回答 23

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从MySQL转储中删除定义器子句
EN