Mysqldump not working with SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER privileges

Question

My system: CentOS 6.5 + MySQL 5.6

I have created a backupuser for mysqldump:

CREATE USER 'usr_backup'@'localhost' IDENTIFIED BY 'XXX';

GRANT USAGE ON *.* TO 'usr_backup'@'localhost' IDENTIFIED BY "XXX";
GRANT SELECT, LOCK TABLES ON `mysql`.* TO 'usr_backup'@'localhost';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON <my schema name>.* TO 'usr_backup'@'localhost' IDENTIFIED BY "XXX";

Mysql dump command:

mysqldump -uusr_backup -pXXX --add-drop-database --add-drop-table --add-drop-trigger --allow-keywords --comments --compact --complete-insert --disable-keys --extended-insert --quick --routines --tz-utc <my schema name> > /usr/share/dump.sql

When I run dump command, I get

mysqldump: Got error: 1045: Access denied for user 'usr_backup'@'localhost' (using password: YES) when trying to connect

I tried removing all options from dump command but no success.

I tried with mysql root user and dump gets created.

What can be the issue with "usr_backup"?

Regards, Suraj

Answer 1

I know this is old, but I stumbled across it when I was having this problem. Here is what was going on with mine. I have a script that runs mysqldump nightly. I just brought a new db online and added the script. The user the script uses had the right permissions, but it was still failing. I temporarily granted dba access to the backup user and it still failed, but with a different error about a user that has permissions to stuff that doesn't exist. Created that user, reset and flushed permissions, then it worked fine. Hopefully this helps.