尝试使用连接器/Python 时出现错误“用户'root'@'localhost 的访问被拒绝”

Question

我正在尝试使用 python 连接到我的本地 mysql 系统。我注意到我只能通过 sudo /usr/bin/mysql -u root -p 从控制台登录到我的 mysql 系统我环顾了网络，对所有对我都不起作用的建议感到非常困惑。就像我试过的mysql> SELECT User, Host, plugin FROM mysql.user;这给了我...+------------------+-----------+-----------------------+| 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.connectorcnx = mysql.connector.connect(user='root', password='#########',                          host='127.0.0.1',                          database='SurveyData')cnx.close()...我收到以下错误...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:

Answer 1

使用 root 用户登录可以被视为“潜在风险”，这解释了通过该用户进行的任何连接的阻塞。因此解决方案是创建一个新用户并授予他们所有权限。

mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'newpassword';    

mysql> GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';

Answer 2

通过命令行向用户帐户授予全局权限。打开终端并运行以下命令。

:~$ mysql -u root -p

Enter password: 

mysql> GRANT SELECT ON *.* TO 'root'@'localhost' WITH GRANT OPTION;

Query OK, 0 rows affected (0.00 sec)

向用户帐户授予特殊架构特权。打开终端运行下面的命令。

:~$ 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)

我使用这个脚本进行测试：

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")