mysqldump 转储完整模式所需的最小授权？（触发器丢失了！！）

发布于 2024-12-23 08:09:05 字数 795 浏览 2 评论 0原文

我有一个名为 dump 的 MySQL 用户，具有以下权限：

GRANT USAGE ON *.* TO 'dump'@'%' IDENTIFIED BY ...
GRANT SELECT, LOCK TABLES ON `mysql`.* TO 'dump'@'%'
GRANT SELECT, LOCK TABLES ON `myschema`.* TO 'dump'@'%'

我想使用 dump 用户转储所有数据（包括触发器和过程）。我通过以下方式调用 mysqldump：

mysqldump -u dump -p --routines --triggers --quote-names --opt \
    --add-drop-database --databases myschema > myschema.sql

转储的文件一切正常，除了触发器，它们丢失了！

如果我使用 root MySQL 用户尝试 mysqldump，触发器会正确转储：

mysqldump -u root -p --routines --triggers --quote-names --opt \
    --add-drop-database --databases myschema > myschema.sql

所以，我猜这是一个权限问题...额外授予我的转储 MySQL 用户需要正确执行完整转储吗？

原文

I have a MySQL user called dump with the following perms:

GRANT USAGE ON *.* TO 'dump'@'%' IDENTIFIED BY ...
GRANT SELECT, LOCK TABLES ON `mysql`.* TO 'dump'@'%'
GRANT SELECT, LOCK TABLES ON `myschema`.* TO 'dump'@'%'

I want to dump all data (included triggers and procedures) using the dump user. I call mysqldump in the following way:

mysqldump -u dump -p --routines --triggers --quote-names --opt \
    --add-drop-database --databases myschema > myschema.sql

Everything is OK with the dumped file except for the triggers, they are missing!!

The triggers are dumped correctly if I try mysqldump with root MySQL user:

mysqldump -u root -p --routines --triggers --quote-names --opt \
    --add-drop-database --databases myschema > myschema.sql

So, I guess it is a perms issue... what are the extra grants my dump MySQL user needs for doing the full dump correctly?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

仅此而已 2024-12-30 08:09:05

假设完全转储还意味着 VIEW 和 EVENT ，您将需要：

GRANT USAGE ON *.* TO 'dump'@'%' IDENTIFIED BY ...;
GRANT SELECT, LOCK TABLES ON `mysql`.* TO 'dump'@'%';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON `myschema`.* TO 'dump'@'%';

并且如果您有执行函数的 VIEW ，那么不幸的是你还需要EXECUTE。

我自己的问题是：如果我只想进行无数据转储，为什么需要 SELECT ？

Assuming by full dump you also mean the VIEWs and the EVENTs, you would need:

GRANT USAGE ON *.* TO 'dump'@'%' IDENTIFIED BY ...;
GRANT SELECT, LOCK TABLES ON `mysql`.* TO 'dump'@'%';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON `myschema`.* TO 'dump'@'%';

and if you have VIEWs that execute a function, then unfortunately you also need EXECUTE.

My own problem is: why do I need SELECT if I only want to make a no-data dump?

回复收藏 0 原文

塔塔猫 2024-12-30 08:09:05

我找到了我需要的额外补助！！

  GRANT TRIGGER ON `myschema`.* TO 'dump'@'%'

这里有官方文档的参考：http://dev.mysql。 com/doc/refman/5.5/en/privileges-provided.html#priv_trigger

TRIGGER 权限启用触发器操作。您必须拥有表的此权限才能为该表创建、删除或执行触发器。

I found the extra GRANT I needed!!

  GRANT TRIGGER ON `myschema`.* TO 'dump'@'%'

Here you have the reference on the official doc: http://dev.mysql.com/doc/refman/5.5/en/privileges-provided.html#priv_trigger

The TRIGGER privilege enables trigger operations. You must have this privilege for a table to create, drop, or execute triggers for that table.

回复收藏 0 原文

探春 2024-12-30 08:09:05

我发现，有时如果 VIEW DEFINER 用户不存在，转储会失败。

按照此处所述进行更改

回复收藏 0 原文

椒妓 2024-12-30 08:09:05

除了 Jannes 答案之外，当使用带有 --tab 选项的 mysqldump 时（产生一个 tab-每个转储表的分隔文本文件），您的 MySQL 用户还必须被授予 FILE 权限：

GRANT FILE ON *.* TO 'dump'@'%';

官方文档参考： https://dev.mysql.com/ doc/refman/5.7/en/mysqldump.html#option_mysqldump_tab

本节提到：

仅当 mysqldump 在同一台计算机上运行时才应使用此选项
机器作为 mysqld 服务器。因为服务器会创建*.txt文件
在您指定的目录中，该目录必须可由
您使用的服务器和 MySQL 帐户必须具有 FILE
特权。因为 mysqldump 在同一目录中创建 *.sql，所以
您的系统登录帐户必须可写。

In addition to Jannes answer, when using mysqldump with --tab option (produces a tab-separated text file for each dumped table), your MySQL user must be granted the FILE privilege as well:

GRANT FILE ON *.* TO 'dump'@'%';

Official docs reference: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_tab

Mentioned in this section:

This option should be used only when mysqldump is run on the same
machine as the mysqld server. Because the server creates *.txt files
in the directory that you specify, the directory must be writable by
the server and the MySQL account that you use must have the FILE
privilege. Because mysqldump creates *.sql in the same directory, it
must be writable by your system login account.

回复收藏 0 原文

~没有更多了~