CODEWITHSUNDEEP
pythonmysqlpymysql
Sophia Feng
Sophia Feng

Reputation: 1003

MySQL Python client got 1045 (28000): Access denied for user 'root'@'localhost'

This error happens to both pymysql and mysql-connector-python. But not to the MySQL shell. MySQL shell is able to login via mysql -u root -p normally.

The error looks like:

In [1]: import mysql.connector

In [2]: mydb = mysql.connector.connect(
   ...:   host="localhost",
   ...:   user="root",
   ...:   passwd="password"
   ...: )

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

Or

In [8]: import pymysql.cursors

In [9]: connection = pymysql.connect(host='localhost',
   ...:                              user='root',
   ...:                              password='password',
   ...:                              db='db',
   ...:                              cursorclass=pymysql.cursors.DictCursor)

...
OperationalError: (1045, "Access denied for user 'root'@'localhost' (using password: YES)")

Python version 3.7, running on Mac OSX 10.14. MySQL version 8.0.16.

Upvotes: 1

Views: 5652

Answers (1)

Sophia Feng
Sophia Feng

Reputation: 1003

I searched all answers on the site. Didn't find a fix, thus want to document this solution to help other people running into similar situation.

The culprit of pymysql stopping working should be upgrading to MySQL 8. MySQL 8 has stronger password authentication than the previous 5.7. Downgrading from 8 to 5.7 is a lot of pain that I don't want to go through. A simple attempt to revert to legacy authentication via editing my.cnf turned out not working.

Then I read this blog: https://blog.csdn.net/zoe9698/article/details/78171465 (it's in Chinese).

Basically it's saying that the user shouldn't be root. A Python connection is considered "remote" thus logging as root is prohibited.

So just create another user and grant all the privileges to it.

mysql> CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost';
Query OK, 0 rows affected (0.00 sec)

Replacing the root in the code with user, the error is gone.

Upvotes: 11

Related Questions