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

2 Answers2

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
  • 103,596
  • 14
  • 107
  • 133
  • 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
0

wake up MySQL community server don't connect from any computer because its free

but what about remote access to your computer. with remote turn on pc. now that can be done. but very risky to your system and data. a good go around for that is a file that runs when the pc is turned on. we already got that to.. the trick is to target the file with password so it starts remotle.

kerry
  • 1