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

Question

I'm connected to my Amazon EC2 instance. I can login with MySQL root with this command:

mysql -u root -p

Then I created a new user bill with host %

CREATE USER 'bill'@'%' IDENTIFIED BY 'passpass';

Granted all the privileges to user bill:

grant all privileges on *.* to 'bill'@'%' with grant option;

Then I exit from root user and try to login with bill:

mysql -u bill -p

entered the correct password and got this error:

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

Okay, I tried this without any success. Any other suggestion please. — Ali
– Ali, Commented Apr 25, 2012 at 4:35
What version of the server are you running? I've seen 5.1 behave oddly about this. — Poodlehat
– Poodlehat, Commented Jun 27, 2012 at 14:27
This happened to me while installing Magento and I made a much sillier mistake. Putting 'mysql -u magento -p magento' was prompting me for a password and instead of the default password I was putting the root password in. — AmirHd
– AmirHd, Commented Oct 29, 2013 at 3:26
make sure to add with grant option to the end of the grant line. Several of the answers below have this buried in their lengthy answers. This is the only thing wrong with what you were doing. Just wasted some time on this myself. — Jilles van Gurp
– Jilles van Gurp, Commented Nov 11, 2013 at 14:49

desertnaut · Accepted Answer · 2025-01-09 00:30:48Z

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.

Therefore 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).

Below edits are mostly irrelevant to the main question. These are only meant to answer some questions raised in other comments within this thread.

Edit 1

Authenticating as 'bill'@'%' through a socket.

root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -ppass --socket=/tmp/mysql-5.5.sock Welcome to the MySQL monitor (...) mysql> SELECT user, host FROM mysql.user; +------+-----------+ | user | host | +------+-----------+ | bill | % | | root | 127.0.0.1 | | root | ::1 | | root | localhost | +------+-----------+ 4 rows in set (0.00 sec) mysql> SELECT USER(), CURRENT_USER(); +----------------+----------------+ | USER() | CURRENT_USER() | +----------------+----------------+ | bill@localhost | bill@% | +----------------+----------------+ 1 row in set (0.02 sec) mysql> SHOW VARIABLES LIKE 'skip_networking'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | skip_networking | ON | +-----------------+-------+ 1 row in set (0.00 sec)

Edit 2

Exact same setup, except I re-activated networking, and I now create an anonymous user ''@'localhost'.

root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql Welcome to the MySQL monitor (...) mysql> CREATE USER ''@'localhost' IDENTIFIED BY 'anotherpass'; Query OK, 0 rows affected (0.00 sec) mysql> Bye root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -ppass \ --socket=/tmp/mysql-5.5.sock ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES) root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -ppass \ -h127.0.0.1 --protocol=TCP ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES) root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -ppass \ -hlocalhost --protocol=TCP ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)

Edit 3

Same situation as in edit 2, now providing the anonymous user's password.

root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -panotherpass -hlocalhost Welcome to the MySQL monitor (...) mysql> SELECT USER(), CURRENT_USER(); +----------------+----------------+ | USER() | CURRENT_USER() | +----------------+----------------+ | bill@localhost | @localhost | +----------------+----------------+ 1 row in set (0.01 sec)

Conclusion 1, from edit 1: One can authenticate as 'bill'@'%'through a socket.

Conclusion 2, from edit 2: Whether one connects through TCP or through a socket has no impact on the authentication process (except one cannot connect as anyone else but 'something'@'localhost' through a socket, obviously).

Conclusion 3, from edit 3: Although I specified -ubill, I have been granted access as an anonymous user. This is because of the "sorting rules" advised above. Notice that in most default installations, a no-password, anonymous user exists (and should be secured/removed).

For anyone curious as to why 'bill'@'localhost' matches ''@'localhost' like I was, an empty string effectively acts a wild card in MySQL's authentication algorithm.
A short query RENAME USER ''@'localhost' TO ''@'%'; made my day! Thank you!
@Sanja Be extra careful with this workaround. You may be allowing anonymous access to your database from any location. In case of doubt, I would rather delete the user.
@RandomSeed Thank you for this comment! Probably I should just delete anonymous user. P.S. I found dev.mysql.com/doc/refman/5.1/en/default-privileges.html that says that these users surely can be deleted: DROP USER ''@'localhost';. They are not needed for some special purpose.
I dropped the anonymous user, but things still did not work. Then I found that I needed to issue also "FLUSH PRIVILEGES". It would be helpful to mention that also.

bstpierre · Accepted Answer · 2012-10-25 20:39:16Z

167

Try:

~$ mysql -u root -p Enter Password: mysql> grant all privileges on *.* to bill@localhost identified by 'pass' with grant option;

edited Oct 25, 2012 at 20:39

bstpierre

31.5k16 gold badges74 silver badges105 bronze badges

answered Oct 25, 2012 at 20:17

Edgar Aviles

1,6951 gold badge10 silver badges2 bronze badges

7 Comments

Adonis K. Kakoulidis Over a year ago

This is rather dangerous, if someone hacks your bill@localhost mysql account, he will have infinite access to all the databases of your mysql server.

bendecko Over a year ago

Whehey. I had to put quote around my user 'myusername'@'myhost.static.myip.com' then it worked.

Csaba Toth Over a year ago

That works for me but I'm afraid I gave too much privileges for the user

jwenting Over a year ago

@CsabaToth you did, reduce the privileges again until your user has what he needs and no more.

Sean Munson Over a year ago

... and what do you do if this gives you "Access denied for user 'root'@'localhost' (using password: YES)" then ?

|

Community · Accepted Answer · 2017-04-13 12:42:36Z

When you ran

mysql -u bill -p

and got this error

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

mysqld is expecting you to connect as bill@localhost

Try creating bill@localhost

CREATE USER bill@localhost IDENTIFIED BY 'passpass'; grant all privileges on *.* to bill@localhost with grant option;

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

Once you login, please run this

SELECT USER(),CURRENT_USER();

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: I will create an anonymous user on my desktop MySQL

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 x@'%'; 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>

OK watch me login as anonymous user:

C:\MySQL_5.5.12>mysql -urol -Dtest -h127.0.0.1 --protocol=TCP 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. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select user(),current_user(); +---------------+----------------+ | user() | current_user() | +---------------+----------------+ | rol@localhost | @% | +---------------+----------------+ 1 row in set (0.00 sec) mysql>

Authentication ordering is very strict. It checks from the most specific to the least. I wrote about this authentiation style in the DBA StackExchange.

Don't forget to explicitly call for TCP as the protocol for mysql client when necessary.

@Yak the sort order is not based solely on mysql.user's user column. MySQL does not do any character matching per se. I wrote about the user authentication ordering protocol in the DBA StackExchange : dba.stackexchange.com/a/10897/877
@YaK This is why I specifically mentioned SELECT USER(),CURRENT_USER();. You almost never see anonymous users appear from these two functions except in really poor setups.
I never assumed the sort order is based solely on mysql.user. In fact, if you read my answer again, you will see that I said (actually, the manual says) the sort order is based on the host column first. You wrote a lot about how to check your current credentials, but I see little information as to why 'bill'@'localhost' cannot login as 'bill'@'%', which is today's question AFAIK. The OP probably has a poor setup, this is why he gets these errors.
Note that in default "binary" installations, an anonymous user ''@'localhost' exists before you run the "Post-installation" procedure. dev.mysql.com/doc/refman/5.5/en/default-privileges.html

bad_coder · Accepted Answer · 2025-01-09 00:19:10Z

I tried all of these other answers and ran many different versions of mysql -u root -p but never just ran.

mysql -u root -p

And just pressing ENTER for the password it worked.

simhumileco · Accepted Answer · 2019-11-16 05:53:38Z

I had a somewhat similar problem - on my first attempt to enter MySQL, as root, it told me access denied. Turns out I forgot to use the sudo...

So, if you fail on root first attempt, try:

sudo mysql -u root -p

and then enter your password, this should work.

nos · Accepted Answer · 2012-06-26 17:39:13Z

When you type mysql -u root -p , you're connecting to the mysql server over a local unix socket.

However the grant you gave, 'bill'@'%' only matches TCP/IP connections curiously enough.

If you want to grant access to the local unix socket, you need to grant privileges to 'bill'@'localhost' , which curiously enough is not the same as 'bill'@'127.0.0.1'

You could also connect using TCP/IP with the mysql command line client, as to match the privileges you already granted, e.g. run mysql -u root -p -h 192.168.1.123 or whichever local IP address your box have.

"'bill'@'%' only matches TCP/IP connections" This is wrong. Try it on a clean (virgin, out-of-the-box) instance with skip-networking
@YaK I cannot get 'user'@'%' to function in such a setup, what do you mean was supposed to happen ?
I could connect as 'bill'@'%' on a v5.0 without networking (hence, through a socket). What version are you using? I will try it on a v5.5.
Please see my updated answer. I was able to login as 'bill'@'%' through a socket on a v5.5.

mstram · Accepted Answer · 2014-08-20 18:19:28Z

23

A related problem in my case was trying to connect using :

mysql -u mike -p mypass

Whitespace IS apparently allowed between the -u #uname# but NOT between the -p and #password#

Therefore needed:

mysql -u mike -pmypass

Otherwise with white-space between -p mypass mysql takes 'mypass' as the db name

answered Aug 20, 2014 at 18:19

mstram

5921 gold badge6 silver badges13 bronze badges

2 Comments

user3608589 Over a year ago

or: mysql -u usrname -p -- this stops anyone from seeing the password as it will drop a new line and ask for the password without displaying it

dimpiax Over a year ago

this solution is comfortable, nevertheless it's insecure to type password obviously, but no need type grant privileges and define other stuff. So if you mac book is secured – this approach very comfortable, even more I used it for importing data to aws from docker mysql driver.

desertnaut · Accepted Answer · 2025-01-08 23:27:47Z

If you forget your password or you want to modify your password.You can follow these steps :

1 :stop your mysql

[root@maomao ~]# service mysqld stop Stopping MySQL: [ OK ]

2 :use “--skip-grant-tables” to restart mysql

>> [root@mcy400 ~]# mysqld_safe --skip-grant-tables <br> [root@cy400 ~]# Starting mysqld daemon with databases from /var/lib/mysql

3 : open a new window and input mysql -u root

>> [root@cy400 ~]# mysql -u root <br> Welcome to the MySQL monitor. Commands end with ; or \g.

4 : change the user database

>> mysql> use mysql

Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed

5 : modify your password your new password should be input in "（）"

>> mysql> update user set password=password('root123') where user='root'; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0

6 : flush

>> mysql> flush privileges;

7: quit

>> mysql> quit Bye

8: restart mysql

>> [root@cy400 ~]# service mysqld restart; <br> Stopping MySQL: [ OK ] Starting MySQL: [ OK ]

Bingo！ You can connect your database with your username and new password:

[root@cy400 ~]# mysql -u root -p <br> Enter password: admin123 <br> Welcome to the MySQL monitor. Commands end with ; or \g. <br> Your MySQL connection id is 2 <br> Server version: 5.0.77 Source distribution <br> Type 'help;' or '\h' for help. Type '\c' to clear the buffer. <br> mysql> quit Bye

This helped me, but on mac if you install with homebrew it's mysql.server stop. In my case, I couldn't update the user column because there isn't one; also couldn't create one because it's in safe mode. For what I'm doing right now I don't care, but I really appreciate this format answer with exact inputs & outputs shown. thanks!

desertnaut · Accepted Answer · 2025-01-08 23:21:09Z

Save yourself of a MAJOR headache... Your problem might be that you are missing the quotes around the password. At least that was my case that detoured me for 3 hours.

[client] user = myusername password = "mypassword" # <----------------------- VERY IMPORTANT (quotes) host = localhost

http://dev.mysql.com/doc/refman/5.7/en/option-files.html

Search for "Here is a typical user option file:" and see the example they state in there.

Rohit Gupta · Accepted Answer · 2015-11-07 20:00:47Z

The solution is to delete the anonymous (Any) user!

I also faced the same issue on a server setup by someone else. I normally don't choose to create an anonymous user upon installing MySQL, so hadn't noticed this. Initially I logged in as "root" user and created a couple of "normal" users (aka users with privileges only on dbs with their username as prefix), then logged out, then went on to verify the first normal user. I couldn't log in. Neither via phpMyAdmin, nor via shell. Turns out, the culprit is this "Any" user.

Abhijeet Kamble · Accepted Answer · 2014-09-18 14:33:59Z

The best solution i found for myself is.

my user is sonar and whenever i am trying to connect to my database from external or other machine i am getting error as

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

Also as i am trying this from another machine and through Jenkins job my URL for accessing is

alm-lt-test.xyz.com

if you want to connect remotely you can specify it with different ways as follows:

mysql -u sonar -p -halm-lt-test.xyz.com mysql -u sonar -p -h101.33.65.94 mysql -u sonar -p -h127.0.0.1 --protocol=TCP mysql -u sonar -p -h172.27.59.54 --protocol=TCP

To access this with URL you just have to execute the following query.

GRANT ALL ON sonar.* TO 'sonar'@'localhost' IDENTIFIED BY 'sonar'; GRANT ALL ON sonar.* TO 'sonar'@'alm-lt-test.xyz.com' IDENTIFIED BY 'sonar'; GRANT ALL ON sonar.* TO 'sonar'@'127.0.0.1' IDENTIFIED BY 'sonar'; GRANT ALL ON sonar.* TO 'sonar'@'172.27.59.54' IDENTIFIED BY 'sonar';

Gerd · Accepted Answer · 2015-12-01 17:32:03Z

It's a difference between:

CREATE USER 'bill'@'%' IDENTIFIED BY 'passpass';

and

CREATE USER 'bill'@'localhost' IDENTIFIED BY 'passpass';

Check it:

mysql> select user,host from mysql.user; +---------------+----------------------------+ | user | host | +---------------+----------------------------+ | bill | % | <=== created by first | root | 127.0.0.1 | | root | ::1 | | root | localhost | | bill | localhost | <=== created by second +---------------+----------------------------+

The command

mysql -u bill -p

access implicit to 'bill'@'localhost' and NOT to 'bill'@'%'.

There are no permissions for 'bill'@'localhost'

you get the error:

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

solving the problem:

CREATE USER 'bill'@'localhost' IDENTIFIED BY 'passpass'; grant all privileges on . to 'bill'@'localhost' with grant option;

The grant statement returned an error. I needed to specify a database like so: grant all privileges on newdb.* to ....
grant all privileges on *.* to 'bill'@'localhost' with grant option; Correction to query

desertnaut · Accepted Answer · 2025-01-09 00:27:38Z

I encountered the same error and searched all over for any anonymous users...and there weren't any. The problem ended up being that the user account was set to "Require SSL" - which I found in PHPMyAdmin by going to User Accounts and clicking on Edit Privileges for the user. As soon as I unchecked this option, everything worked as expected!

Pranit More · Accepted Answer · 2016-07-08 06:44:42Z

This also happens when your password contains some special characters like @,$,etc. To avoid this situation you can wrap password in single quotes:

$ mysql -usomeuser -p's0mep@$$w0Rd'

Or instead don't use password while entering. Leave it blank and then type it when terminal asks. This is the recommended way.

$ mysql -usomeuser -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 191 Server version: 5.5.46-0ubuntu0.14.04.2 (Ubuntu) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>

This can indeed be an issue (as it was for me). All my passwords for any of my accounts are generated using pwgen. Today I generated a new one for a MySQL database and its user. Unfortunately the password contained a backslash "\" which I didn't identify as the errors origin (I didn't even think of that). So, I searched for hours a solution. After setting the password to "123" in despair the login finally worked. … Users should be aware of that some special chars may cause problems since MySQL doesn't show any warnings on using passwords such as "daiy4ha4in7chooshuiphie\Th*aew", for example.

cokeman19 · Accepted Answer · 2019-12-14 04:03:46Z

Update: On v8.0.15 (maybe this version) the PASSWORD() function does not work.

You have to:

Make sure you have Stopped MySQL first.
Run the server in safe mode with privilege bypass: sudo mysqld_safe --skip-grant-tables
Login: mysql -u root
mysql> UPDATE mysql.user SET authentication_string=null WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit;
Login again: mysql -u root
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpasswd';

user8075070 · Accepted Answer · 2020-12-10 21:28:32Z

For me root had a default password

i changed the password using ALTER USER 'root'@'localhost' IDENTIFIED BY 'new Password'; and it worked

desertnaut · Accepted Answer · 2025-01-08 23:21:57Z

For me, this problem was caused by a new feature of MySQL 5.7.2: user entries are ignored if their plugin field is empty.

Set it to e.g. mysql_native_password to reenable them:

UPDATE user SET plugin='mysql_native_password' WHERE user='foo'; FLUSH PRIVILEGES;

See the release notes for MySQL 5.7.2, under «Authentication Notes».

For some reason (maybe because my pre-4.1 password hashes were removed), the mysql_upgrade script didn't set a default plugin value.

I found out by noticing the following warning message in /var/log/mysql/error.log:

[Warning] User entry 'foo'@'%' has an empty plugin value. The user will be ignored and no one can login with this user anymore.

desertnaut · Accepted Answer · 2025-01-09 00:28:37Z

5

Probably it is my.cnf file inside mysql installation directory that is the culprit. Comment out this line and the problem might be resolved:

bind-address = 127.0.0.1

edited Jan 9 at 0:28

desertnaut

60.8k32 gold badges155 silver badges183 bronze badges

answered Apr 25, 2012 at 9:37

Ali

8,14012 gold badges44 silver badges66 bronze badges

2 Comments

Ali Over a year ago

For more details, you can view this as well wiki.bitnami.org/Components/MySQL

Pelle Over a year ago

I am quite sure that this is not the issue. If it would be, MySQL refuses connections from any host other than 127.0.0.1, and you will not get a SQL 'Access Denied' error.

Erik · Accepted Answer · 2015-10-06 13:28:21Z

Just wanted to let you know a unusual circumstance I received the same error. Perhaps this helps someone in the future.

I had developed a few base views, created at the development site and transferred them to the production-site. Later that week I changed a PHP script and all of a sudden errors came up that Access was denied for user 'local-web-user'@'localhost'. The datasource object had not changed, so I concentrated on the database user in MySQL, worrying in the meantime someone hacked my website. Luckily the rest of the site seemed unharmed.

It later turned out that the views were the culprit(s). Our object transfers are done using another (and remote: admin@ip-address) user than the local website user. So the views were created with 'admin'@'ip-address' as the definer. The view creation SECURITY default is

SQL SECURITY DEFINER

When local-web-user tries to use the view it stumbles on the lacking privileges of the definer to use the tables. Once security was changed to:

SQL SECURITY INVOKER

the issue was resolved. The actual problem was completely different than anticipated based on the error message.

This ended up being the source of my problem as well - a view with a missing "definer". One quick way to find out if this is what's happening to you is to try querying the same table or view as root - if you do, the error message changes to the much more descriptive "ERROR 1449 (HY000): The user specified as a definer does not exist".

Murmel · Accepted Answer · 2018-01-21 01:05:28Z

I hope you have not done more damage by also deleting the debian-sys-maint user in mysql

Have your mysql daemon running the normal way. Start your mysql client as shown below

mysql -u debian-sys-maint -p

In another terminal, cat the file /etc/mysql/debian.cnf. That file contains a password; paste that password when prompted for it.

http://ubuntuforums.org/showthread.php?t=1836919

David A. Gray · Accepted Answer · 2018-12-27 07:12:32Z

I discovered yet another case that appears on the surface to be an edge case; I can export to the file system, via SELECT INTO .. OUTFILE as root, but not as regular user. While this may be a matter of permissions, I've looked at that, and see nothing especially obvious. All I can say is that executing the query as a regular user who has all permissions on the data base in question returns the access denied error that led me to this topic. When I found the transcript of a successful use of SELECT INTO … OUTFILE in an old project, I noticed that I was logged in as root. Sure enough, when I logged in as root, the query ran as expected.

Shnkc · Accepted Answer · 2014-02-03 08:00:37Z

When you run mysql -u bill -p, localhost is resolved to your ip, since it is 127.0.0.1 and in your /etc/hosts file, as default 127.0.0.1 localhost exists. So, mysql interprets you as bill@localhost which is not granted with bill@'%' . This is why there are 2 different records for root user in result of select host, user from mysql.user; query.

There are two ways to handle this issue.

One is specifying an ip which is not reversely resolved by /etc/hosts file when you try to login. For example, the ip of server is 10.0.0.2. When you run the command mysql -u bill -p -h 10.0.0.2, you will be able to login. If you type select user();, you will get [email protected]. Of course, any domain name should not be resolved to this ip in your /etc/hosts file.

Secondly, you need grant access for this specific domain name. For bill@localhost, you should call command grant all privileges on *.* to bill@localhost identified by 'billpass'; . In this case, you will be able to login with command mysql -u bill -p. Once logined, select user(); command returns bill@localhost.

But this is only for that you try to login a mysql server in the same host. From remote hosts, mysql behaves expectedly, '%' will grant you to login.

J. Scott Elblein · Accepted Answer · 2019-12-14 01:35:19Z

This may apply to very few people, but here goes. Don't use an exclamation ! in your password.

I did and got the above error using MariaDB. When I simplified it to just numbers and letters it worked. Other characters such as @ and $ work fine - I used those characters in a different user on the same instance.

The fifth response at this address led me to my fix.

It's more a matter of understanding how to use your shell properly to escape such characters (or not putting your password on the command line, which is very dangerous.)

salvo720 · Accepted Answer · 2022-05-19 18:10:57Z

sudo -i mysql -u root ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';

access mysql service without login ( can see beacue in shell mysql> )

This repeats numerous answers. Please only post new answers if you have new content to share.

desertnaut · Accepted Answer · 2025-01-08 23:23:35Z

0

I had similar problems because my password contains ";" char breaking my password when I creates it at first moment. Caution with this.

edited Jan 8 at 23:23

desertnaut

60.8k32 gold badges155 silver badges183 bronze badges

answered Sep 16, 2019 at 10:58

Genaut

1,8622 gold badges31 silver badges64 bronze badges

1 Comment

miken32 Jan 8 at 23:01

It's more a matter of understanding how to use your shell properly to escape such characters (or not putting your password on the command line, which is very dangerous.)

Collectives™ on Stack Overflow

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

25 Answers 25

9 Comments

7 Comments

6 Comments

Comments

Comments

4 Comments

2 Comments

1 Comment

Comments

Comments

Comments

2 Comments

Comments

1 Comment

Comments

Comments

Comments

2 Comments

1 Comment

Comments

Comments

Comments

1 Comment

1 Comment

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

25 Answers 25

9 Comments

7 Comments

6 Comments

Comments

Comments

4 Comments

2 Comments

1 Comment

Comments

Comments

Comments

2 Comments

Comments

1 Comment

Comments

Comments

Comments

2 Comments

1 Comment

Comments

Comments

Comments

1 Comment

1 Comment

1 Comment

Linked

Related