Bug #67084 Triggers not dumped using documented backup grants
Submitted: 4 Oct 2012 16:20 Modified: 12 Oct 2012 20:17
Reporter: Van Stokes Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Backup Severity:S2 (Serious)
Version:5.5.27 OS:Linux (Ubuntu 12.04 x86_64)
Assigned to: CPU Architecture:Any
Tags: mysqldump, trigger

[4 Oct 2012 16:20] Van Stokes
Description:
mysqldump does not dump triggers using recommended backup grants for the backup user.

Administrative users CAN dump and get the triggers.

How to repeat:
GRANT SELECT, SHOW DATABASES, LOCK TABLES, EXECUTE, SHOW VIEW, EVENT ON *.* TO 'backup'@'localhost' IDENTIFIED BY PASSWORD 'blahblah'

$ mysqldump --user=backup --password="blahblah" --result-file=dump.sql --databases mydatabase --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --routines --events --compress --skip-set-charset --quick

A successful dump is performed but WITHOUT triggers. No error messages are provided by mysqldump.

This user DOES successfully dump the database WITH triggers.
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD 'blahblah' WITH GRANT OPTION

Suggested fix:
Identify and document the grant required to dump triggers or correct the server to honor the proper grant.
[12 Oct 2012 18:59] Sveta Smirnova
Thank you for the report.

Please add option --triggers to your command line and inform us if it will dump triggers.
[12 Oct 2012 19:04] Van Stokes
Adding --triggers does NOT cause the triggers to dump when executing mysqldump using database user "backup" however if we use user "root" the triggers do dump.

It appears to be a permission problem as stated before. The "backup" user cannot dump triggers unless it has full grants like "root".
[12 Oct 2012 19:40] Sveta Smirnova
Thank you for the feedback.

You missed TRIGGER privilege: http://dev.mysql.com/doc/refman/5.5/en/privileges-provided.html#priv_trigger This is not a bug.
[12 Oct 2012 20:17] Van Stokes
Thank you. That was not checked because the manual does not specify that it is needed for backing up (or dumping) the database. 

The documentation needs to specify this requirement. It's not stated in the backup documentation.