MySQL account issues

3 host WEB01,WEB02,WEB03, the master from, and one of them is not even on the Masters.

In the host WEB02 MySQL database to build an account (rep), Web01 Login failed, WEB03 can log on normally.

Create an account grant replication slave on * * to [e-mail protected] ' 192.168.190.% ' identified by ' rep111 ';

A. Login success in WEB03

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/88/58/wKioL1fx-6qivgrQAABWXOPYzR4442.png "title=" Qq20161003143238.png "alt=" Wkiol1fx-6qivgrqaabwxopyzr4442.png "/>

B. Login failure in WEB01

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/88/5B/wKiom1fx-_mxJF_GAAAjvuQJ32U168.png "title=" Qq20161003143415.png "alt=" Wkiom1fx-_mxjf_gaaajvuqj32u168.png "/>

However, no user password can be logged on successfully in WEB01.

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M02/88/5C/wKiom1fx_ISDMrrBAABM8bd_dPc862.png "title=" Qq20161003143600.png "alt=" Wkiom1fx_isdmrrbaabm8bd_dpc862.png "/>

Consult the documentation to see which user you are using using User () and current_user () two functions

The user () function returns the username and hostname that you specified when the client logged on.

The Current_User () function returns which user in the MySQL Use authorization table authenticates your login request.

Then check in WEB01 and web03 after logging in.

Web01

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/88/5C/wKiom1fx_sHiFIjkAAAXm3-_gB0885.png "title=" Qq20161003144151.png "alt=" Wkiom1fx_shifijkaaaxm3-_gb0885.png "/>

Web03

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/88/58/wKioL1fx_ougl5ZOAAAamKjifOA514.png "title=" Qq20161003144306.png "alt=" Wkiol1fx_ougl5zoaaaamkjifoa514.png "/>

You can tell by the above two graphs. WEB01 is logged in through an empty account, which explains why you can log in without a password, but why not log in with a password??

Second, it may be necessary to understand how MySQL user identity authentication.

A. When a user requests a login from a client, MySQL compares the entries in the authorization table with the entries provided by the client, including the user's user name, password, and host. The host field in the authorization table can be matched using wildcards as a pattern, such as test.example.com,%.example.com,%.com, and% can all match test.example.com. The user field in the authorization table does not allow pattern matching, but you can have a null character that represents an anonymous user, and an empty string can match all user names, just like a wildcard character. When host and user in the user table have multiple values that match the host and username provided by the client, MySQL reads the user table into memory and, according to a certain sequence of rules, authenticates the client by the entry of the first matching client user name and host name read by the collation.

B, collation: For the host field, sorted by the exact degree of the match, the more accurate the ordering, for example, when matching test.example.com this host,%.example.com than%.com more accurate, and test.example.com than%. example.com more accurate. For the user field, a non-empty string user name is sorted more forward than an empty string that matches the user name. Both the user and host fields have multiple matching values, and MySQL uses the host name to sort the first entry, and then selects the entry with the user name sorted before the host name field is the same. Therefore, if both the user and host fields have multiple matching values, the entries with the most exact match for the host name are authenticated by the user.

Let's take a look at the host, account, and information on the WEB02.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/88/5C/wKiom1fyAiLzYLsaAAB3KFTKo6A715.png "title=" Qq20161003145626.png "alt=" Wkiom1fyailzylsaaab3kftko6a715.png "/>

Through the MySQL user authentication rule, we conclude that the WEB01 login is using the 1th rule, and we do not know what we think of 2nd. This also explains why it is possible to login without a password.

Iii. Workaround : Delete anonymous users

1. Log in with root privileges

2, mysql> select Host,user,password from Mysql.user;

3, mysql> delete from mysql.user where user= ';

4, mysql> flush privileges;

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/88/58/wKioL1fyBaHA1yxvAABwdVmz2fE602.png "title=" Qq20161003151528.png "alt=" Wkiol1fybaha1yxvaabwdvmz2fe602.png "/>

Someone can ask why root seems to have no such situation, it is because

Because the mysql_install_db script generates a ' root ' @ ' localhost ' account in the authorization table. Similarly, when using root to log in to MySQL, ' root ' @ ' localhost ' and ' @ ' localhost ' can match the logged-in account, but according to the collation, the hostname is the same, and the user name is not empty string first, so ' root ' @ ' localhost ' This entry is sorted more forward. Using root to log on locally is not obscured by anonymous users. Perhaps there are people who want to ask you in the WEB02 why there will be Web01 anonymous account, and then think carefully, the original web02 is from the WEB01 virtual machine copied over.

This article is from the "59090939" blog, please be sure to keep this source http://59090939.blog.51cto.com/6338052/1858567

MySQL account issues