[Solved-3 Solutions] MySQL ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)




Error Description:

  • If we connect to the Amazon EC2 instance. We can login with MySQL root with this command:
mysql -u root -p
click below button to copy the code. By - sql tutorial - team
  • Then when we create a new user bill with host %
CREATE USER 'bill'@'%' IDENTIFIED BY 'passpass';
click below button to copy the code. By - sql tutorial - team
  • Granted all the privileges to user bill:
grant all privileges on *.* to 'bill'@'%' with grant option;
click below button to copy the code. By - sql tutorial - team
  • Then if we exit from root user and try to login with bill:
mysql -u bill -p
click below button to copy the code. By - sql tutorial - team
  • we enter the correct password and get this error:

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

Solution 1:

  • You probably have an anonymous user ''@'localhost' or ''@'127.0.0.1'. As per the manual: When multiple matches are possible, the server must determine which of them to use. It resolves this issue as follows: (...)
    • When a client attempts to connect, the server looks through the rows [of table mysql.user]in sorted order.
    • The server uses the first row that matches the client host name and user name.
  • (...) The server uses sorting rules that order rows with the most-specific Host values first. Literal host names [such as 'localhost'] and IP addresses are the most specific.
  • Hence, such an anonymous user would "mask" any other user like '[any_username]'@'%' when connecting from localhost.
  • 'bill'@'localhost' does match 'bill'@'%', but would match (e.g.) ''@'localhost'beforehands.
  • The recommended solution is to drop this anonymous user (this is usually a good thing to do anyways).

Solution 2:

Try:

~$ mysql -u root -p
Enter Password:

mysql> grant all privileges on *.* to [email protected] identified by 'pass' with grant option;
click below button to copy the code. By - sql tutorial - team

Solution 3:

  • When you ran
mysql -u bill -p
click below button to copy the code. By - sql tutorial - team
  • and got this error
ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)
click below button to copy the code. By - sql tutorial - team
CREATE USER [email protected] IDENTIFIED BY 'passpass';
grant all privileges on *.* to [email protected] with grant option;
click below button to copy the code. By - sql tutorial - team
  • If you want to connect remotely, you must specify either the DNS name, the public IP, or 127.0.0.1 using TCP/IP:
mysql -u bill -p [email protected]
mysql -u bill -p -h10.1.2.30
mysql -u bill -p -h127.0.0.1 --protocol=TCP
click below button to copy the code. By - sql tutorial - team
  • Once you login, please run this
SELECT USER(),CURRENT_USER();
click below button to copy the code. By - sql tutorial - team
  • USER() reports how you attempted to authenticate in MySQL
  • CURRENT_USER() reports how you were allowed to authenticate in MySQL from the mysql.user table
  • This will give you a better view of how and why you were allowed to login to mysql. Why is this view important to know? It has to do with the user authentication ordering protocol.
  • Here is an example:
mysql> select user,host from mysql.user;

user host
lwdba %
mywife %
lwdba 127.0.0.1
root 127.0.0.1
lwdba localhost
root localhost
vanilla localhost
7 rows in set (0.00 sec) mysql> grant all on *.* to [email protected]'%'; Query OK, 0 rows affected (0.02 sec) mysql> select user,host from mysql.user;
user host
lwdba %
mywife %
x %
lwdba 127.0.0.1
root 127.0.0.1
lwdba localhost
root localhost
vanilla localhost
8 rows in set (0.00 sec) mysql> update mysql.user set user='' where user='x'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.01 sec) mysql> select user,host from mysql.user;
user host
%
lwdba %
mywife %
lwdba 127.0.0.1
root 127.0.0.1
lwdba localhost
root localhost
vanilla localhost
8 rows in set (0.00 sec) mysql>
click below button to copy the code. By - sql tutorial - team
  • OK watch me login as anonymous user:
C:\MySQL_5.5.12>mysql -urol -Dtest -h127.0.0.1 --protocol=TCP
click below button to copy the code. By - sql tutorial - team
  • Welcome to the MySQL monitor. Commands end with ; or \g.
  • Your MySQL connection id is 12
  • Server version: 5.5.12-log MySQL Community Server (GPL)
  • Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
  • Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select user(),current_user();
+--------------------------+------------------------------+
| user()                   | current_user()         |
+----------------------+----------------------------------+
| [email protected]     | @%                            |
+--------------------------+------------------------------+
1 row in set (0.00 sec)

mysql>

click below button to copy the code. By - sql tutorial - team

Related Searches to MySQL ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)

Adblocker detected! Please consider reading this notice.

We've detected that you are using AdBlock Plus or some other adblocking software which is preventing the page from fully loading.

We don't have any banner, Flash, animation, obnoxious sound, or popup ad. We do not implement these annoying types of ads!

We need money to operate the site, and almost all of it comes from our online advertising.

Please add wikitechy.com to your ad blocking whitelist or disable your adblocking software.

×