首页 > 代码库 > 用户管理模块之mysql.user

用户管理模块之mysql.user

不使用-h参数来指定登录host,默认会连接localhost,仅当mysql.user表中有一条对应的localhost访问授权(username@%不对任何主机做限制也不行)时登录才成功,否则登录会被拒绝。
虚拟机VMUest上安装两个MySQL实例,两个实例搭建了Master(端口3306)-Slave(端口3307),主从数据完全一致。

技术分享
mysql> select Host,User,Password from mysql.user;+-----------------------+--------+-------------------------------------------+| Host                  | User   | Password                                  |+-----------------------+--------+-------------------------------------------+| localhost             | root   | *6B4F89A54E2D27ECD7E8DA05B4AB8FD9D1D8B119 || localhost.localdomain | root   |                                           || 127.0.0.1             | root   |                                           || ::1                   | root   |                                           || localhost             |        |                                           || localhost.localdomain |        |                                           || %                     | mydba  | *80BF8C1F4008F25267DB194E29D2E8BC20C836ED || %                     | backup | *975B2CD4FF9AE554FE8AD33168FBFC326D2021DD || 192.168.85.%          | repl   | *A424E797037BF97C19A2E88CF7891C5C2038C039 |+-----------------------+--------+-------------------------------------------+9 rows in set
View Code

使用root用户登录

技术分享
#不指定-h参数,默认就会走localhost[uest@VMUest ~]$ mysql -uroot -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.6.35-log Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type help; or \h for help. Type \c to clear the current input statement.mysql> select @@port;+--------+| @@port |+--------+|   3306 |+--------+1 row in set (0.00 sec)mysql> exitBye[uest@VMUest ~]$ mysql -uroot -p -P3307Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.6.35-log Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type help; or \h for help. Type \c to clear the current input statement.mysql> select @@port;+--------+| @@port |+--------+|   3306 |+--------+1 row in set (0.00 sec)mysql> exitBye#指定-h+ip参考,同时指定-P端口[uest@VMUest ~]$ mysql -uroot -p -h127.0.0.1 -P3306Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 5Server version: 5.6.35-log Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type help; or \h for help. Type \c to clear the current input statement.mysql> select @@port;+--------+| @@port |+--------+|   3306 |+--------+1 row in set (0.00 sec)mysql> exitBye[uest@VMUest ~]$ mysql -uroot -p -h127.0.0.1 -P3307Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.6.35-log Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type help; or \h for help. Type \c to clear the current input statement.mysql> select @@port;+--------+| @@port |+--------+|   3307 |+--------+1 row in set (0.00 sec)mysql> exitBye#指定-h+localhost参考,同时指定-P端口[uest@VMUest ~]$ mysql -uroot -p -hlocalhost -P3307Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 10Server version: 5.6.35-log Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type help; or \h for help. Type \c to clear the current input statement.mysql> select @@port;+--------+| @@port |+--------+|   3306 |+--------+1 row in set (0.00 sec)mysql> exitBye[uest@VMUest ~]$ 
View Code

从上面的结果可以看出只有在指定-h+ip,同时指定-P端口的情况下,才能连接到指定的端口中。
注意:我们的mysql.user表中root@‘127.0.0.1‘对应的Password为空(即不需要输入密码),但实际登录过程需要输入密码。这是受skip_name_resolve参数影响

技术分享
mysql> show variables like %skip_name_resolve%;+-------------------+-------+| Variable_name     | Value |+-------------------+-------+| skip_name_resolve | OFF   |+-------------------+-------+1 row in set
View Code

该参数的值为OFF,root@‘127.0.0.1‘会转化为root@‘localhost‘登录,它使用的是root@‘localhost‘定义的密码。可尝试开启skip_name_resolve参数(配置文件my.cnf中添加skip_name_resolve=1),然后使用空密码登录。
mysql.user表中第5条记录Host=‘localhost‘,User和Password字段为空,也就是localhost可以不指定用户、密码直接登录

技术分享
[uest@VMUest ~]$ mysqlWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 26Server version: 5.6.35-log Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type help; or \h for help. Type \c to clear the current input statement.mysql> select @@port;+--------+| @@port |+--------+|   3306 |+--------+1 row in set (0.00 sec)mysql> 
View Code

还是有一些小细节需要注意。引用iVictor一段话:‘t1‘@‘%‘中包含‘t1‘@‘127.0.0.1‘,如果开启skip_name_resolve参数,则‘t1‘@‘%‘中定义的密码可用于‘t1‘@‘127.0.0.1‘的登录,如果没有开启该参数,则‘t1‘@‘127.0.0.1‘会转化为‘t1‘@‘localhost‘登录,此时‘t1‘@‘%‘定义的密码并不适用。

用户管理模块之mysql.user