MySQL有關1042 Can’t get hostname for your address的問題分析解決過程

[Comment 1]
 前同事企鵝上面說他安裝的mysql 5.5，發現用mysql用戶端遠端連線的時候，報1042-Can’t get hostname for your address錯誤，但是許可權已經grant了並且grant成功了。
 這個報錯的意思是“您的地址無法獲得主機名稱”，我就想到了skip-name-resolve參數。

官方文檔:

--skip-name-resolve

Use IP addresses rather than host names when creating grant table entries. This option can be useful if your DNS does not work.

 

--skip-name-resolve

Do not resolve host names when checking client connections. Use only IP numbers. If you use this option, all
Host column values in the grant tables must be IP numbers or
localhost. See
Section 7.7.11, “How MySQL Uses DNS”.

 

mysql> show variables like '%skip_name_resolve%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| skip_name_resolve | ON   |
+-------------------+-------+
1 row in set (0.00 sec)

mysql> set global skip_name_resolve=0;
ERROR 1238 (HY000): Variable 'skip_name_resolve' is a read only variable
mysql>
失敗了，看來是只有修改my.cnf了

 

在[mysqld]節點下修改

skip-name-resolve
#忽略主機名稱的方式訪問
lower_case_table_names=1
#忽略資料庫表名大小寫

重啟MySQLD process即可。

 

[Comment 2]
前同事企鵝留言：
mysql -h 192.168.1.101 -u root -p
訪問不了，我本地的mysql.192.168.1.101是本地ip地址。
grant all privileges on *.* to
'hive'@'192.168.1.101' identified by 'hive' with grant option;就是報串連不上，許可權問題，怎麼搞定。

查看官方文檔：

hostname

Variable Name	hostname
Variable Scope	Global
Dynamic Variable	No
	Permitted Values
	Type	string

The server sets this variable to the server host name at startup.

 

mysql> show variables like '%hostname%';+---------------+-------------------------------------------+| Variable_name | Value                                     |+---------------+-------------------------------------------+| hostname      | xxxxx.china.online.xx.com |+---------------+-------------------------------------------+1 row in set (0.00 sec)mysql> set global hostname='xxxxx2.china.online.xx.com';ERROR 1238 (HY000): Variable 'hostname' is a read only variablemysql> 

失敗了，只有修改my.cnf了，在mysqld選項下面

 

[mysqld]
# 注釋掉參數即可，mysql直接用本機ip不能訪問
# hostname

然後重啟mysqld 服務即可。