MySQL 'root'@'localhost'无法登录,提示Access denied for user ‘root’@’localhost’ (using password: YES)
MySQL 'root'@'localhost'无法登录,提示Access denied for user ‘root’@’localhost’ (using password: YES)
郑加威 发表于1个月前
MySQL 'root'@'localhost'无法登录,提示Access denied for user ‘root’@’localhost’ (using password: YES)
  • 发表于 1个月前
  • 阅读 22
  • 收藏 0
  • 点赞 0
  • 评论 0

腾讯云 十分钟定制你的第一个小程序>>>   

#mysql -u root -p 
提示”Access denied for user ‘root’@’localhost’ (using password: YES)”

[root@test01 bin]# ./mysql -u root -p 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@test01 bin]# 

第一反应是谁修改了root密码?按照忘记root密码来重置一下密码: 

[root@test01 bin]# /etc/init.d/mysql stop 
[root@test01 bin]# mysqld_safe –skip-grant-tables & 
[root@test01 bin]# ./mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2649
Server version: 5.5.47 Source distribution

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> update mysql.user set password=password(‘rootroot’) where user=’root’;
Query OK, 0 rows affected (0.00 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
mysql> 

用新密码还是无法登录,提示跟上面一样。换一个非root账号登录,查看一下user表: 

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
mysql> select user,host from user; 
+-----------+----------------+
| user      | host           |
+-----------+----------------+
| monitor   | %              |
| root      | %              |
| root      | ::1            |
|           | localhost      |
| root      | localhost      |
|           | test01         |
| root      | test01         |
+-----------+----------------+
10 rows in set (0.00 sec)

mysql> 

怀疑默认的localhost没有映射到127.0.0.1?

[root@test01 ~]# cd /mysql/bin
You have new mail in /var/spool/mail/root
[root@test01 bin]# ./mysql -u root -h 127.0.0.1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 520
Server version: 5.5.47 Source distribution

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> 

试试#mysql -u root -p xxxx -h 127.0.0.1,果然可以登录。 

配置数据库的时候,没有给’root’@’localhost’和’root’@’ip’授权。

mysql> grant all privileges on *.* to root@'%' identified by 'rootroot' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to root@'localhost' identified by 'rootroot' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to root@'127.0.0.1' identified by 'rootroot' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to root@'192.168.XX.XXX' identified by 'rootroot' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> 

再查询一下用户表:

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
mysql> select user,host from user; 
+-----------+----------------+
| user      | host           |
+-----------+----------------+
| monitor   | %              |
| root      | %              |
| root      | 127.0.0.1      |
| root      | 192.168.xx.xxx |
| root      | ::1            |
|           | localhost      |
| root      | localhost      |
|           | test01         |
| root      | test01         |
+-----------+----------------+
10 rows in set (0.00 sec)

mysql> 

然后# mysql -u root -p xxxx,登录成功!

查了一下mysql -h localhost和mysql -h 127.0.0.1的区别,通过localhost连接到mysql是使用UNIX socket,而通过127.0.0.1连接到mysql是使用TCP/IP

mysql>  status
--------------
./mysql  Ver 14.14 Distrib 5.5.47, for Linux (x86_64) using readline 5.1

Connection id:		2649
Current database:	mysql
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.5.47 Source distribution
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8
Db     characterset:	utf8
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/mysql/data/mysql.sock
Uptime:			14 days 2 hours 59 min 38 sec

Threads: 12  Questions: 672838  Slow queries: 4  Opens: 252  Flush tables: 1  Open tables: 170  Queries per second avg: 0.551
--------------

mysql> 
[root@test01 bin]# ./mysql -u root -h 127.0.0.1 -p mysql
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2678
Server version: 5.5.47 Source distribution

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> status
--------------
./mysql  Ver 14.14 Distrib 5.5.47, for Linux (x86_64) using readline 5.1

Connection id:		2678
Current database:	mysql
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.5.47 Source distribution
Protocol version:	10
Connection:		127.0.0.1 via TCP/IP
Server characterset:	utf8
Db     characterset:	utf8
Client characterset:	utf8
Conn.  characterset:	utf8
TCP port:		3306
Uptime:			14 days 3 hours 17 min 42 sec

Threads: 12  Questions: 676580  Slow queries: 4  Opens: 252  Flush tables: 1  Open tables: 170  Queries per second avg: 0.553
--------------

mysql> 

 

共有 人打赏支持
粉丝 19
博文 549
码字总数 699704
×
郑加威
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: