I am getting error "Access denied for user 'root'@'localhost" when trying to use Connector/Python

Question

I am trying to connect to my local mysql system using python.

I note that I can only login to my mysql system from the console by sudo /usr/bin/mysql -u root -p

I have looked around the web and am very confused by all the suggestions of which none work for me.

Like I tried

mysql> SELECT User, Host, plugin FROM mysql.user;

Which gives me ...

+------------------+-----------+-----------------------+
| User             | Host      | plugin                |
+------------------+-----------+-----------------------+
| root             | localhost | mysql_native_password |
| mysql.session    | localhost | mysql_native_password |
| mysql.sys        | localhost | mysql_native_password |
| debian-sys-maint | localhost | mysql_native_password |
| keith            | localhost | mysql_native_password |
+------------------+-----------+-----------------------+

5 rows in set (0.12 sec)

...

import mysql.connector

cnx = mysql.connector.connect(user='root', password='#########',
                          host='127.0.0.1',
                          database='SurveyData')
cnx.close()

...

I get the following errors

...

MySQLInterfaceError                       Traceback (most recent call last)
~/miniconda3/lib/python3.7/site-packages/mysql/connector  /connection_cext.py in _open_connection(self)
    199         try:
--> 200             self._cmysql.connect(**cnx_kwargs)
    201         except MySQLInterfaceError as exc:

MySQLInterfaceError: Access denied for user 'root'@'localhost'

During handling of the above exception, another exception occurred:

Please don't post passwords and have you tried creating a user with database specific privileges? — DaveStSomeWhere, Sep 22 '19 at 15:11

score 4 · Answer 1 · answered Feb 21 '21 at 12:23

Logging in with the root user can be considered a "petential risk" and this explains the blocking of any connection through this user. So the solution is to create a new user and grant them all privileges.

mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'newpassword';    
mysql> GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';

Christophermp · Answer 2 · 2019-09-24T11:23:01.243

It seemes that you use root@localhost then connecting. Have you tried root@127.0.0.1 ?

Also check out: Access Denied for User 'root'@'localhost' (using password: YES) - No Privileges?

Grant Global Privileges To User Account By Command Line. Open a terminal and run below command.

:~$ mysql -u root -p
Enter password: 
mysql> GRANT SELECT ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

Grant Special Schema Privileges To User Account. Open terminal run below command.

:~$ mysql -u root -p
Enter password: 
mysql> GRANT SELECT,UPDATE,INSERT,DELETE ON SurveyData.* TO 'root'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON SurveyData.* TO 'root'@'localhost';
Query OK, 0 rows affected (0.00 sec)

I use this script for test:

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='SurveyData',
                                         user='root',
                                         password='########')

    if connection.is_connected():
        db_Info = connection.get_server_info()
        print("Connected to MySQL Server version ", db_Info)
        cursor = connection.cursor()
        cursor.execute("select database();")
        record = cursor.fetchone()
        print("Your connected to database: ", record)

except Error as e:
    print("Error while connecting to MySQL", e)
finally:
    if (connection.is_connected()):
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

I tried the following and am giving up trying to format, I put 4 spaces in each line but no deal. mysql> GRANT SELECT ON *.* TO 'root'@'localhost' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL PRIVILEGES ON SurveyData.* TO 'root'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT SELECT,UPDATE,INSERT,DELETE ON SurveyData.* TO 'root'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GRANTS FOR root -> ; ERROR 1141 (42000): There is no such grant defined for user 'root' on host '%' — Keith Sloan, Sep 24 '19 at 10:28
Not sure what fixed things but now accessing ok via user keith — Keith Sloan, Sep 24 '19 at 11:13

I am getting error "Access denied for user 'root'@'localhost" when trying to use Connector/Python

2 Answers2