Mysql.user of User Management module

The login host is not specified by using the-h parameter, and localhost is connected by default, only if the Mysql.user table has a corresponding localhost access authorization ([email protected]% does not restrict any hosts). Otherwise, the login will be rejected.
Two MySQL instances are installed on the virtual machine vmuest, two instances are built with master (Port 3306)-slave (Port 3307), and the master and slave data are exactly the same.

Mysql> SelectHost,User, Password fromMysql.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. -.%          |Repl| *a424e797037bf97c19a2e88cf7891c5c2038c039|+-----------------------+--------+-------------------------------------------+9Rowsinch Set

View Code

Log in with the root user

#不指定-h parameter, will go by default Localhost[[email protected]~]$ Mysql-uroot-Penter Password:welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connectionIDIs3Server Version:5.6. *-Log Source distributioncopyright (c) -, ., Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or Itsaffiliates. Other names trademarks of their respectiveowners. Type'Help ;'Or'\h'  forHelp. Type'\c'ToClearThe current input statement.mysql>Select@ @port;+--------+| @ @port |+--------+|3306|+--------+1RowinchSet (0.00sec) MySQL>Exitbye[[email protected]~]$ Mysql-uroot-p-P3307enter Password:welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connectionIDIs4Server Version:5.6. *-Log Source distributioncopyright (c) -, ., Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or Itsaffiliates. Other names trademarks of their respectiveowners. Type'Help ;'Or'\h'  forHelp. Type'\c'ToClearThe current input statement.mysql>Select@ @port;+--------+| @ @port |+--------+|3306|+--------+1RowinchSet (0.00sec) MySQL>exitbye# Specify-h+ip reference, at the same time specify-p port [[email protected]~]$ mysql-uroot-p-h127.0.0.1-P3306enter Password:welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connectionIDIs5Server Version:5.6. *-Log Source distributioncopyright (c) -, ., Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or Itsaffiliates. Other names trademarks of their respectiveowners. Type'Help ;'Or'\h'  forHelp. Type'\c'ToClearThe current input statement.mysql>Select@ @port;+--------+| @ @port |+--------+|3306|+--------+1RowinchSet (0.00sec) MySQL>Exitbye[[email protected]~]$ mysql-uroot-p-h127.0.0.1-P3307enter Password:welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connectionIDIs3Server Version:5.6. *-Log Source distributioncopyright (c) -, ., Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or Itsaffiliates. Other names trademarks of their respectiveowners. Type'Help ;'Or'\h'  forHelp. Type'\c'ToClearThe current input statement.mysql>Select@ @port;+--------+| @ @port |+--------+|3307|+--------+1RowinchSet (0.00sec) MySQL>exitbye# Specify-h+localhost reference, at the same time specify-p port [[email protected]~]$ mysql-uroot-p-hlocalhost-P3307enter Password:welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connectionIDIsTenServer Version:5.6. *-Log Source distributioncopyright (c) -, ., Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or Itsaffiliates. Other names trademarks of their respectiveowners. Type'Help ;'Or'\h'  forHelp. Type'\c'ToClearThe current input statement.mysql>Select@ @port;+--------+| @ @port |+--------+|3306|+--------+1RowinchSet (0.00sec) MySQL>Exitbye[[email protected]~]$

View Code

It can be seen from the above results that you can connect to the specified port only if you specify-H+IP and specify the-P port.
Note: The password of our mysql.user table [email protected] ' 127.0.0.1 ' is empty (that is, you do not need to enter a password), but the actual login process requires a password. This is affected by the Skip_name_resolve parameter

Mysql>Show variables like '%skip_name_resolve%';+-------------------+-------+|Variable_name|Value|+-------------------+-------+|Skip_name_resolve| OFF   |+-------------------+-------+1Rowinch Set

View Code

The value of this parameter is off,[email protected] ' 127.0.0.1 ' translates to [email protected] ' localhost ' login, which uses the password defined by [email protected] ' localhost '. You can try to turn on the skip_name_resolve parameter (add skip_name_resolve=1 in profile my.cnf) and log in with a blank password.
Mysql.user table 5th record host= ' localhost ', the user and password fields are empty, that is, localhost can not specify the user, password directly login

[[Email protected] ~]$ Mysqlwelcome to the MySQL Monitor. Commands End With; or \g.your MySQL connectionIDIs -Server Version:5.6. *-Log Source distributioncopyright (c) -, ., Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or Itsaffiliates. Other names trademarks of their respectiveowners. Type'Help ;'Or'\h'  forHelp. Type'\c'ToClearThe current input statement.mysql>Select@ @port;+--------+| @ @port |+--------+|3306|+--------+1RowinchSet (0.00sec) MySQL>

View Code

There are some small details to note. Quote Ivictor paragraph: ' T1 ' @ '% ' contains ' t1 ' @ ' 127.0.0.1 ', if the Skip_name_resolve parameter is turned on, then the password defined in ' t1 ' @ '% ' can be used for ' T1 ' @ ' 127.0.0.1 ' login, If this parameter is not turned on, then ' t1 ' @ ' 127.0.0.1 ' will be converted to ' T1 ' @ ' localhost ' login, at which time the ' T1 ' @ '% ' defined password does not apply.

Mysql.user of User Management module