mysqldump和view的问题

Question

当使用mysqldump备份MySQL时，我得到了以下错误。

mysqldump --all-databases --routines >> all.sql
mysqldump: Couldn't execute 'show table status like 'hdkien'': SELECT command denied to user 'tungbt'@'192.168.12.197' for column 'id' in table 'hdcn_hd' (1143)

hdkien是一个视图

CREATE ALGORITHM=UNDEFINED DEFINER=`tungbt`@`192.168.12.197` SQL SECURITY DEFINER VIEW `hdcn`.`hdkien` AS (...striped...)

用户tungbt@192.168.12.197已经拥有了在表hdcn_hd上进行选择的特权，并且我可以从视图hdkien中进行选择，没有问题。

mysql> select * from hdkien limit 1;
+------+-----------+
| id   | shd       |
+------+-----------+
|  876 | ADFADFA1  |
+------+-----------+

更多信息：

• MySQL版本：mysql-community-server-5.5.37-4.el6.x86_64
• 操作系统: CentOS 6.5

为什么在运行mysqldump时会出现错误，如何解决呢？

更新1 (2014/04/17)

我使用用户mysqldump运行'root'@'localhost'。

mysql> show grants for 'root'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '...striped...' WITH GRANT OPTION                             |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+

用户'tungbt'@'192.168.12.197'是视图hdcn.hdkien的定义者( SQL SECURITY是DEFINER )。

+------------------------------------------------------------------------------------------------------------------+
| Grants for tungbt@192.168.12.197                                                                                   |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tungbt'@'192.168.12.197' IDENTIFIED BY PASSWORD '...striped...'                             |
| GRANT ALL PRIVILEGES ON `hdcn`.* TO 'tungbt'@'192.168.12.197'                                                      |
+------------------------------------------------------------------------------------------------------------------+

更新2

$ mysql -ANe"SELECT USER(),CURRENT_USER()"
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+

更新3

mysql> SELECT COUNT(1) MySQLGrantsCount,VERSION() MySQLVersion FROM information_schema.columns WHERE table_schema='mysql' AND table_name='user';
+------------------+--------------+
| MySQLGrantsCount | MySQLVersion |
+------------------+--------------+
|               42 | 5.5.37-log   |
+------------------+--------------+

Answer 1

你需要有显示视图特权。我写了关于2013年12月：获得MySQL数据库模式备份所需的最低权限是哪些？的文章

在那篇文章中，我展示了mysqldump的这些最低特权。

• 选择
• 显示视图 (如果任何数据库都有视图)
• 触发器 (如果任何表都有一个或多个触发器)
• 锁表 (如果使用显式--lock-tables)

您应该运行以下命令：

SHOW GRANTS FOR tungbt@192.168.12.197;

如果SHOW VIEW不在那里，这就是原因所在。

更新2014-04-16 23:06美国东部时间

当你这么做的时候

mysqldump --all-databases --routines >> all.sql

我看你没有指定用户和密码。在这种情况下，您没有以root@localhost的身份登录。在指定root用户时，必须是明确的。

mysqldump -uroot -p --all-databases --routines >> all.sql

您将看到密码提示。输入root@localhost密码，您就可以关闭并运行。

您也可以指定密码。

mysqldump -uroot -ppassword --all-databases --routines >> all.sql

试试看！

狂野建议

如果您使用的是.~/my.cnf，但仍然有一个错误，您可能在错误#70907 mysqldump:无法执行“显示表状态”：拒绝用户的选择命令中遇到了这种情况。

如果配置文件是.~/my.cnf，实际上是/root/.my.cnf，那么您可能不会以Linux登录。你可能得跑一趟。

请运行以下命令

mysql -ANe"SELECT USER(),CURRENT_USER()"

如果您没有两次看到root@localhost，那么您就没有正确地进行身份验证。

在.my.cnf中，您需要确保用户和密码在[client]部分下面

[client]
user=root
password=rootpassword

而不是在[mysql]部分。

更新2014-04-17 13:53美国东部时间

我不禁要看看这个bug报告，并想知道:既然您有DEFINER=tungbt@192.168.12.197，那么根@localhost的行为可能就像tungbt@192.168.12.197吗？我这么说是因为根据关于CREATE视图的MySQL文档：At view definition time, the view creator must have the privileges needed to use the top-level objects accessed by the view. For example, if the view definition refers to table columns, the creator must have some privilege for each column in the select list of the definition, and the SELECT privilege for each column used elsewhere in the definition.

可以将视图的定义器更改为root@localhost，然后再次尝试mysqldump。

Answer 2

我也遇到了一个类似的问题，无法作为root在我的视图上执行mysqldump：

mysqldump: Couldn't execute 'show create table `v_view01`': View 'my_db.v_view01' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)

在我的例子中，这是因为我的底层模式改变了，所以依赖它的视图不再有效。即使我以root的身份执行转储，它仍然说“或者调用视图缺乏权限.”。解决办法很简单：

drop view v_view01

既然视图已经过时了，我就把它扔掉了，然后mysqldump就像往常一样继续进行下去。

Answer 3

我的无法在一个不存在的视图上显示create视图，所以job就辞职了。所以我做了两件事：

create or replace view v_view01 as select * from any_table;
drop view v_view01;

更大的问题是mysqldump默认停止错误-可怕的想法！

如果您需要备份所有其他好的表，那么添加选项--force和以下选项.

mysqldump -u root -p${MP} --all-databases --lock-tables --debug-info --comments --dump-date --force --events --routines --add-drop-table --add-locks --log-error=/somewhere/mysqldump.err >all_db.`date "+%Y%m%d%H%M"`.sql