mysql启用skip-name-resolve模式时出现Warning怎么解决

发布时间:2021-09-14 18:07 来源:亿速云 阅读:0 作者:chen 栏目: 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对外部连接进行解析,使用这一选项可以消除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 ,发现警告已经没有啦。

免责声明:本站发布的内容(图片、视频和文字)以原创、来自本网站内容采集于网络互联网转载等其它媒体和分享为主,内容观点不代表本网站立场,如侵犯了原作者的版权,请告知一经查实,将立刻删除涉嫌侵权内容,联系QQ:712375056。