I am troubleshooting a backup script (bash), and I have limited working knowledge of the current configuration since the last 3 predecessors did not leave any documentation.

I do know that my server is running MySQL Server 5.5.37 on a Debian machine. I'm manually testing the script and found mysqldump to be the problem. I realize there are very many posts on this same error message (1, 2, 3, 4), but none of them have worked for me.

Running this command

mysqldump -uroot -p******** --all-databases > backup.all.sql 2> backup.all.err

produces the following error:

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

Here's a summary of what I've tried.

  1. Verifying that root user can log in, and is being logged in correctly (ie. USER(), CURRENT_USER();).

  2. I can log in to mysqlwith root user and supplying the -p flag. Yet if I supply the password as part of the cmd (ie. mysql -uroot -p********) I receive the same error as above.

    ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
  3. Verified that every user has a password, there are no anonymous users, and root is in the users table with the normal hostnames (list below, shortened).

    | user        | host         |
    | root        |    |
    | root        | localhost    |
  4. I can run mysqldump -p --all-databases with limited success but will not work for my use case because I'd like the script to run automatically without the need for user interaction (eg. supplying the password before proceeding with the backup process).

  5. Root user has all privileges on all dbs.

    | Grants for root@localhost                                         |
    | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY   |
    | PASSWORD '*********' WITH GRANT OPTION                            |
  6. Values in /etc/mysql/my.conf all seem to be pretty standard and are probably default. There are no additional .conf files.

  7. Tried forcing a connection to localhost (which also forces use of the TCP protocol)

    mysql -h localhost -uroot -p******** 
  8. Reviewed and tested each of the Causes of Access-Denied Errors from the MySQL 5.5 docs.

  9. Reset root password (not OS root user) using dpkg-reconfigure mysql-server-5.5

  10. Flushed privileges, just in case, using mysqladmin flush-privileges

  11. Ran mysqlcheck --all-databases in case mysql was corrupted. No errors were found.

Is there any reason why 'root'@'localhost' would still be denied when supplying the password for both mysql and mysqldump? Could both modules be ignoring the password I'm supplying and, if so, why? Lastly, is there anything else that I can try in order to allow me to log in by providing a password as part of the cmd?

  • if you add the user and password to a [client] section in ~/.my.cnf can you login without specifying user/password on the cli? – eroomydna Jan 27 '15 at 22:12
  • Yes, actually. Is it wise to put the root user's password in the my.conf? I mean, of course I'm doing the same thing by putting it in a shell script but at least I can manage permissions on that file. – filoxo Jan 27 '15 at 22:15
  • 1
    Why use root? Create a backup user. – eroomydna Jan 27 '15 at 22:16
  • Also not /etc/my.cnf use /home/{user}/.my.cnf and manage the permissions on that file. – eroomydna Jan 27 '15 at 22:16
  • 1
    I think the 'close as duplicate' votes were preemptive; I didn't even know what I was really asking for, and anyone else in my situation might not be able to understand why this question was closed and linked to another (especially if they don't read all comments which are meant to be "temporary 'Post-It' notes"). @Michael-sqlbot you were right. Thanks for your help! – filoxo Feb 2 '15 at 18:54

Browse other questions tagged or ask your own question.