In-depth analysis of MySQL ERROR 1045 (28000)

These days after MySQL new user, there is an access denied problem, error code 1045 (28000). Search on the internet for a long time, found a lot of solutions, but unfortunately there are so many ways to solve the problem. Although the error code appears 28000 Many people have encountered, but the reasons are also different, There is no information in the Mysql.user table, some root users do not have a password (that is, no password login), while using mysql-5.6.19, Mysql.user has user information, the root user does not have a password, the method is the root user entered a blank password, login success. Use the root user to create tests with test, the password is test, and the statement is as follows:

Grant all onlogdb.* to test identified by ' test ';

At the command line input mysql-u test–p, enter the password test, the following error message appears, detailed the error message many people have encountered 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, password test, the only difference is that the specified test login host is localhost, as follows:

Grant all onlogdb.* to [email protected] ' localhost ' identified by ' test ';

Again, use the test user to log on to MySQL successfully, as follows:

Log in to MySQL using the root user, and see the user information in the Users table as follows, you can see that there are two test users, the host field is% and localhost, which is the two users created earlier.

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:

\

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.

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.

Based on the rules described above, the example shows why the [email protected] User must be created to successfully log on locally. The results of sorting the user table are as follows:

When [email protected] is not created, the table does not contain records for the first row. When the user test logs on, it matches the record on line fourth: The host is localhost,user empty, because user is null to match any user, and then to verify that the password failed to log on successfully. Either log in without a password, or match to line fourth, but the authentication password succeeds, but the anonymous user only has permissions to the INFORMATION_SCHEMA and test databases and fails with other databases, as shown below:

        summarize that when a 28000 error occurs, first look at whether there is data in user and whether there are anonymous users. Create [email protected] If an anonymous user exists, or you can delete the anonymous user.