Accessing MySQL from Python 3: Access denied for user

Question

I tried to access MySQL from Python 3.3 with the following methods:

import mysql.connector

config = {
  'user': '###',
  'password': '******',
  'host': '##.##.#.##',
  'database': '########',
  'port': '####',
  'raise_on_warnings': True,
}

cnx = mysql.connector.connect(**config)

cnx.close()

but when I ran the above code, I got this error:

 mysql.connector.errors.ProgrammingError: 1045 (28000): Access denied for user '###'@'##.##.#.###' (using password: YES);

Why am I getting this error, and how can I fix it?

Answer 1

I would like to add one more possibilities for the cause: version mismatch between your MySQL and mysql-connector-python. This problem happened to me when I was using MySQL 5.6.24-72.2-log and mysql-connector-python==8.0.13. Downgrading to mysql-connector-python==8.0.5 resolved the issue. Though I was using Python 2, hopefully this would provide more clues for debugging.

Answer 2

You need to grant priviliges to your root user from MySQL, as your exception message said. Firstly, you login to your MySQL from the command line or from your favorite MySQL client (such as MySQL workbench.) Then you grant permission to your user (root, in this case). This link describes how to login to MySQL from the console, for example,

mysql -h localhost -u root -p

then you need to grant priviliges to user root, for example,

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';

which gives full permissions for all databases from any host (%) to root using a password. You need to be careful if this server is accessible from internet while granting permissions.

Answer 3

I was having this problem when I was trying to transfer data from a python script to a MySQL database on a Raspberry Pi.

When you initially install MySQL it sets you up as the user 'root' with whatever password you enter. If you changed the security settings afterwards to have strong passwords (like I did) your password must have uppercase, lowercase, digit and special characters.

This may be what's throwing an error when you try to connect to the database.

To remedy this:

Log in to MySQL: sudo mysql --user=root

Delete the root user: DROP USER 'root'@'localhost'; Create a new user: CREATE USER 'root'@'localhost' IDENTIFIED BY 'password'; Give the user all permissions: GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;

Answer 4

For me it was fixing the right port (changed the default while installing because it was already used).

Answer 5

This means MySQL has denied the connection for one of these reasons:

1. The username is wrong.
2. The password is wrong.
3. The IP address/hostname you're connecting from is not one that MySQL has been configured to allow for that user.
4. The user does not have access to the specified database.

MySQL's users are actually a combination of a user and a pattern for where you can connect from. e.g. 'user'@'10.0.23.99' or 'test'@'%' or 'fred'@'196.168.123.%' or 'mary'@'%.example.com'

Using a MySQL client, check the "user" table in the "mysql" database to see what users MySQL knows about and what hosts the can connect from.

e.g.

mysql> SELECT user, host FROM mysql.user WHERE user = 'youruser';

Check the MySQL documentation for how to add users. See also how to GRANT access to different tables etc.

http://dev.mysql.com/doc/refman/5.5/en/adding-users.html

http://dev.mysql.com/doc/refman/5.5/en/grant.html

Given that your anonymised IP addresses look the same I suppose you might be running your Python code on the same machine as MySQL is running on. If so, you might find that you are using the server's external IP address whereas perhaps MySQL is configured to accept connections only from localhost/127.0.0.1. If so, try changing the IP address you're connecting to. Also, usually if you're running on the database server, you can connect via a Unix domain socket instead of via TCP/IP. To do this, just leave out the host and port parameters.

Answer 6

I was getting this error because I had quotes in my config file.

[mysql]
username: 'uuuu'
password: 'pppp'
host: 'localhost'
database: 'dddd'

gave me the error. However, removing the quotes worked:

[mysql]
username: uuuu
password: pppp
host: localhost
database: dddd

Answer 7

I was having lots of trouble with this same task. Here's what worked for me:

I had to initialize a new database using the legacy password encryption option (compatible with MySQL 5.x):

Database initialization options screenshot

Once I did that, everything worked fine. I got that suggestion from this article:

https://tableplus.com/blog/2018/07/failed-to-load-caching-sha2-password-authentication-plugin-solved.html

And here are some specs:

• I'm using Python 3.7
• I have MySQL 8.0.17 and mysql-connector-python 2.0.4 installed

Answer 8

try the same with mysql command line client:

mysql -h ##.##.#.## -P #### -u ### -p ########