In-depth analysis of MySQL ERROR 1045 (28000) and mysql28000

In-depth analysis of MySQL ERROR 1045 (28000) and mysql28000

After a user is created in MySQL over the past few days, the access request is denied. The ERROR code is ERROR 1045 (28000 ). I searched the internet for a long time and found many solutions, but unfortunately none of them can solve the problem. Although many people have encountered the error code 28000, the reasons are also different, some of which are mysql. there is no information in the user table, some are root users do not have a password (then do not use a password to log on), and when using mysql-5.6.19, mysql. the user has user information, and the root user does not have a password. The root user enters an empty password during logon, And the logon is successful. Use the root user to create a test with the password test. The statement is as follows:

grant all onlogdb.* to test identified by ‘test’;

Enter mysql-u test-p in the command line and password test. The following error message is displayed. For details, many users have encountered this error when using MySQL.

ERROR 1045 (28000):Access denied for user 'test'@'localhost' (using password: YES)

The solution is to use the root user to create the user test and password test. The only difference is that the host that the test log on to is specified as localhost, as shown below:

grant all onlogdb.* to test@'localhost' identified by 'test';

Log on to MySQL again using the test user, as shown below:

Log on to MySQL as the root user and view the user information in the user table as follows. We can find that there are two test users. The host fields are % and localhost, which are the two users created earlier.

In MySQL, "%" indicates that you can log on to the MySQL database on any host. Why do we need to explicitly create a user whose login host is localhost? This involves initialization users, anonymous users, and connection verification policies during MySQL installation. The following is an in-depth analysis.

During MySQL installation, some users will be initialized by default, such as the root user, and users whose host field is localhost and user field is empty. If the User field is empty, the User is an anonymous User, and the password of the User is empty. Anyone can log on to the MySQL database using an anonymous User, but what can be done is limited, for example, you can directly enter mysql in the command line to log on and check which databases anonymous users have permissions on:

\

The preceding figure shows that anonymous users only have permissions on information_schema and test Databases. How does an anonymous user affect the login of other users and the 28000 error occurs? When attempting to connect to the MySQL database, the database determines whether to accept the connection request based on the provided identity and password. The identity consists of the user name and client host (that is, the host that inputs the mysql command ). Because the % field in the host field matches any host or the host field contains wildcards, multiple matching rows may appear. The server must decide which one to match. The solution is as follows:

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

When the client tries to connect, the server searches for sorted rows and uses the first row that matches the client host and user name. If the user field is null, it can match any user.

After the matching line is found, verify that the password is consistent. If the password is consistent, the logon is successful.

Based on the rules described above, the example shows why the user test @ localhost must be created to successfully log on to the local machine. Shows the sorting result of the user table:

When test @ localhost is not created, the table does not contain records of the first row. When the user test logs on, it will match the record in the fourth line: host is localhost, user is empty, because the user is empty, it can match any user, and then verify that the password fails to log on. Or, if you do not use the password to log on, the password is successfully verified. However, anonymous users only have permissions on the information_schema and test Databases and will fail to use other databases, as shown below:

To sum up, when a 28000 error occurs, first check whether data exists in the user and whether there are anonymous users. If an anonymous user exists, create userName @ localhost or delete the anonymous user.

Failed to log on to MYSQL. ERROR 1045 (28000)

Command Error
It should be mysql-u root-p123456

You have an additional symbol-
 
Mysql ERROR 1045 (28000)

You log on to the MySQL server through an anonymous user. You are not authorized to create a database.
Use the root account to log on. Mysql core technologies and best practices