Reasons for MySQL ERROR 1045 and how to solve it

When entering mysql -u root –p on the command line, entering the password, or connecting to the database through a tool, the following error message often appears. I believe many people have this error message. I have encountered this when using MySQL.

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

You can usually find solutions online

1. Stop the service

Stop the MySQL service;

 # windows
 net stop mysql 
 # linux
 service mysqld stop

2. Skip verification

Modify the my.ini configuration file in the MySQL installation directory so that Skip the permission check when logging in;

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

3. Change password

Start the MySQL service and log in to MySQL. You will be prompted to enter a password. Enter any password and press Enter to enter MySQL.

#登录mysql
mysql -u root -p

Then modify the password of the root user through SQL statements;

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

4. Restart the service

Delete or add the skip permission statement added to the my.ini file# Note.

Restart the service and log in with the modified password.

Cause Analysis

Use the root user to log in to MySQL and check the user information in the user table as follows. You can find that the host fields are % and localhost respectively.

mysql>select host,user,password from user;

In MySQL, '%' means that you can log in to the MySQL database on any host, so why do you need to explicitly create a user whose login host is localhost?

This involves the initialization user, anonymous user and connection verification strategy during MySQL installation.

Let’s conduct an in-depth analysis below.

When installing MySQL, some users will be initialized by default, such as the root user, and users whose host field is localhost and user field is empty.

The user whose User field is empty is an anonymous user, and the user's password is also empty. Anyone can use an anonymous user to log in to the MySQL database, but the things that can be done are limited, such as on the command line Directly enter mysql to log in, you can check which databases the anonymous user has permissions:

mysql>select current_user;

mysql>show databases;

You can find through the above picture that the anonymous user Only has permissions for information_schema and test database.

How do anonymous users affect the login of other users and cause the 28000 error?

When trying to connect to a MySQL database, the database decides whether to accept the connection request based on the provided identity and password. The identity consists of two parts: the user name and the client host (that is, the host where the mysql command is entered).

Since the % in the host field matches any host or the host field contains wildcards, multiple matching rows may appear, and the server must decide which one to match. The server reads the data in the user table into memory, according to the host and user fields to sort the rows.

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

When the client attempts to connect, the server looks for the sorted rows and uses the first one that matches the client's host and username. After a matching row is found, Verify whether the passwords are consistent. If they are consistent, the login is successful.

If the user field is empty, it means that it can match any user.

Attention here:

user is empty, that is, any user. When logging in to localhost without a password, the anonymous user only has permissions for the information_schema and test database. Use other database, resulting in failure.

If the user is root, the password is displayed as empty, or the host field is displayed as non-’%', the default matching order will be followed, or a password error or prompt will be displayed. is ERROR 1045.

The picture below has been modified and is for reference only. For example, in the first sequence, if the host is localhost or the 127 loopback address, a 1045 error will occur.

SO Solution

mysql> select host,user,password from user order by host desc,user desc;
## 将排序序列中最前面的用户修改host为'%'
mysql> update user set host = '%' where host = 'oracle' and user = 'root';
mysql> flush privileges;

The above is the detailed content of Reasons for MySQL ERROR 1045 and how to solve it. For more information, please follow other related articles on the PHP Chinese website!