MySQL about any user login and resolve ERROR1405

1. Stop the service: Stop the MySQL service;

# windows net stop mysql  # linux service mysqld stop

• 1
• 2
• 3
• 4

2. Skip verification: Modify the My.ini configuration file in the MySQL installation directory so that you skip permission check at login;

#到mysql根目录找到mysql配置文件  vim my.ini #在my.ini，[mysqld]下添加一行，使其登录时跳过权限检查 skip_grant_tables

• 1
• 2
• 3
• 4

3. Change Password: Start the MySQL service, log in to MySQL, at this time prompted to enter a password, enter any password return to enter MySQL.

#登录mysqlmysql -u root -p

• 1
• 2

The root user's password is then modified by the SQL statement;

#将数据库切换至mysql库mysql> USE mysql;#修改密码mysql> UPDATE user SET password=PASSWORD(‘newpasswd’)WHERE user=’root’; #刷新MySQL权限相关的表mysql> flush privileges;mysql> exit;

• 1
• 2
• 3
• 4
• 5
• 6
• 7

4. Restart Service: Remove or add a # comment to the SKIP permission statement added to the My.ini file. Restart the service and log in with the modified password.

**

Cause Analysis:

**
Log in to MySQL with the root user and look at the user information in the users table below, and you can see that the host fields are% and localhost, respectively.

mysql>select host,user,password from user;

• 1

In MySQL,% means that you can log in to MySQL database on any host, why do you need to explicitly create a user with login host localhost?
This involves an in-depth analysis of the initial user, anonymous user, and connection validation policies for MySQL installation.

When you install MySQL, some users are initialized by default, such as the root user, and the user with the Localhost,user field empty. The user field is empty users are anonymous users, the user's password is also empty, anyone can use anonymous users to log on to the MySQL database, but can do things are limited, such as the command line to enter the MySQL login directly, you can see which databases anonymous users have permissions:

mysql>select current_user;

• 1

mysql>show databases;

• 1

As you can see from the image above, anonymous users only have permissions to the INFORMATION_SCHEMA and test databases.
And how do anonymous users affect other users to log in, resulting in a 28000 error?
When attempting to connect to a MySQL database, the database determines whether the connection request is accepted based on the identity and password provided, and the identity consists of two parts: the user name and the client host (that is, the host that entered the MySQL command).
Because the% match in the host field contains any hosts or host fields that contain wildcards, multiple matching rows may occur, and the server must decide which one to match, and the solution is as follows:

The server reads the data from the user table into memory and sorts the rows by the host and user fields.

mysql>select host,user,password from user order by host desc,user desc;

• 1

When the client tries to connect, the server looks for the sorted row and uses the first row that matches the client host and user name, and the user word blank indicates that it can match any user.

After the matching rows are found, verify that the passwords are consistent and that the login succeeds if they are consistent.
The main concern here is the fourth line, user is empty, that is, any user, without password logon to localhost, anonymous users only have permissions to the INFORMATION_SCHEMA and test databases, when using other databases, resulting in failure.

So solution

select host,user,password from user order by host desc,user desc;mysql> update user set host = ‘%‘ where host = ‘oracle‘ and user = ‘root‘;mysql> flush privileges;

• 1
• 2
• 3

MySQL about any user login and resolve ERROR1405