0

This question may have been asked many times but I can't find an answer that solves my problem. I want to create a user that can connect to MySQL (5.5.35) on my box from any computer and have all privileges on the schema except GRANT.

So I started with:

$ mysql -u root -p

and then

create user 'dbuser'@'%' identified by 'password';
grant usage on *.* TO 'dbuser'@'%';
grant all on testdb to 'dbuser'@'%';
grant all on testdb.* to 'dbuser'@'%';
flush privileges;
quit

But to my surprise when I tried to connect to the database with:

$ mysql -u dbuser -p testdb

I get the following error message:

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

and I did triple check the password. What am I doing wrong?

Cheers, Johan

user3799605
  • 1
  • 1
  • 1
    Whats the output of `select user, host from mysql.user;`? If you have multiple users with the same user name it can cause this problem. – wils484 Jul 02 '14 at 23:34
  • The output is: mysql> select user, host from mysql.user; +------------------+-----------+ | user | host | +------------------+-----------+ | dbuser | % | | root | 127.0.0.1 | | root | ::1 | | | lion | | root | lion | | | localhost | | debian-sys-maint | localhost | | root | localhost | +------------------+-----------+ 8 rows in set (0.00 sec) – user3799605 Jul 02 '14 at 23:38

1 Answers1

0

The clue here is the @localhost in the error message.

excerpt from MySQL documentation:

http://dev.mysql.com/doc/refman/5.5/en/connecting.html

On Unix, MySQL programs treat the host name localhost specially, in a way that is likely different from what you expect compared to other network-based programs. For connections to localhost, MySQL programs attempt to connect to the local server by using a Unix socket file. This occurs even if a --port or -P option is given to specify a port number. To ensure that the client makes a TCP/IP connection to the local server, use --host or -h to specify a host name value of 127.0.0.1, or the IP address or name of the local server. You can also specify the connection protocol explicitly, even for localhost, by using the --protocol=TCP option.

The fix is either to create user 'dbuser'@'localhost' and grant privileges,

or to force connection through TCP/IP stack, e.g:

mysql -h 127.0.0.1 -u dbuser -p testdb
      ^^^^^^^^^^^^
spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • Your suggestion will not work for me as I want to be able to connect to the database from any computer. I think I actually found a solution: http://stackoverflow.com/questions/10299148/mysql-error-1045-28000-access-denied-for-user-billlocalhost-using-passw?rq=1. I too had anonymous users so after I deleted them everything works. – user3799605 Jul 03 '14 at 00:12