mysql出现unauthenticated user简单分析以及解决方法

近期进行性能测试时，数据库采用mysql，当当用show processlist;看到如下信息时:

LL | login | NULL             |
| 697 | unauthenticated user | 218.26.219.186:26944 | NULL       | Connect | NULL | login | NULL             |
| 699 | unauthenticated user | 218.204.126.130:41099 | NULL       | Connect | NULL | login | NULL             |
| 700 | unauthenticated user | 201.91.135.204:43806 | NULL       | Connect | NULL | login | NULL             |
| 701 | unauthenticated user | 218.204.126.130:41119 | NULL       | Connect | NULL | login | NULL             |
| 704 | unauthenticated user | 220.227.138.82:55339 | NULL       | Connect | NULL | login | NULL             |
| 706 | unauthenticated user | 220.227.138.82:55356 | NULL       | Connect | NULL | login | NULL             |
| 707 | unauthenticated user | 220.227.138.82:55346 | NULL       | Connect | NULL | login | NULL             |
| 709 | unauthenticated user | 201.39.194.106:35304 | NULL       | Connect | NULL | login | NULL             |
| 710 | unauthenticated user | 59.76.81.3:27225      | NULL       | Connect | NULL | login | NULL             |
| 711 | unauthenticated user | 220.227.138.82:55352 | NULL       | Connect | NULL | login | NULL             |
| 712 | unauthenticated user | 201.39.194.106:60461 | NULL       | Connect | NULL | login | NULL             |
| 713 | unauthenticated user | 193.194.89.2:55643    | NULL       | Connect | NULL | login | NULL             |
| 714 | unauthenticated user | 220.227.138.82:55378 | NULL       | Connect | NULL | login | NULL             |
| 716 | unauthenticated user | 220.227.138.82:55353 | NULL       | Connect | NULL | login | NULL             |
| 717 | unauthenticated user | 202.133.195.2:3983    | NULL       | Connect | NULL | login | NULL             |
| 718 | unauthenticated user | 220.227.138.82:55358 | NULL       | Connect | NULL | login | NULL             |
| 719 | unauthenticated user | 220.227.138.82:55360 | NULL       | Connect | NULL | login | NULL             |
| 723 | unauthenticated user | 220.227.138.82:55359 | NULL       | Connect | NULL | login | NULL             |
| 725 | unauthenticated user | 59.76.81.3:28440      | NULL       | Connect | NULL | login | NULL             |
| 726 | unauthenticated user | 59.76.81.3:28439      | NULL       | Connect | NULL | login | NULL             |
| 727 | unauthenticated user | 202.133.195.2:2368    | NULL       | Connect | NULL | login | NULL             |
| 728 | unauthenticated user | 218.204.126.130:41336 | NULL       | Connect | NULL | login | NULL             |
| 731 | unauthenticated user | 200.219.152.6:57604   | NULL       | Connect | NULL | login | NULL             |经过查证,不管连结的的方式是经过 hosts 或是 IP 的模式,他都会对 DNS 做反查
mysqld 会尝试去反查 IP -> dns ,由于反查解析过慢,无法应付快速多量的查询

解决办法:
1、修改my.ini文件,在 [mysqld] 行下添加　 –skip-name-resolve ,重新启动mysql服务
2、访问的主机授权时用IP,最好把该主机的IP及主机名写到/etc/hosts文件中

经过以上的设定及重新启动过后,直到现在还没有看到一个 unauthenticated user 的情况发生
近日还是会持续的观察 Mysql 的运作情况,如有问题将会持续的禀报及解决

经过这样处理后,就只能通过localhost访问数据库,如果在/etc/hosts加别名到127.0.0.1 是不行的.会出现报错:

[root@X ~]# mysql -h127.0.0.1 -p
Enter password:
ERROR 1130 (00000): Host '127.0.0.1' is not allowed to connect to this MySQL server所以如果在使用了多个Mysql访问主机名时,激活–skip-name-resolve就会导致系统只允许 localhost访问.而127.0.0.1访问就会被拒绝.