Minimum GRANT required by mysqldump to reset the full schema? (TRIGGER is missing !!)

Question

I have a MySQL user called a 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 (triggers and procedures included) using a dump user. I call mysqldump as follows:

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

Everything is fine with the uploaded file, with the exception of triggers , they are missing !!

Triggers are reset correctly if I try mysqldump with MySQL root user:

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

So, I think this is a problem with perms ... what are the additional grants that I need MySQL MySQL to do a full dump correctly?

Answer 1

Assuming a full dump also means VIEW and EVENT s, you'll 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 a VIEW that performs a function, then, unfortunately, you will also need EXECUTE .

My own problem: why do I need SELECT if I only want to dump without data?

Answer 2

I found the extra GRANT that I need!

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

Here you have a link to the official document: http://dev.mysql.com/doc/refman/5.5/en/privileges-provided.html#priv_trigger

The TRIGGER flag allows triggering trigger operations. You must have this privilege for the table to create, delete, or trigger triggers for this table.

Answer 3

I found that sometime, if the VIEW DEFINER user does not exist, it crashes.

Change it as described there