转载

mysql启用skip-name-resolve模式时出现Warning的处理办法

	查看mysql告警日志，发现两个警告，如下红体字

[root@S243 ~]#  tail  -f /mysql/datadir/S243.err

2017-02-15 08:59:57 23433 [Warning] 'user' entry 'root@s243' ignored in --skip-name-resolve mode.

2017-02-15 08:59:57 23433 [Warning] 'proxies_priv' entry '@ root@s243' ignored in --skip-name-resolve mode.

2017-02-15 09:00:00 23433 [Warning] 'user' entry 'root@s243' ignored in --skip-name-resolve mode.

2017-02-15 09:00:00 23433 [Warning] 'proxies_priv' entry '@ root@s243' ignored in --skip-name-resolve mode.

2017-02-15 09:22:58 23433 [Warning] 'user' entry 'root@s243' ignored in --skip-name-resolve mode.

2017-02-15 09:22:58 23433 [Warning] 'proxies_priv' entry '@ root@s243' ignored in --skip-name-resolve mode.

2017-02-15 09:23:03 23433 [Warning] 'user' entry 'root@s243' ignored in --skip-name-resolve mode.

2017-02-15 09:23:03 23433 [Warning] 'proxies_priv' entry '@ root@s243' ignored in --skip-name-resolve mode.

网上搜到一篇文章。。。。。。，验证确实解决了问题，于是粘过来做个记录。

	http://www.justwinit.cn/post/7539/ 

	[实践OK]mysql启用skip-name-resolve模式时出现Warning的处理办法  

	背景：skip-name-resolve 参数的目的是不再进行反解析（ip不反解成域名），这样可以加快数据库的反应时间。修改配置文件添加并需要重启：[mysqld] skip-name-resolve添加后发现错误日志有警告信息： 

	[root@jackxiang mysql]# vi my.cnf 

	skip-name-resolve 

	# 禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项， 

	# 则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求 

	实践如下： 

	重启mysql，发现日志还有: 

	1）按提示作下查询，果然有一个空账户和root帐户的Host是jackxiang： 

	select * from user where Host="jackxiang"/G; 

	mysql> select Host,User,Password from user where Host="jackxiang"; 

	+-----------+------+-----------------------------------------------------+ 

	| Host          | User  | Password                              | 

	+-----------+------+-----------------------------------------------------+ 

	| jackxiang  |           |                                                                            | 

	| jackxiang |root      | *2CD42BDFDF0EB0E*Z****3458EB72EE1F17F26F | 

	+-----------+------+------------------------------------------------------+ 

	2）查下localhost，因为大都是限定本机连接，不让外面机器连接，确保安全： 

	mysql> select Host,User,Password from user where Host="localhost" limit 2; 

	+-----------+-----------------+-------------------------------------------+ 

	| Host      | User                     | Password                                               | 

	+-----------+-----------------+-------------------------------------------+ 

	| localhost |                           |                                                               | 

	| localhost | jack_mysql          | *2CD42BDFDF0E***3458EB72EE1F17F26F | 

	+-----------+-----------------+-------------------------------------------+ 

	3）把Host既是 jackxiang的，用户是空或root的Host修改为localhost： 

	mysql> update user set Host="localhost" where Host="jackxiang"; 

	ERROR 1062 (23000): Duplicate entry 'localhost-' for key 'PRIMARY' 

	这样搞不行，得一个一个干掉，看有没有root同名的，查下： 

	mysql> select Host,User,Password from user where User=""; 

	+-----------+------+----------+ 

	| Host      | User | Password | 

	+-----------+------+----------+ 

	| localhost |            |               | 

	| jackxiang |            |               | 

	+-----------+------+----------+ 

	mysql> delete from user where  User="" and Password=""; 

	Query OK, 2 rows affected (0.01 sec) 

	4)查下user为root的 

	mysql> select Host,User,Password from user where User="root";                                       

	+-----------+------+-------------------------------------------+ 

	| Host      | User | Password                                  | 

	+-----------+------+-------------------------------------------+ 

	| localhost | root | *2CD42BDFDF0EB0E1A7777777777EE1F17F26F | 

	| jackxiang | root | *2CD42BDFDF0EB0E1A7777777777EE1F17F26F | 

	| 127.0.0.1 | root | *2CD42BDFDF0EB0E1A7777777777EE1F17F26F | 

	| ::1       | root | *2CD42BDFDF0EB0E1A7777777777EE1F17F26F | 

	+-----------+------+-------------------------------------------+ 

	5)留下localhost就足够了，其余删除掉： 

	mysql> delete from user where Host !="localhost" and User="root"; 

	Query OK, 3 rows affected (0.00 sec) 

	6)restart mysql: 

	

日志warning还有一个： 

	[Warning] 'proxies_priv' entry '@ root@jackxiang' ignored in --skip-name-resolve mode. 

	解决办法： 

	然后删除表mysql.proxies_priv中和cvs类似与具体域名有关的行,方法同上。 

	mysql> select Host,User,Proxied_host,Proxied_user,With_grant,Grantor,Timestamp from proxies_priv ; 

	+-----------+------+--------------+--------------+------------+---------+----------------+ 

	| Host      | User | Proxied_host | Proxied_user | With_grant | Grantor | Timestamp           | 

	+-----------+------+--------------+--------------+------------+---------+----------------+ 

	| localhost | root |              |              |                              1 |         | 2014-07-14 13:26:08 | 

	| jackxiang | root |              |              |                              1 |         | 2014-07-14 13:26:08 | 

	+-----------+------+--------------+--------------+------------+---------+----------------+ 

	mysql> delete from proxies_priv where Host="jackxiang"; 

	Query OK, 1 row affected (0.02 sec) 

	这下彻底清静了。 

	原来是当时安装mysql后，多次grant授权引起的,。 

	备注： 

	  skip-name-resolve是禁用dns解析，避免网络DNS解析服务引发访问MYSQL的错误，一般应当启用。   启用后，在mysql的授权表中就不能使用主机名了，只能使用IP ，出现此警告是由于mysql 表中已经存在有 root@jackxiang 帐号信息。     我们把它删除就好了。   mysql>use mysql; mysql> delete  from user where HOST='localhost.localdomain'; Query OK, 2 rows affected (0.00 sec)   重启MYSQL ，发现警告已经没有啦。 

正文到此结束