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
Best Answer
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.