show processlist查看有大量如下信息,造成mysql假死.
| 364 | unauthenticated user | xxx.xxx.xxx.xxx:63249 | NULL | Connect | | login | NULL |
| 365 | unauthenticated user | xxx.xxx.xxx.xxx:56768 | NULL | Connect | | login | NULL |
| 366 | unauthenticated user | xxx.xxx.xxx.xxx:54127 | NULL | Connect | | login | NULL |
| 367 | unauthenticated user | xxx.xxx.xxx.xxx:51060 | NULL | Connect | | login | NULL |
看下手册中的解释是：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。意即：有一个线程在处理客户端的连接，但是该客户端还没通过用户验证。
原因可能有：
1、 服务器在做DNS反响解析，解决办法有2：
1、） 在 hosts 中添加客户端ip，如
192.168.0.1 yejr
2、） MySQL启动参数增加一个skip-name-resolve，即不启用DNS反响解析;或者在/etc/my.cnf里添加一行skip-name-resolve
2、服务器的线程还处于排队状态，因此可以加大 back_log
然后重启mysql就会发现unauthenticated user 全部消失
[手册]
When a new client connects to mysqld, mysqld spawns a new thread to handle the request. This thread first checks whether the hostname is in the hostname cache. If not, the thread attempts to resolve the hostname:
-
If the operating system supports the thread-safe
gethostbyaddr_r()andgethostbyname_r()calls, the thread uses them to perform hostname resolution. -
If the operating system does not support the thread-safe calls, the thread locks a mutex and calls
gethostbyaddr()andgethostbyname()instead. In this case, no other thread can resolve hostnames that are not in the hostname cache until the first thread unlocks the mutex.
You can disable DNS hostname lookups by starting mysqld with the --skip-name-resolve option. However, in this case, you can use only IP numbers in the MySQL grant tables.
If you have a very slow DNS and many hosts, you can get more performance by either disabling DNS lookups with --skip-name-resolve or by increasing the HOST_CACHE_SIZE define (default value: 128) and recompiling mysqld.
You can disable the hostname cache by starting the server with the --skip-host-cache option. To clear the hostname cache, issue a FLUSH HOSTS statement or execute the mysqladmin flush-hosts command.
To disallow TCP/IP connections entirely, start mysqld with the --skip-networking option.