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.cnf 4 5 //重启mysql 6 7 [root@iZ28f6o6c20Z ~]# service mysql restart 8 9 Redirecting to /bin/systemctl restart mysql.service 10 11 //登录 12 13 [root@iZ28f6o6c20Z ~]# /usr/bin/mysql -u RnE0LXGMQhHN -p 14 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.29 MySQL 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 its 26 27 affiliates. Other names may be trademarks of their respective 28 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.11 sec) 38 39 //配置RnE0LXGMQhHN权限可以本地访问 40 41 mysql> grant all privileges on *.* to RnE0LXGMQhHN@localhost identified by '********gNWT'; 42 43 Query OK, 0 rows affected (0.04 sec) 44 45 //退出 46 47 mysql> exit 48 49 // my.cnf删除一行，取消跳过访问控制：skip-grant-tables 50 51 [root@iZ28f6o6c20Z ~]# vim /etc/my.cnf 52 53 //重启mysql 54 55 [root@iZ28f6o6c20Z ~]# service mysql restart 56 57 Redirecting to /bin/systemctl restart mysql.service 58 59 //可以正常登录了 60 61 [root@iZ28f6o6c20Z ~]# /usr/bin/mysql -h localhost -u RnE0LXGMQhHN -p 62 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.29 MySQL 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 its 74 75 affiliates. Other names may be trademarks of their respective 76 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 <privileges> ON <what> 2 3 -> TO <user> [IDENTIFIED BY "<password>"] 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 for RnE0LXGMQhHN; 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.00 sec) 16 17 mysql> show grants for root; 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)
参考资料：
http://www.phperz.com/article/14/1208/39952.html
