用户登陆遇到主机名问题

问题描述:
使用跟hostname一样的用户可以登陆msyql,不一样的登陆不了。

 一、背景描述:
 
1、系统配置:
(1)[root@mongo1 ~]# cat /etc/hostname
        mongo1

(2)[root@mongo1 ~]# cat /etc/hosts
            127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
            ::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
            172.16.100.247 mongo3
            172.16.100.245 mongo1
            172.16.100.246 mongo2
            172.16.100.245 app2.zst.com
            172.16.100.246 app3.zst.com
 
2、mysql配置:
(1)skip-name-resolve=off  #解析主机名
 
(2)用户表:
mysql> select user,host from mysql.user;
        +---------------+--------------+
        | user                | host         |
        +---------------+--------------+
        | zst                   | app%.zst.com |
        | mysql.session | localhost    |
        | mysql.sys        | localhost    |
        | root                | localhost    |
        | Song               | mongo%       |
        +---------------+--------------+
        5 rows in set (0.00 sec)
 
 
二、问题描述:

使用跟hostname一样的用户可以登陆,别的登陆不了,
比如hostname是mongo1,使用Song@'mongo%' 这个用户可以登陆,使用zst@'app%.zst.com' 这个用户登陆不了。

用户Song@'mongo%'   登陆截图如下:

[root@mongo1 ~]# mysql3306  -uSong -pSong -h172.16.100.245
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.22-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> \q
 
 
使用zst用户登陆不了,日志没有报错信息。
 
建用户zst的语句:

mysql> create user 'zst'@'app%.zst.com' identified by 'zst';
Query OK, 0 rows affected (0.00 sec)

登陆报错截图:
[root@mongo1 ~]# mysql3306  -uzst -pzst -h172.16.100.245
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'zst'@'mongo1' (using password: YES)
 

 
感谢阅读!
 
 
已邀请:

wubx - 专注MySQL及架构设计

赞同来自: Chrisu

mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'zst'@'mongo1' (using password: YES)
 
从最后的报错来看,是把你识别成mongo1的来源,没能识别成 app%.zst.com 这个来源。
 
和我上课遇到的不一样,我上课遇到的问题,只识别IP, 基于hostname的不能识别。你这个把mongoX这类从hosts中删掉试一下。

Chrisu - 爱生活爱工作

赞同来自:

按照老师建议,情况确实如此。
操作步骤:
1、把/etc/hosts里mongo1 注释掉后,/etc/hosts 如下所示:
[root@mongo1 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
172.16.100.247 mongo3
#172.16.100.245 mongo1
172.16.100.246 mongo2
172.16.100.245 app2.zst.com
172.16.100.246 app3.zst.com
 
2、再使用zst用户,登录成功,截图如下:
 
[root@mongo1 ~]# mysql3306  -uzst -pzst  -h172.16.100.245
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.7.22-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select user(),current_user();
+------------------+------------------+
| user()           | current_user()   |
+------------------+------------------+
| zst@app2.zst.com | zst@app%.zst.com |
+------------------+------------------+
1 row in set (0.00 sec)

mysql> \q
Bye
感谢老师!
总结来说就是 最好  /etc/hosts里只能有一个,多个的话就不大起作用了,是吧!
 

要回复问题请先登录注册