mysqldump需要最小GRANT来转储完整模式？ （TRIGGERs丢失!!） [英] Minimum GRANTs needed by mysqldump for dumping a full schema? (TRIGGERs are missing!!)

问题描述

我有一个名为 dump 的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'@'%'

我要转储所有数据过程）使用 dump 用户。我以下列方式调用mysqldump：

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!!

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

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?

推荐答案

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

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'@'%';

，如果您有 VIEW 一个函数，那么不幸的是你还需要 EXECUTE 。

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

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

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

这篇关于mysqldump需要最小GRANT来转储完整模式？ （TRIGGERs丢失!!）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！