Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a MySQL server (5.6) running on a virtual Debian box on my local computer. Connecting to MySQL from the localhost command line via the following command worked as expected:

mysql -uroot -ppassword

When originally trying to connect to the virtual machine remotely using MySQL Workbench, I was getting a permission denied message for my root account, which I resolved by commenting out the bind-address=localhost directive in the virtual MySQL's my.cnf file. Once doing this and rebooting, I was able to connect remotely as expected.

Now though, I am having a different issue - when trying to connect from the command line on the virtual box (i.e. localhost) I get the following error:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Connecting via the host name, local loopback IP (both IP4 & IP6) and remotely all work:

mysql -uroot -ppassword -hhostname
mysql -uroot -ppassword -h127.0.0.1

Looking at the user table in the database, there are four user accounts for root:

  • root@localhost
  • root@127.0.0.1
  • root@::1
  • root@%

and they have the correct settings, so everything looks good.

I've run FLUSH PRIVILEGES, rebooted MySQL, but no solution.

EDIT

Output from mysql -uroot -ppassword -hlocalhost -ANe"SELECT USER(),CURRENT_USER();:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Output from mysql -uroot -ppassword -hhostname -ANe"SELECT USER(),CURRENT_USER();:

+----------------+----------------+
| root@hostname | root@% |
+----------------+----------------+

Output from mysql -uroot -ppassword -h127.0.0.1 -ANe"SELECT USER(),CURRENT_USER();:

+----------------+----------------+
| root@localhost | root@127.0.0.1 |
+----------------+----------------+
share|improve this question
    
My (version 5.6) MySQL install has a 'localhost' and '%' root user, but doesn't have a '127.0.0.1' entry in the user table. It seems strange yours is the opposite... Note: turning off skip-name-resolve in my.ini often helps in these cases. (Hmm on another box, I've got all 3: localhost, ::1, and 127.0.0.1) –  ebyrob Jul 24 '13 at 17:25
    
I am running 5.6 as well (added above) and actually have a fourth account with ::1 as well - just omitted that from the original question. Will try skip-name-resolve, but that feels a bit hackish for sure. –  Paul Mennega Jul 24 '13 at 17:46
    
@ebyrob FYI, adding skip-name-resolve did not resolve the issue. –  Paul Mennega Jul 24 '13 at 18:01
    
Having deleted bind-address=localhost, it is only logical that you need to specify the -h parameter. Please explain more what is the problem. –  harrymc Aug 8 '13 at 8:12
    
Please run the following 2 commands: mysql -uroot -ppassword -hhostname -ANe"SELECT USER(),CURRENT_USER();" and mysql -uroot -ppassword -h127.0.0.1 -ANe"SELECT USER(),CURRENT_USER(); and post the output in the question body. –  RolandoMySQLDBA Aug 9 '13 at 15:13
show 2 more comments

migrated from stackoverflow.com Jul 24 '13 at 22:21

This question came from our site for professional and enthusiast programmers.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.