Linux密码正确也进不去mysql_linux下使用正确的用户名密码，本地无法连接mysql

问题现象：

Linux系统为CentOS 7.0 64位，通过IP远程mysql时，可以正常访问，确定账号密码没有问题。但是本地连接mysql时，提示ERROR 1045 (28000): Access denied for user 'RnE0LXGMQhHN'@'localhost' (using password: YES)，如下图：

解决办法：

1 //my.cnf增加一行，跳过访问控制：skip-grant-tables

2

3 [root@iZ28f6o6c20Z ~]# vim /etc/my.cnf4

5 //重启mysql

6

7 [root@iZ28f6o6c20Z ~]# service mysql restart8

9 Redirecting to /bin/systemctl restart mysql.service10

11 //登录

12

13 [root@iZ28f6o6c20Z ~]# /usr/bin/mysql -u RnE0LXGMQhHN -p14

15 Enter password:16

17 Welcome to the MySQL monitor. Commands end with ; or \g.18

19 Your MySQL connection id is 2

20

21 Server version: 5.6.29MySQL Community Server (GPL)22

23 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.24

25 Oracle is a registered trademark of Oracle Corporation and/or its26

27 affiliates. Other names may be trademarks of their respective28

29 owners.30

31 Type 'help;' or '\h' for help. Type '\c'to clear the current input statement.32

33 //刷新权限表

34

35 mysql>flush privileges;36

37 Query OK, 0 rows affected (0.11sec)38

39 //配置RnE0LXGMQhHN权限可以本地访问

40

41 mysql> grant all privileges on *.* to RnE0LXGMQhHN@localhost identified by '********gNWT';42

43 Query OK, 0 rows affected (0.04sec)44

45 //退出

46

47 mysql>exit48

49 //my.cnf删除一行，取消跳过访问控制：skip-grant-tables

50

51 [root@iZ28f6o6c20Z ~]# vim /etc/my.cnf52

53 //重启mysql

54

55 [root@iZ28f6o6c20Z ~]# service mysql restart56

57 Redirecting to /bin/systemctl restart mysql.service58

59 //可以正常登录了

60

61 [root@iZ28f6o6c20Z ~]# /usr/bin/mysql -h localhost -u RnE0LXGMQhHN -p62

63 Enter password:64

65 Welcome to the MySQL monitor. Commands end with ; or \g.66

67 Your MySQL connection id is 46

68

69 Server version: 5.6.29MySQL Community Server (GPL)70

71 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.72

73 Oracle is a registered trademark of Oracle Corporation and/or its74

75 affiliates. Other names may be trademarks of their respective76

77 owners.78

79 Type 'help;' or '\h' for help. Type '\c'to clear the current input statement.80

81 mysql>

原因分析：

Mysql有非常细致的权限控制，用户RnE0LXGMQhHN没有在localhost访问mysql的权限。

1、Mysql中配置权限的命令：

1 mysql> GRANT ON

2

3 -> TO [IDENTIFIED BY ""]4

5 -> [WITH GRANT OPTION];

(1)Privileges 权限列表：有三种类型

a)数据库/数据表/数据列权限:Alter Create Delete Drop INDEX Insert Select Update

b) 全局管理权限：

file: 在MySQL服务器上读写文件。

PROCESS: 显示或杀死属于其它用户的服务线程。

RELOAD: 重载访问控制表，刷新日志等。

SHUTDOWN: 关闭MySQL服务。

c)特别的权限：

ALL: 允许做任何事(和root一样)。

USAGE: 只允许登录--其它什么也不允许做。

(2)what：权限所作用的区域

*.*意味着权限对所有数据库和数据表有效；

dbName.*意味着对名为dbName的数据库中的所有数据表有效；

dbName.tblName意味着仅对名为dbName中的名为tblName的数据表有效；

甚至还可以通过在赋予的权限后面使用圆括号中的数据列的列表以指定权限仅对这些列有效；

(3)user：指定用户

一个用户通过它登录的用户名和用户使用的计算机的主机名/IP地址来指定，例如'discuz'@'localhost'表示从本地访问的discuz用户拥有配置的权限

(4)password：可选项，会取代原密码

(5)WITH GRANT OPTION：可选项，代表用户可以使用GRANT/REVOKE命令将他拥有的权限赋予其他用户

2、Mysql中查看权限的命令

1 mysql> show grants forRnE0LXGMQhHN;2

3 +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

4

5 | Grants for RnE0LXGMQhHN@% |

6

7 +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

8

9 | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'RnE0LXGMQhHN'@'%' IDENTIFIED BY PASSWORD '*8B300979B49556C62DFB9C8941E08E0CF5184A95' WITH GRANT OPTION |

10

11 | GRANT ALL PRIVILEGES ON `esci\_test`.* TO 'RnE0LXGMQhHN'@'%' WITH GRANT OPTION |

12

13 +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

14

15 2 rows in set (0.00sec)16

17 mysql> show grants forroot;18

19 +--------------------------------------------------------------------------------------------------------------------------------+

20

21 | Grants for root@% |

22

23 +--------------------------------------------------------------------------------------------------------------------------------+

24

25 | GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*338D1B0459CF4789AD78DAC80B1CC8ED12BEEEAF' WITH GRANT OPTION |

26

27 +--------------------------------------------------------------------------------------------------------------------------------+

28

29 1 row in set (0.00 sec)

参考资料：