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)
Answers
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).
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).
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@localhostCREATE 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 -hmydb@mydomain.com
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.
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
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/mysql3 : 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 databasemysql> 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 changed5 : 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: 06 : flushmysql> flush privileges;7: quitmysql> quit
Bye8: 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
Super late to this
I tried all of these other answers and ran many different versions of
mysql -u root -p but never just ranmysql -u root -p
And just pressing
[ENTER] for the password.
Once I did that it worked. Hope this helps someone.
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
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>
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 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.
On window ,How to resolve ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
1) Uninstall mysql from control panel
2)Delete MySql folder from C:\Program Files,C:\Program Files (x86) and C:\ProgramData
3)Install 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 bill@10.0.0.2. 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.
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.
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'.
I had the same issue as the OP while trying to access a MYSQL docker container built with compose that had a really long password:
# docker-compose.yml snippet
services:
db:
environment:
MYSQL_ROOT_PASSWORD: some_password_more_than_32_characters
Reducing the password length and rebuilding via compose enabled me to access the MYSQL container with a client like Workbench.
Posts
![IVRAMANA](https://draft.blogger.com/img/profile_blank_gray.jpg" title="IVRAMANA)
0 comments:
Post a Comment