Minimum GRANTs needed by mysqldump for dumping a full schema? (TRIGGERs are missing!!)
MysqlDatabaseMysqldumpDatabase BackupsSql GrantMysql Problem Overview
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?
Mysql Solutions
Solution 1 - Mysql
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?
Solution 2 - Mysql
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.
Solution 3 - Mysql
I found, that sometime if VIEW DEFINER user does not exist, dump fails.
Change it, as described there
Solution 4 - Mysql
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.