mardi 27 janvier 2015

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


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 | 127.0.0.1 |
    | 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?





Aucun commentaire:

Enregistrer un commentaire