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

Question

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?

Answer 1

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?