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

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