web:/home/web/install/tomcat8_sm1/logs> mysql -h10.0.29.205 -u loan -p loan Enter password: ERROR 1045 (28000): Access denied for user 'loan'@'kfcsdb1' (using password: YES)
问题：最近项目中遇到个奇怪问题，同事搭建的web服务中的JDBC连接，只要用主机IP地址，就无法连接，只能通过localhost连接。而同样的IP地址形式的JDBC连接从远程主机却能正常连接。JDBC也正常
原因：因为远程主机能连接，说明mysql的远程连接已经打开. 最后排查发现，服务器本机配置了/etc/hosts, 在该文件中，10.0.29.205被DNS系统解释成主机名kfcsdb1.
而在mysql的权限管理中， kfcsdb1被配置成只能用root登录。所以导致WEB应用中的loan用户无法连接。而远程主机本身没有配置kfcsdb1主机名， 所以能通过mysql的权限检查。
解决： 从本地登录mysql, 将kfcsdb1和10.0.29.205授权给loan用户， 解决问题
web:/home/web/install/tomcat8_sm1/logs> mysql -hlocalhost -uloan -ploan Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 7667 Server version: 5.4.3-beta-log MySQL Community Server (GPL) Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> use mysql; Database changed mysql> select host,user from user; +-----------+------+ | host | user | +-----------+------+ | % | loan | | 127.0.0.1 | root | | kfcsdb1 | | | kfcsdb1 | root | | localhost | | | localhost | loan | | localhost | root | +-----------+------+ 7 rows in set (0.00 sec)
以上红色就是无法本地通过IP登录的原因，而localhost是可以的
mysql> GRANT ALL PRIVILEGES ON *.* TO 'loan'@'kfcsdb1' IDENTIFIED BY 'loan' WITH GRANT OPTION; Query OK, 0 rows affected (0.01 sec) mysql> select host,user from user; +-----------+------+ | host | user | +-----------+------+ | % | loan | | 127.0.0.1 | root | | kfcsdb1 | loan | | localhost | | | localhost | loan | | localhost | root | +-----------+------+ 6 rows in set (0.00 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO 'loan'@'10.0.29.205' IDENTIFIED BY 'loan' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec) mysql> select host,user from user; +-------------+------+ | host | user | +-------------+------+ | % | loan | | 10.0.29.205 | loan | | 127.0.0.1 | root | | kfcsdb1 | loan | | localhost | | | localhost | loan | | localhost | root | +-------------+------+ 7 rows in set (0.00 sec)
授权后，loan用户也能通过IP从本地连接, WEB应用JDBC连接也能正常了