Mysqldump 转储完整模式所需的最小 GRANT? (缺少触发器! !)

我有一个名为 扔掉的 MySQL 用户，他烫了以下发型:

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

我想使用 扔掉用户转储所有数据(包括触发器和过程)。我用以下方式调用 mysqldump:

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

除了 引爆器不见了之外，转储文件一切正常! ！

如果我使用 根 MySQL 用户尝试 mysqldump，触发器会被正确地转储:

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

所以，我想这是一个烫发的问题... 什么是 额外授予我的 < em > dump MySQL 用户正确执行完整转储所需的权限？

73652 次浏览
小开

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.

小开
最佳答案

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?

小开

I found, that sometime if VIEW DEFINER user does not exist, dump fails.

Change it, as described there

小开

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.


提交答案