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?

score 118 · Accepted Answer · edited Jul 27 '15 at 16:37

118

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?

edited Jul 27 '15 at 16:37

ChronoTrigger

8,459
1
36
57

answered Aug 21 '13 at 08:22

Jannes

1,784
1
17
20

2

For 5.5 and above, you actually don't need TRIGGER privilege to dump trigger code. – bluecollarcoder Dec 17 '13 at 23:57
1

I'm using 5.5 and needed the TRIGGER privilege to dump the trigger code. – heuri Jul 21 '14 at 11:35
In my case it appears I also need `LOCK TABLES` on the database I am trying to dump itself ... using `5.5.49-MariaDB` – 0xC0000022L Sep 15 '16 at 10:15
2

Regarding @0xC0000022L's comment, locking tables or not is optional for mysqldump, depending on your mysqldump parameters (relevant parameters are `--opt`, `--lock-tables`, `--lock-all-tables`, `--single-transaction` and the respective `--skip-*` variants. – Torque Aug 20 '19 at 12:51

score 8 · Answer 2 · edited Jul 24 '13 at 23:58

8

I found the extra GRANT I needed!!

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

Here you have the reference on the official doc: http://dev.mysql.com/doc/refman/5.5/en/privileges-provided.html#priv_trigger

The TRIGGER privilege enables trigger operations. You must have this privilege for a table to create, drop, or execute triggers for that table.

edited Jul 24 '13 at 23:58

redochka

12,345
14
66
79

answered Dec 29 '11 at 12:15

Emilio Nicolás

2,554
5
22
29

3

The name is actually TRIGGER, not TRIGGERS – thenickdude Jul 06 '12 at 01:10
Even though this may an answer to the author problem, it does not answer the question: "what are the minimum GRANT requirements to do a full dump". For one: EVENT and SHOW VIEW are missing. – Jannes Aug 21 '13 at 07:43
For 5.5 and above, you actually don't need `TRIGGER` privilege to dump trigger code. – bluecollarcoder Dec 17 '13 at 23:56
5

I use 5.5 and I actually needed it – Emilio Nicolás Jan 07 '14 at 12:22

score 1 · Answer 3 · edited May 23 '17 at 11:54

1

I found, that sometime if VIEW DEFINER user does not exist, dump fails.

Change it, as described there

edited May 23 '17 at 11:54

Community

1
1

answered Jul 06 '16 at 08:06

BaZZiliO

232
1
6

Not sure why this was down voted, solved my problem. I had specified definer as `person` instead of `person@localhost`. Thanks! – stianlik Aug 12 '17 at 10:31

score 1 · Answer 4 · answered Feb 07 '19 at 15:56

In addition to Jannes 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:

GRANT FILE ON *.* TO 'dump'@'%';

Official docs reference: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_tab

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.

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

4 Answers4

Linked

Related