在许多视图上修改DEFINER

更新后备份我的数据库时遇到问题。我一直在摸索我的系统，试图找出原因。我运行的一个查询返回了此结果。

Got error: 1449: The user specified as a definer ('cittool'@'%') does not exist when using LOCK TABLES

经过一番调查，看来这些视图的定义者是一个已从系统中清除的旧开发人员帐户。带有此问题的数据库和视图很少使用，并且大多数用于存档目的。

大约有40个带有定义器的视图不再存在。是否有一种简单的方法可以一次将定义器更改为其他帐户？有没有办法让mysqldump轻松地将所有视图转储到文件中，以便我可以编辑该文件并重新创建视图？

对我来说，我只是将丢失的帐户添加到数据库中，错误消失了。

#1 楼

创建具有所有视图定义的文本文件：

mysql -uusername -ppassword -A --skip-column-names -e"SELECT CONCAT('SHOW CREATE VIEW ',table_schema,'.',table_name,'\G') FROM information_schema.tables WHERE engine IS NULL" | mysql -uusername -ppassword -A --skip-column-names > AllMyViews.sql

您可以从此处编辑AllMyViews.sql。然后，删除视图

mysql -uusername -ppassword -A --skip-column-names -e"SELECT CONCAT('DROP VIEW ',table_schema,'.',table_name,';') FROM information_schema.tables WHERE engine IS NULL" | mysql -uusername -ppassword -A

编辑AllMyViews.sql后，重新加载它们

mysql -uusername -ppassword -A < AllMyViews.sql

试一试!!!

如果未显示information_schema的view_definition，但SHOW CREATE VIEW工作正常，则+1是一个很好的选择。

–德里克·唐尼（Derek Downey）
2011年7月26日在21:01

从理论上来说，我更喜欢DTest的解决方案，但是我的系统出现了一些问题，阻止了它的正常运行。我基本上使用上面的方法将show create语句转储到文件中，并且我不得不手动对其稍加编辑然后运行它们。

– Zoredache
2011年7月26日在21:10

当定义者用户不存在时，SHOW CREATE VIEW不起作用-它引发与mysqldump相同的错误：指定为定义者的用户（'abc'@'1.2.3.4'）不存在

– Marki555
2014年4月30日19:42

@ Marki555然后，您必须临时创建该用户。运行CREATE USER'abc'@'1.2.3.4';。然后，再试一次。

– RolandoMySQLDBA
2014年4月30日19:44

我只是注意到View使用了一些存储过程，并且该错误消息被实现给该过程的定义者，而不是视图本身

– Marki555
2014年4月30日在20:24

#2 楼

您可以将ALTER VIEW与信息模式结合使用。您提到将其转储到文本文件中，所以可能是这样的：

SELECT CONCAT("ALTER DEFINER=`youruser`@`host` VIEW `",table_name,"` AS ", view_definition,";") 
FROM information_schema.views WHERE table_schema='databasename'

与mysql命令行混合（假设* nix，不熟悉Windows）：

> echo "*abovequery*" | mysql -uuser -p > alterView.sql
> mysql -uuser -p databasename < alterView.sql

侧注：您不能直接更改information_schema条目。
注2：如果您不进行此操作，则一次仅适用于一个数据库。在table_schema中，您需要在每个表之间插入USE命令。

我喜欢这一点，因为它看起来比我的参与程度更少，更简洁。 +1 !!! （我忘记了ALTER VIEW）

– RolandoMySQLDBA
2011年7月26日19:12

知道为什么view_definition字段为空吗？从information_schema.views中选择table_name，view_definition，其中table_schema ='cittmp';返回|员工| |

– Zoredache
2011年7月26日20:02

从聊天中的讨论看来，这是超级特权的权限问题。 bugs.mysql.com/bug.php?id=39733

–德里克·唐尼（Derek Downey）
2011年7月26日在20:49

完美的解决方案！ +1

–巴勃罗·马丁内斯（Pablo Martinez）
2014年5月22日12:50

至少在RedHat上，如果要输出到文件，则必须转义回跳号，例如echo“ SELECT CONCAT（” ALTER DEFINER = \`您的用户\`@ \`主机\`视图...

–clav
16 Jan 14'20:41

#3 楼

自动执行Derek的解决方案，这会将DEFINER更改为root@localhost，并在所有数据库的所有视图中设置SQL SECURITY INVOKER（确保首先要设置！）：

mysql -BNe 'show databases' | \
egrep -v '^(information_schema|performance_schema)$' | \
while read DB
do 
    mysql -BNe "SELECT CONCAT(\"ALTER DEFINER=\`root\`@\`localhost\` SQL SECURITY INVOKER VIEW \",table_name,\" AS \", view_definition,\";\") FROM information_schema.views WHERE table_schema=\"$DB\"" | \
    mysql $DB
done

警告：在运行它之前，请确保已进行完整的mysql备份（例如，通过停止mysqld并备份/ var / lib / mysql中的所有文件），以防万一...它对我有用，但YMMV。 br />
还应该使用以下方法检查所有表/视图：

mysql -BNe 'show databases' | \
egrep -v '^(information_schema|performance_schema)$' | \
while read DB
do 
   mysql -BNe 'show tables' $DB | \
   while read t
   do
      echo "check table $t;"
   done | mysql -BN $DB
done | \
egrep -v 'status\s*OK'

它不再应该抱怨视图中的无效定义器。

无耻地将此添加到我的摘要中。谢谢！

–stefgosselin
19年7月27日在1:02

#4 楼

导出数据库的所有视图<DB>：

mysql -BNe "SELECT TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA = '<DB>' AND TABLE_TYPE = 'VIEW'" \
    information_schema | xargs mysqldump --single-transaction --no-data <DB> >views.sql

或：

mysql -BNe "SELECT TABLE_NAME FROM VIEWS WHERE TABLE_SCHEMA = '<DB>'" \
    information_schema | xargs mysqldump --single-transaction --no-data <DB> >views.sql

编辑views.sql（更改定义器））并重新创建它们：

cat views.sql | mysql <DB>

必要时指定-u和-p开关。

应该以哪种方式编辑VIEWS views.sql？另外，如何重新创建它们？

– SherylHohman
19年8月24日在18:57

@SherylHohman将DEFINER = olduser @ oldhost更改为DEFINER = newuser @ newhost，cat views.sql | mysql 重新创建视图。

–x-yuri
19-09-3在5:50

#5 楼

我创建了一个简单的python脚本，将生产db定义器替换为本地db定义器。此脚本将修改转储文件并自动导入指定的数据库。

GIT存储库：https://github.com/mjakal/db-auto-import

编程黑洞网