I have created a mysql user using following command:
CREATE USER 'backupuser'@'localhost' IDENTIFIED BY 'password';
I have given him permissions using following command:
GRANT EVENT, LOCK TABLES, SELECT, SHOW DATABASES, RELOAD, REPLICATION CLIENT ON *.* TO 'backupuser'@'localhost' IDENTIFIED BY 'password';
But when I try to do database dump using following command it does not work:
sudo mysqldump -ubackupuser -ppassword --all-databases > all_db_backup.sql
It says: mysqldump: Got error: 1045: Access denied for user 'backupuser'@'localhost' (using password: YES) when trying to connect
But if I try to connect without using password then it asks for password and then it works.
I am creating a backup script so I have to pass the password in command. Can you please help how to make my backupuser to connect using mysql dump?
Does your password contain any characters that are special to the shell? Like ; & ! or space, quotes, etc.? You might have to quote the password.
But regardless, I recommend you do NOT put your passwords on the command-line. Anyone who can run ps on your server can therefore see your password in plain text.
Instead, put the user and password in an options file. That is, save a small file with the following content:
[mysqldump]
user = backupuser
password = ...
Of course write your own password where I put "...".
Change the permissions on the file so no one can read it except the user who runs your backups.
Then reference the options file when you run mysqldump:
mysqldump --defaults-file=myopts.cnf --all-databases ...
That way the password is not visible in the server's process list. Also there is no worry about special characters in your password.
P.S.: I'm not sure why you use sudo to run your backup. That should not be necessary.