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

First let me mention that I've gone through many suggested questions and found no relevent answer. Here is what I'm doing.

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)

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>

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. Good luck, and I hope to save someone else some time.

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.

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

Super late to this

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.

Once I did that it worked. Hope this helps someone.

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';

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;

Try:

~$ mysql -u root -p
Enter Password:

mysql> grant all privileges on *.* to bill@localhost identified by 'pass' with grant option;

Also the problem can occur if you are using old version of the MySQL UI (like SQLYoug) that generates passwords with wrong hash.

Creating user with SQL script will fix the problem.

OS: windows

My error message is:

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

My reason is that I didn't open cmd with administrator permissions.

So my solution was to open cmd as administrator, then it worked.

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.

I post this answer here to maybe save someone from using the same ridiculous amount of time on this as I did.

For me, It was not specifying the -p parameter when entering mysql.

mysql -p

I had no problem, but it was wrong to invoke mysql without a password.

Not sure if anyone else will find this helpful, but 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!

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.

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

You have to:

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

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
[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
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;
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 <br>
Bye

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.

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

I had the same problem, but in my case the solution was solved by the comment by eggyal. I had an anonymous user as well, but removing it didn't solve the problem. The 'FLUSH PRIVILEGES' command worked though.

The surprising thing to me about this was that I created the user with MySQL Workbench and I would have expected that to perform all of the necessary functions to complete the task.

On Windows, here's how to resolve:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

1. Uninstall mysql from the control panel
2. Delete the MySql folder from C:\Program Files,C:\Program Files (x86) and C:\ProgramData
3. Install mysql

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.

Okay, I'm not sure but probably this is my.cnf file inside mysql installation directory is the culprit. Comment out this line and the problem might be resolved.

bind-address = 127.0.0.1

Nowadays! Solution for :

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

Wampserver 3.2.0 new instalation or upgrading

Probably xamp using mariaDB as default is well.

Wamp server comes with mariaDB and mysql, and instaling mariaDB as default on 3306 port and mysql on 3307, port sometimes 3308.

Connect to mysql!

On instalation it asks to use mariaDB or MySql, But mariaDB is checked as default and you cant change it, check mysql option and install.

when instalation done both will be runing mariaDB on default port 3306 and mysql on another port 3307 or 3308.

Right click on wampserver icon where its runing should be on right bottom corner, goto tools and see your correct mysql runing port.

And include it in your database connection same as folowng :

$host = 'localhost';
$db   = 'test';
$user = 'root';
$pass = '';
$charset = 'utf8mb4';
$port = '3308';//Port

$dsn = "mysql:host=$host;dbname=$db;port=$port;charset=$charset"; //Add in connection
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
try {
     $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
     throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

Note : I am using pdo.

See here for more : https://sourceforge.net/projects/wampserver/

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.

I resolved this by deleting the old buggy user 'bill' entries (this is the important part: both from mysql.user and mysql.db), then created the same user as sad before:

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

Worked, user is connecting. Now I'll remove some previlegies from it :)

If your dbname, username, password, etc strings lengths exceed values outlined at https://dev.mysql.com/doc/refman/5.7/en/grant-tables.html#grant-tables-scope-column-properties , you might also fail to log in, as this was in my case.

I encountered the same error. The setup that wasn't working is as follows:

define("HOSTNAME", "localhost");
define("HOSTUSER", "van");
define("HOSTPASS", "helsing");
define("DBNAME", "crossbow");
$connection = mysqli_connect(HOSTNAME, HOSTUSER, HOSTPASS);

The edited setup below is the one that got it working. Notice the difference?

define('HOSTNAME', 'localhost');
define('HOSTUSER', 'van');
define('HOSTPASS', 'helsing');
define('DBNAME', 'crossbow');
$connection = mysqli_connect(HOSTNAME, HOSTUSER, HOSTPASS);

The difference is the double quotes. They seem to be quite significant in PHP as opposed to Java and they have an impact when it comes to escaping characters, setting up URLs and now, passing parameters to a function. They're prettier (I know) but always use single quotes as much as possible then double quotes can be nested within those if necessary.

This error came up when I tested my application on a Linux box as opposed to a Windows environment.

This may also happen if MySQL is running on a case-insensitive OS, such as Windows.

e.g. I found that attempting to connect to a database using these credentials failed:

mysql> grant select on databaseV105.* to 'specialuser' identified by 's3curepa5wrd';

$ mysql -specialuser' -p's3curepa5wrd' -h10.61.130.89 databaseV105

ERROR 1045 (28000): Access denied for user 'specialuser'@'10.0.1.113' (using password: YES)

But, this succeeded:

mysql> grant select on databasev105.* to 'specialuser' identified by 's3curepa5wrd';

$ mysql -specialuser' -h10.300.300.400 databaseV105 -p

Enter password:

Debugging Summary

• Check for typo error: username or password.
• Check the host name and compare it with mysql.user table host name.
• Check user exists or not.
• Check whether host contains IP address or host name.

There is a great chance that, you might have encountered this issue multiple times in your work. This issue occurred to me most of times due to the incorrectly entering user name or password. Though this is one of the reasons, there are other many chances you might get this issue. Sometimes, it looks very similar, but when you dig deeper, you will realize multiple factors contributing to this error. This post will explain in detail, most of the common reasons and work around to fix this issue.

Possible reasons:

• Case 1: Typo error: username or password.

This is the most common reason for this error. If you entered the username or password wrongly, surely you will get this error.

Solution:

Solution for this type of error is very simple. Just enter the correct username and password. This error will be resolved. In case if you forget the password you can reset the username/password. If you forget the password for admin / root account, there are many ways to reset / recapture the root password. I will publish another post on how to reset the root password in-case if you forget root password.

• Case 2: Accessing from wrong host.

MySQL provides host based restriction for user access as a security features. In our production environment, we used to restrict the access request only to the Application servers. This feature is really helpful in many production scenarios.

Solution:

When you face this type of issue, first check whether your host is allowed or not by checking the mysql.user table. If it is not defined, you can update or insert new record to mysql.user table. Generally, accessing as a root user from remote machine is disabled and it is not a best practice, because of security concerns. If you have requirements to access your server from multiple machines, give access only to those machines. It is better not to use wildcards (%) and gives universal accesses. Let me update the mysql.user table, now the demouser can access MySQL server from any host.

• Case 3: User does not exists on the server.

This type of error occurs when the user, which you are trying to access not exist on the MySQL server.

Solutions:

When you face this type of issue, just check whether the user is exists in mysql.user table or not. If the record not exists, user cannot access. If there is a requirement for that user to access, create a new user with that username.

• Case 4: Mix of numeric and name based hosts.

Important points

• It is not advisable to use wildcards while defining user host, try to use the exact host name.

• Disable root login from remote machine.

• Use proxy user concept.

There are few other concepts related with this topic and getting into details of those topics is very different scope of this article. We will look into the following related topics in the upcoming articles.

• What to do, if you forgot root password in of MySQL server.
• MySQL Access privilege issues and user related tables.
• MySQL security features with best practices.

I hope this post will help for you to fix the MySQL Error Code 1045 Access denied for user in MySQL.

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.

For me root had a default password

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

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

For Mac users if they still have the problem (as was my case), I found this solution worked for me: MySQL command line '-bash command not found'

To use command i.e. mysql on macbook terminal you need to export path using:

export PATH=$PATH:/usr/local/mysql/bin/

Considering default installation, use following command to get mysql prompt as root user:

mysql -u root

Otherwise you are using wrong root password.

Ref: Setting the MySQL root user password on OSX

Be sure there are no other SQL instances running that are using the localhost post. In our case another instance was running on the localhost that conflicted with the login. Turning it off solved this problem.

just remove password

Change this

mydb = mysql.connector.connect(host="localhost",user="root",password='password',auth_plugin='mysql_native_password')

To this

mydb = mysql.connector.connect(host="localhost",user="root",auth_plugin='mysql_native_password')

It worked for me

MySQL account names consist of a user name and a host name, The name 'localhost' in host name indicates the local host also You can use the wildcard characters “%” and “_” in host name or IP address values. These have the same meaning as for pattern-matching operations performed with the LIKE operator. For example, a host value of '%' matches any host name, whereas a value of '%.mysql.com' matches any host in the mysql.com domain. '192.168.1.%' matches any host in the 192.168.1 class C network.

Above was just introduction:

actually both users 'bill'@'localhost' and 'bill'@'%' are different MySQL accounts, hence both should use their own authentication details like password.

For more information refer http://dev.mysql.com/doc/refman//5.5/en/account-names.html

The percent sign means all ip's so localhost is superfluous ... There is no need of the second record with the localhost .

Actually there is, 'localhost' is special in mysql, it means a connection over a unix socket (or named pipes on windows I believe) as opposed to a TCP/IP socket. using % as the host does not include 'localhost'

MySQL user accounts have two components: a user name and a host name. The user name identifies the user, and the host name specifies what hosts that user can connect from. The user name and host name are combined to create a user account:

'<user_name>'@'<host_name>' You can specify a specific IP address or address range for host name, or use the percent character ("%") to enable that user to log in from any host.

Note that user accounts are defined by both the user name and the host name. For example, 'root'@'%' is a different user account than 'root'@'localhost'.

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.

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.