MySQL mysqldump 转储完整模式所需的最小 GRANTs ？（缺少触发器！！）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8658996/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Minimum GRANTs needed by mysqldump for dumping a full schema? (TRIGGERs are missing!!)
提问by Emilio Nicolás
I have a MySQL user called dumpwith the following perms:
我有一个名为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'@'%'
I want to dump all data (included triggers and procedures) using the dumpuser. I call mysqldump in the following way:
我想使用转储用户转储所有数据（包括触发器和过程）。我通过以下方式调用 mysqldump：
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 rootMySQL user:
如果我使用rootMySQL 用户尝试 mysqldump，触发器将被正确转储：
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 dumpMySQL user needs for doing the full dump correctly?
所以，我想这是一个 perms 问题......我的转储MySQL 用户需要哪些额外的授权才能正确执行完整转储？
回答by Jannes
Assuming by full dump you also mean the VIEWs and the EVENTs, you would need:
假设完全转储也指VIEWs 和EVENTs，则需要：
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.
如果你有VIEWs 来执行一个函数，那么不幸的是你还需要EXECUTE.
My own problem is: why do I need SELECTif I only want to make a no-data dump?
我自己的问题是：SELECT如果我只想进行无数据转储，为什么我需要？
回答by Emilio Nicolás
I found the extra GRANT I needed!!
我找到了我需要的额外 GRANT ！！
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
这里你有官方文档的参考：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.
TRIGGER 权限启用触发器操作。您必须拥有该表的此权限才能为该表创建、删除或执行触发器。
回答by BaZZiliO
回答by Rotem Ad
In addition to Jannesanswer, when using mysqldump with --taboption (produces a tab-separated text file for each dumped table), your MySQL user must be grantedthe FILEprivilege as well:
除了雅尼的答案，使用mysqldump用时--tab选项（产生一个制表符分隔文本文件，每个转储的表），您的MySQL用户必须被授予的FILE权限，以及：
GRANT FILE ON *.* TO 'dump'@'%';
Official docs reference: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_tab
官方文档参考：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.
仅当 mysqldump 与 mysqld 服务器在同一台机器上运行时，才应使用此选项。由于服务器在您指定的目录中创建 *.txt 文件，因此该目录必须可由服务器写入并且您使用的 MySQL 帐户必须具有 FILE 权限。因为mysqldump在同一个目录下创建了*.sql，所以必须是你的系统登录账号可写的。
