从 MySQLDumps 中删除 DEFINER 子句

我有一个数据库的 MySQL 转储，里面有 DEFINER 子句,

"DEFINER=`root`@`localhost`"

也就是说，这些 DEFINER 子句位于 CREATEVIEW 和 CREATEPROCEDURE 语句上。有没有办法从转储文件中删除这些 DEFINER 子句？

149207 次浏览
小开
最佳答案

我不认为有一种方法可以忽略向转储添加 DEFINER。但是，在创建转储文件之后，有一些方法可以删除它们。

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

  2. 使用 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
小开

用 CURRENT _ USER 替换所有定义器用户。在创建备份的 gradle 脚本中，替换脚本如下:

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

那么你就不用担心这个问题了。

小开

根据对方的建议，这里有一个如何在转储完成后将 DEFINER 从转储中移除的例子:

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

一种快速的方法是通过管道将 Mysqldump的输出通过 Sed来删除包装在条件注释中的 DEFINER语句。我使用它来从 CREATE TRIGGER语句中删除 DEFINER，但是您可以调整正则表达式中的条件注释版本号，以满足您的需要。

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

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

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

Http://thesimplesynthesis.com/post/mysqlmigrate (或 GitHub)

否则，是的，像阿布伊这样的命令是必要的:

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

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

只要删除代码和 DEFINER 之前的 !，注释的其余部分就会变成常规注释。

例如:

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

是无助的，尽管这样做。。

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

但是，最简单的 regexp 是删除! 和数字

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

删除 !#### DEFINER并替换为 DEFINER... 您也可以删除 DEFINER，这真的没有关系-一旦“ !”消失了

小开

在 Linux 机器上，你可以使用这句俏皮话:

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%'" | 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

您只需用 DB 用户凭据和数据库名称/类似模式替换粗体字符串。

更多信息请点击: https://blog.novoj.net/posts/2014-05-16-recreate-mysql-views-without-definer-one-liner-solution-linux/

小开

您可以使用 SED 删除

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

在 MacOS 中:

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

不知道是否有帮助，但我使用 SQLyog 社区版，可在:-

Https://code.google.com/p/sqlyog/wiki/downloads

当转储 sql 或复制到另一个数据库时，它允许您‘忽略 DEFINER’

它是免费的，并提供许多人需要的基本功能

还有一个付费版本可从:-

Https://www.webyog.com/product/sqlyog

干杯

小开

我能让它在 Windows 下工作的唯一方法是:

安装 http://gnuwin32.sourceforge.net/使 Sed位于命令行中，并将其添加到 WindowsPATH: D: 程序 GetGnuWin32 bin;

sed -i "s|DEFINER=`mydbuser`@`%%` SQL SECURITY DEFINER||g" "D:/prod_structure.sql"


sed -i "s|DEFINER=`mydbuser`@`%%`||g" "D:/prod_structure.sql"


mysqldump -P{port} -h{host} -u{user} -p{password} --routines --no-data --lock-tables=false database_prod > D:\prod_structure.sql
小开

谢谢你们的提示。 由于懒惰，我写了一个名为“ MYsqldump”的脚本:

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 (( $# < 3 )) ; then
echo ""
echo "usage: MYsqldump <db> <host> <user>"
echo ""
exit 1
fi


$MYSQLDUMP -h $HOST -u $USER -p  $PARAMS $DB | grep -v '/*!50013 DEFINER' > $FILE
小开

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

在我看来，解决这个问题的最佳方法是，不要使用 sed 或 grep 或其他任何方法添加额外的字符串解析，而是使用添加—— single-action 来生成一个很好的转储

小开

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

这是我用过的正则表达式:

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

OSX 上删除文件中定义符的另一个选项:

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

由于 mysql 版本5.7.8，您可以在 mysqlump 中使用 --skip-definer选项，例如:

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

请参阅 http://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_skip-definer更新的 mysql 手册

小开

这里是一个完整的工作解决方案，删除 MySQL 5.6.x和 Linux 的 DEFINER 信息。

通常，我们必须替换来自 Mysql dump 的以下条目(如果与数据和触发器/例程/函数一起使用的话)。

/*!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`

转储是在 mysqldump 命令下面执行的。

mysqldump -uMYSQLUSER -pPASSWORD DATABASENAME -R > dbdump.sql

可以使用以下三个命令获取不包含 DEFINER 信息的所需转储文件。

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\`@\`%\`//"

如果转储文件在当前文件夹中，则忽略[ PATH/]。

如果表中的数据非常庞大，那么在两个文件中获取转储文件，在一个转储文件中获取带有数据的转储文件，在另一个转储文件中只获取脚本的转储文件(触发器/函数/过程)并在第2个 dump (脚本)文件上运行以上三个命令。

小开

比如:

DELIMITER ;;
CREATE DEFINER=`mydb`@`localhost` FUNCTION `myfunction`() RETURNS int(11)
begin
(...)
end ;;

试试这个:

mysqldump --force --routines --opt --user=myuser --databases mydb | sed -e 's/DEFINER=`.*`@`.*`\ //g'
小开

对所有对象都适用的最简单的正则表达式是:

sed 's/DEFINER=[^ ]*`//' ...

请注意，quote-names必须是 TRUE(默认情况下是 TRUE)。

希望在更新的版本中，5.7版本的 mysqldump 中引入的交换机 --skip-definer也能到 mysqldump。

小开

对我来说，这个是有效的: perl -p -i.bak -e "s/DEFINER=[^ |\s]*//g" yourdump.dmp。 这样就从每个 create 过程语句的转储中删除了 DEFINER = root@localhost

小开

我使用 PowerShell 脚本删除了 DEFINER的所有代码行:

Get-content $file _ in _ full _ path | select-string-pattern $line _ string _ delete-notmatch | Out-File-width 10000000-Encoding Default $file _ out _ full _ path

小开

  1. 用任何编辑器打开你的数据库，我用记事本 + + ,

  2. 找到所有测试的 DEFINER=root@localhost 并将其替换为空("") IE。删除它。

然后您可以将其导入到任何您想要的主机。

小开

对我来说什么都不管用，所以我需要编写自己的正则表达式。

  1. 尝试将数据库转储到文件中，cat整个文件和 grep只有您的定义语句才能看到它们。

    Sql | grep-o-E’. { ，60} DEFINER. { ，60}’

  2. 写你的正则表达式

  3. 使用这个正则表达式将转储导入 sed:

    Mysqldump | sed-E’s//* ! 500[0-9][0-9] DEFINER = . + ? *///’| sed-E’s/DEFINER = ?[^ ]+??@?[^ ]+??//’

  4. 利润！

小开

Bat:

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

关于 https://stackoverflow.com/a/19707173/1488762，我怀疑以下内容可能在非常大的数据库上执行得更好——在末尾修改定义符，而不是在每一行上执行额外的正则表达式处理:

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
小开

只是为了我将来的参考，sd版本。

mysqldump ... | sd 'DEFINER=.*?\*' '*' > file.sql
小开

没有问题的倾倒和恢复的最佳方式:

MYSQL DUMP

mysqldump -h HOST -u USER -pPASSWORD --single-transaction --quick --skip-lock-tables --triggers --routines --events DATABASE | gzip > "DATABASE.sql.gz" &

MYSQL 还原

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 < DATABASE.sql
小开

我在 MariaDB 10.3上测试了移除 DEFINER的解决方案:

mysqldump | grep -v -P 'SQL SECURITY DEFINER \*/$' | perl -pe 's/(?<=^\/\*!50003 CREATE\*\/ )\/\*!50017 DEFINER=`.+`@`.+`\*\/ (?=\/\*!50003 TRIGGER)//'

grep为视图删除它，perl为触发器删除它

这个解决方案是防攻击者的，因为它匹配行的开始和结束。因此，即使攻击者知道您正在替换转储上的 DEFINER，他安排在数据中的 DEFINER也不会被替换。

小开

如果您的 SQL 文件具有 DEFINER事件(包含和)而没有条件注释，那么您可能会对这种替代 REGEXP 感兴趣。

sed -Ei \
's/DEFINER\s*=\s*(["'\''`]?)[^"'\''`@]+\1\s*@\s*(["'\''`]?)[^"'\''`\*]+\2//g' \
#      [-----1------][----2----][----4----]-5[--6--][----------7------------]
"$FILE"

说明:

  1. DEFINER {潜在空格} = {潜在空格} ..。
  2. ... {任何潜在的报价或反勾，捕获} ..。
  3. (中断序列，添加转义单引号，恢复序列-因为我们用单引号包装构造)
  4. ... (用户名){ string: 无引号或@} ..。
  5. {使用第一个捕获组-引号-如果有} ..。
  6. ... {潜在空格}@{潜在空格} ..。
  7. ... {对于具有新捕获组的主机也是如此，但是不包括星号}。

(不使用注释行运行，因为这会抛出一个错误)

小开

正如其他人所说，一种可能的解决方案是将 DEFINER 子句中的用户名替换为 CURRENT _ USER。
注意，我们必须在普通 SQL 语句和特殊注释中考虑到 DEFINER，如下面的示例所示:

CREATE DEFINER=`root`@`localhost` FUNCTION...
/*!50013 DEFINER=`MYSQLUSER`@`%` SQL SECURITY DEFINER */
/*!50017 DEFINER=`root`@`localhost`*/

所以，对我来说，有效的方法是:

mysqldump <my-options> | sed -E 's/DEFINER=[^ *]+/DEFINER=CURRENT_USER/g' > dumpfile.sql

提交答案