- 从192.168.111.99上连接远程数据的时候报错:
-
- [mysql@LVS01 mysql_5621]$ mysql -uroot -p123 -h192.168.111.10 -P 5621
- Warning: Using a password on the command line interface can be insecure.
- ERROR 1045 (28000): Access denied for user 'root'@'192.168.111.99' (using password: YES
-
-
- 查看远程主机权限:
-
- mysql> use mysql;
- Database changed
- mysql> select user,host,password from user;
- +-------+----------------+-------------------------------------------+
- | user | host | password |
- +-------+----------------+-------------------------------------------+
- | root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
- | root | mysql-svr1 | |
- | root | 127.0.0.1 | |
- | root | ::1 | |
- | repl | 192.168.110.20 | *A424E797037BF97C19A2E88CF7891C5C2038C039 |
- | repl | 192.168.111.20 | *A424E797037BF97C19A2E88CF7891C5C2038C039 |
- | repl | 192.168.111.30 | *A424E797037BF97C19A2E88CF7891C5C2038C039 |
- | repl | 192.168.111.10 | *A424E797037BF97C19A2E88CF7891C5C2038C039 |
- | user1 | % | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
- | root | 192.168.111.20 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
- | root | 192.168.111.30 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
- | root | 192.168.111.10 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
- | admin | 192.168.111.10 | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
- | root | 192.168.111.% | |
- | root | 192.168.111.99 | |
- | root | % | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
- +-------+----------------+-------------------------------------------+
- 16 rows in set (0.00 sec)
-
- 发现已经给192.168.111.99授权了,可为什么还是连不上?
-
- 和192.168.111.99相关的权限是下面几行:
-
-
- | root | 192.168.111.% | |
- | root | 192.168.111.99 | |
- | root | % | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
-
- 尝试使用空密码登陆成功:
-
- [mysql@LVS01 mysql_5621]$ mysql -uroot -h192.168.111.10 -P 5621
- Welcome to the MySQL monitor. Commands end with ; or \g.
- Your MySQL connection id is 20216
- Server version: 5.6.21-log Source distribution
- Copyright (c) 2000, 2014, 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>
-
- 删除密码为空的用户:
-
- mysql> drop user root@'192.168.111.%';
- Query OK, 0 rows affected (0.31 sec)
- mysql> drop user root@'192.168.111.99';
- Query OK, 0 rows affected (0.00 sec)
-
- mysql> select user,host,password from user;
-
- +-------+----------------+-------------------------------------------+
- | user | host | password |
- +-------+----------------+-------------------------------------------+
- | root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
- | root | mysql-svr1 | |
- | root | 127.0.0.1 | |
- | root | ::1 | |
- | repl | 192.168.110.20 | *A424E797037BF97C19A2E88CF7891C5C2038C039 |
- | repl | 192.168.111.20 | *A424E797037BF97C19A2E88CF7891C5C2038C039 |
- | repl | 192.168.111.30 | *A424E797037BF97C19A2E88CF7891C5C2038C039 |
- | repl | 192.168.111.10 | *A424E797037BF97C19A2E88CF7891C5C2038C039 |
- | user1 | % | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
- | root | 192.168.111.20 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
- | root | 192.168.111.30 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
- | root | 192.168.111.10 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
- | admin | 192.168.111.10 | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
- | root | % | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
- +-------+----------------+-------------------------------------------+
- 14 rows in set (0.00 sec)
- mysql>
-
- 再次使用密码登陆成功:
-
- [mysql@LVS01 mysql_5621]$ mysql -uroot -p123 -h192.168.111.10 -P 5621
- 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 20312
- Server version: 5.6.21-log Source distribution
- Copyright (c) 2000, 2014, 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>
-
- 总结:
- 出现这个错误的原因是同一个用户有不同主机范围的权限时,按最匹配的一个主机验证权限。
- 也就是说从192.168.111.99登陆服务器时,验证顺序为:
- root@'192.168.111.99'
- root@'192.168.111.%'
- root@'%'
-
- 参考文档:
- http://dev.mysql.com/doc/refman/5.7/en/connection-access.html
- When multiple matches are possible, the server must determine which of them to use. It resolves this issue as follows:
-
- Whenever the server reads the user table into memory, it sorts the rows.
-
- When a client attempts to connect, the server looks through the rows in sorted order.
-
- The server uses the first row that matches the client host name and user name.
-
- The server uses sorting rules that order rows with the most-specific Host values first. Literal host names and IP addresses are the most specific. (The specificity of a literal IP address is not affected by whether it has a netmask, so 192.168.1.13 and 192.168.1.0/255.255.255.0 are considered equally specific.) The pattern '%' means “any host” and is least specific. The empty string '' also means “any host” but sorts after '%'. Rows with the same Host value are ordered with the most-specific User values first (a blank User value means “any user” and is least specific). For rows with equally-specific Host and User values, the order is indeterminate.
-
- 建议:
- (1)用户授权时,按最小主机范围授权,并且赋予密码,只授权一次。
- (2)如果将来主机范围变化,重新授权时可删除旧授权,否则可能因为两次授权密码不同导致类似问题。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29100640/viewspace-2078153/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29100640/viewspace-2078153/