JAVA高级面试进阶训练营视频教程

admin

今天OJ数据库服务器挂了，我这边很奇怪的ssh连不上，后来连上了但是很卡，看了下mysql/error.log没看出来什么原因。学长那边进去mysql用了下showprocesslist发现大量的unauthenticated user Connect的信息。于是查了一下原因。
手册中的解释是：unauthenticated user refers to a thread that has become associated with a client connection but for which authentication of the client user has not yet been done。意即：有一个线程在处理客户端的连接，但是该客户端还没通过用户验证
MySQL处理用户连接时进行如下过程来验证用户的合法性：

When a new client connects to mysqld, mysqld spawns a new thread to handle the request. This thread first checks whether the host name is in the host name cache. If not, the thread attempts to resolve the host name: 
The thread takes the IP address and resolves it to a host name (using gethostbyaddr()). It then takes that host name and resolves it back to the IP address (using gethostbyname()) and compares to ensure it is the original IP address.

即：先作反向解析IP>Hostname,然后作Hostname>IP的正向解析。如果结果符合，则验证为合法用户允许登录，如果不符合则定义为“unauthenticated user”。
原因可能有：
1、服务器在做DNS反响解析，解决办法有两个：
1-）在 hosts 中添加客户端ip，如
192.168.0.1 yejr
2-） MySQL启动参数增加一个skip-name-resolve，即不启用DNS反响解析。即修改mysql配置文件/etc/mysql/my.cnf,在 [mysqld] 行下添加　 –skip-name-resolve ,重新启动mysql服务
2、服务器的线程还处于排队状态，因此可以加大 back_log

采用第二个方法成功了。
建议创建用户权限的时候，不要使用hostname作为限制而是直接用IP；更干脆的话就主机不配置DNS Server