用户管理模块之mysql.user

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 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.35-log Source distribution

Copyright (c) 2000, 2016, 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 @@port;
+--------+
| @@port |
+--------+
|   3306 |
+--------+
1 row in set (0.00 sec)

mysql> exit
Bye
[uest@VMUest ~]$ mysql -uroot -p -P3307
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.35-log Source distribution

Copyright (c) 2000, 2016, 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 @@port;
+--------+
| @@port |
+--------+
|   3306 |
+--------+
1 row in set (0.00 sec)

mysql> exit
Bye
#指定-h+ip参考，同时指定-P端口
[uest@VMUest ~]$ mysql -uroot -p -h127.0.0.1 -P3306
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.35-log Source distribution

Copyright (c) 2000, 2016, 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 @@port;
+--------+
| @@port |
+--------+
|   3306 |
+--------+
1 row in set (0.00 sec)

mysql> exit
Bye
[uest@VMUest ~]$ mysql -uroot -p -h127.0.0.1 -P3307
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.35-log Source distribution

Copyright (c) 2000, 2016, 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 @@port;
+--------+
| @@port |
+--------+
|   3307 |
+--------+
1 row in set (0.00 sec)

mysql> exit
Bye
#指定-h+localhost参考，同时指定-P端口
[uest@VMUest ~]$ mysql -uroot -p -hlocalhost -P3307
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.6.35-log Source distribution

Copyright (c) 2000, 2016, 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 @@port;
+--------+
| @@port |
+--------+
|   3306 |
+--------+
1 row in set (0.00 sec)

mysql> exit
Bye
[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 ~]$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.6.35-log Source distribution

Copyright (c) 2000, 2016, 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 @@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

标签：过程   mysql   参数   ide   word   password   连接   color   val