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

created at 07-06-2021 views: 58

Connect Mysql with Python

import mysql.connector
conn = mysql.connector.connect(
                  user='root', 
                  password='password',
                  database='test')

When connecting to the database, Mysql reported an error:

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

So the same error occurred when logging in to mysql under cmd, so I came to the conclusion: it is not a code problem, it may be a configuration environment problem
Found a solution after searching for a while
(The environment is window10, MySQL Server 8.0)

Solution:

1. Edit the my.ini file

The directory of my.ini configuration file has changed in versions after 5.7
Put it in C:\ProgramData\MySQL\MySQL Server 8.0
After opening with Notepad, add skip-grant-tables under [mysqld], save and exit

2. Restart MySQL

Enter the cmd command line and enter successively

net stop mysql
net start mysql

If access is denied, please run cmd as an administrator.

3. Login

At this time, enter mysql -u root -p in cmd and you don't need a password to log in. If password appears, press Enter to enter
But the operation will be restricted because there is no permission

4. Reset password

4.1. Enter the mysql database:

mysql>use mysql;

4.2. Set a new password for the root user

mysql> update user set password=password("Write a new password here") where user="root"

4.3. Refresh the database

mysql>flush privileges;

4.4. Exit mysql

mysql> quit

5. Re-edit my.ini

Remove the skip-grant-tables just added, and restart mysql

Questions and thoughts:

Why can the problem be solved after resetting the password?
I searched a lot on the Internet and almost found only a solution
The following main reasons have been found for reference only:
1. There is no information in the mysql.user table
2. The root user has no password

created at:07-06-2021
edited at: 07-06-2021: