Minimum GRANTs needed by mysqldump for dumping a full schema? (TRIGGERs are missing!!)

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?

 Answers

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