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?**

I found the extra GRANT I needed!!

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

Here you have the reference on the official doc:

[To see links please register here]

> 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 `VIEW`s and the `EVENT`s, 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 `VIEW`s 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][1]


[1]:

[To see links please register here]

In addition to [Jannes](

[To see links please register here]

) 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:
<!-- language: lang-sql -->

GRANT FILE ON *.* TO 'dump'@'%';
Official docs reference:

[To see links please register here]

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.