MySQL Study之--MySQL普通用户无法本地登陆
在安装完成MySQL后，我们通常添加拥有相应权限的普通用户用来访问数据库。在使用用户本地登录数据库的时候，经常会出现怎么登录也无法登录的情况,但是从其它的mysql客户端却可以登录。
故障现象：
[root@mysrv ~]# mysql -u root -poracle
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.6.25-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2012,
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 version()\g
+-------------------------------------------+
| version() |
+-------------------------------------------+
| 5.6.25-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)
创建用户并授权：
mysql> grant all on prod.* to 'rose'@'%' identified by 'rose';
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for rose;
+-----------------------------------------------------------------------------------------------------+
| Grants for rose@% |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'rose'@'%' IDENTIFIED BY PASSWORD '*86F57026C60B8CE1038EFB3B9383EC573979A7BD' |
| GRANT ALL PRIVILEGES ON `prod`.* TO 'rose'@'%' |
+-----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select user,host from user;
+-------+-----------+
| user | host |
+-------+-----------+
| jerry | % |
| rose | % |
| tom | % |
| tom1 | % |
| tom2 | % |
| root | 127.0.0.1 |
| root | ::1 |
| | localhost |
| jerry | localhost |
| root | localhost |
| scott | localhost |
| tom | localhost |
| | mysrv |
| root | mysrv |
+-------+-----------+
14 rows in set (0.00 sec)
用户登陆：
[root@mysrv ~]# mysql -u rose -prose
ERROR 1045 (28000): Access denied for user 'rose'@'localhost' (using password: YES) ---登陆失败！
[root@mysrv ~]# mysql -u rose -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.6.25-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2012, 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> use prod;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'prod'--
---在不用密码的情况下可以登陆，但没有权限访问，应该是匿名用户的身份 ！
远程登陆：
---远程登陆成功！
一、登录后查看mysql.user表的情况
可以看到，我的数据库中有rose用户和匿名用户localhost;
mysql> show grants for rose;
+-----------------------------------------------------------------------------------------------------+
| Grants for rose@% |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'rose'@'%' IDENTIFIED BY PASSWORD '*86F57026C60B8CE1038EFB3B9383EC573979A7BD' |
| GRANT ALL PRIVILEGES ON `prod`.* TO 'rose'@'%' |
+---------------------------------------- |