一、问题描述
1、客户端在连接目标数据库的时候,需要49s才能连接上
time /usr/local/zftmysql/bin/mysql -h10.132.16.34 -utest -ptest
real 0m49.304s
user 0m0.004s
sys 0m0.000s
time /usr/local/zftmysql/bin/mysql -h10.132.16.34 -utest -ptest
real 0m49.304s
user 0m0.004s
sys 0m0.000s
2、然后登录目标数据库执行show processlist时,User列显示unauthenticated user
mysql> show processlist;
+----+----------------------+----------------------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+----------------------+----------------------+------+---------+------+-------+------------------+
| 6 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 7 | unauthenticated user | 10.132.16.136:53254 | NULL | Connect | NULL | login | NULL |
+----+----------------------+----------------------+------+---------+------+-------+------------------+
二、问题分析
查看官方文档,unauthenticated user说明正在给未完成验证的客户端用户分配客户端连接。那为什么验证需要那么久时间呢,只有一个可能,MySQL在进行域名解析查看一下域名解析的设置
mysql> show variables like 'skip_name_resolve';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| skip_name_resolve | OFF |
+-------------------+-------+
果然是MySQL服务器没有打开跳过域名解析
三、解决问题
问题找到了,怎么解决?1、skip_name_resolve是一个只读(静态)参数,必须在配置文件修改,然后重启才能生效
mysql> set global skip_name_resolve=on;
ERROR 1238 (HY000): Variable 'skip_name_resolve' is a read only variable
2、修改配置文件
#在[mysqld]下面添加一行
skip-name-resolve
3、然后重启MySQL
4、重新测试:
time /usr/local/zftmysql/bin/mysql -h10.132.16.34 -utest -ptest
real 0m0.015s
user 0m0.004s
sys 0m0.000s