CODEWITHSUNDEEP
mysqlbackup
Future King
Future King

Reputation: 3849

mysqldump: MySql backup user is not able to connect using password

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?

Upvotes: 1

Views: 1900

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562348

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.

Upvotes: 1

Related Questions