Solve the MySQL login 1045 problem under centos

Since the entire application needs to be deployed under centos, database operations are naturally indispensable. But many people may encounter some problems, such as successfully creating a user but being unable to log in.

There are generally two reasons why you cannot log in. First, the remote access port is not open, and the second reason is that the password is wrong (it is very strange, the password we logged in with is obviously correct, but it still prompts that the password is wrong. I don’t know why this is, it may be encoding or something like that)

The problem of the remote access port not being opened is relatively easy to solve. If you are using a cloud server, you can configure the security group in the console and open the corresponding port.

Let me talk about the second mistake here, which is more tricky.

The second situation often displays an error message: 1045 Access denied for user 'root'@'%' (using password: YES).

I wrote down the whole process roughly. My operating system is centos7.4 and MySQL version is 5.7.

First log in to mysql, mine is the root user. The login command is

mysql -u root -p

After entering the login password, the window changes as shown below:

First, let's take a look at which users are in the system, their specific permissions, and specific commands:

View users:

SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

View the permissions of a specific user:

show grants for 'root'@'%';

The window changes as shown below:

Next, I will create a user so that the user can remotely access the database and operate it. The specific commands are as follows:

create user zhangsanidentified by 'zhangsan.CQU.2020.lisi';
grant all privileges on *.* to zhangsan@'%' identified by 'zhangsan.CQU.2020.lisi';

Or just one command:

grant all privileges on *.* to 'zhangsan'@'%' identified by 'zhangsan.CQU.2020.lisi' with grant option;

After the operation is completed, remember to refresh, otherwise it may not take effect. The command is as follows

flush privileges;

If you check the users again at this time, you will find that there is one more user, which is the user we just created.

Enter exit to exit MySQL, and then try to log in with the zhangsan account.

The problem has been solved, but I just can't log in. Let's solve this problem next.

First, find your own MySQL my.cnf file. (Mine is in the etc folder, different people may have different files, just use Linux commands to search by condition.)

After opening the file (vi my.cnf), enter the editing mode (a), find the mysqld keyword, add skip-grant-tables under mysqld, save and exit (first esc, then :wq), as shown in the figure:

Just remove the comments.

Then restart mysql with the following command:

service mysqld restart

The next step is to log in again and change your password.

When prompted to enter a password, just press enter to skip password verification and log in successfully. Next, enter the mysql database and change the password. The command is as follows.

use mysql

To change your password:

update user set authentication_string=password("zhangsan.CQU.2020.lisi") where user="zhangsan";

As shown in the figure:

Then exit mysql, change the my.cnf file you just modified back, and restart mysql.

You can see that the login has been successful and the remote connection can also be successful, so I won’t demonstrate it. If the remote login is still unsuccessful, check the port settings to see if remote access is enabled.

Summarize

The above is the editor's introduction to solving the MySQL login 1045 problem under centos. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!
If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!