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

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

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

    
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

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.