向东博客 专注WEB应用 构架之美 --- 构架之美，在于尽态极妍 | 应用之美，在于药到病除|赢在IT，Playin' with IT,Focus on Killer Application,Marketing Meets Technology.

背景：skip-name-resolve 参数的目的是不再进行反解析（ip不反解成域名），这样可以加快数据库的反应时间。修改配置文件添加并需要重启：[mysqld] skip-name-resolve添加后发现错误日志有警告信息：
==> mysql-error.log <== 2014-09-30 23:22:54 19598 [Note] InnoDB: 5.6.21 started; log sequence number 2129979262 2014-09-30 23:22:55 19598 [Note] Server hostname (bind-address): '119.10.*.22'; port: 3306 2014-09-30 23:22:55 19598 [Note]   - '119.10.*.22' resolves to '119.10.*.22'; 2014-09-30 23:22:55 19598 [Note] Server socket created on IP: '119.10.*.22'. 2014-09-30 23:22:55 19598 [Warning] 'user' entry 'root@jackxiang' ignored in --skip-name-resolve mode. 2014-09-30 23:22:55 19598 [Warning] 'user' entry '@jackxiang' ignored in --skip-name-resolve mode. 2014-09-30 23:22:55 19598 [Warning] 'proxies_priv' entry '@ root@jackxiang' ignored in --skip-name-resolve mode. 2014-09-30 23:22:55 19598 [Note] Event Scheduler: Loaded 0 events 2014-09-30 23:22:55 19598 [Note] /usr/local/mysql/bin/mysqld: ready for connections. Version: '5.6.21-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution


[root@jackxiang mysql]# vi my.cnf
skip-name-resolve
# 禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，
# 则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求

实践如下：
[root@jackxiang examples]# mysql -uroot -p Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 118176 Server version: 5.6.21-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> delete  from user where HOST='localhost.localdomain'; Query OK, 0 rows affected (0.00 sec)


重启mysql，发现日志还有:
[root@jackxiang examples]# service mysql restart Shutting down MySQL........ SUCCESS! Starting MySQL........ SUCCESS!


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:
[root@jackxiang examples]# service mysql restart Shutting down MySQL........ SUCCESS! Starting MySQL........ SUCCESS!

日志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-error.log <== 2014-10-01 10:20:42 19598 [Note] /usr/local/mysql/bin/mysqld: Normal shutdown 141001 10:20:50 mysqld_safe mysqld from pid file /data/mysql/mysql.pid ended 2014-10-01 10:20:59 27231 [Note] /usr/local/mysql/bin/mysqld: ready for connections. Version: '5.6.21-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution ==> mysql-slow.log <== /usr/local/mysql/bin/mysqld, Version: 5.6.21-log (Source distribution). started with: Tcp port: 3306  Unix socket: /tmp/mysql.sock

原来是当时安装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 ，发现警告已经没有啦。

来自：http://www.dedecms.com/knowledge/data-base/mysql/2012/0819/7084.html
http://blog.itpub.net/14184018/viewspace-1061224/

作者：jackxiang@向东博客 专注WEB应用 构架之美 --- 构架之美，在于尽态极妍 | 应用之美，在于药到病除
地址：https://jackxiang.com/post/7539/
版权所有。转载时必须以链接形式注明作者和原始出处及本声明！