【方案】Python的mysql驱动连不上数据库

python基础

浏览数:326

2019-8-28

先放错误:

>>> import mysql.connector
>>> conn = mysql.connector.connect(user='root', password='zd')
Traceback (most recent call last):
  File "/home/wsx/anaconda3/lib/python3.7/site-packages/mysql/connector/connection_cext.py", line 176, in _open_connection
    self._cmysql.connect(**cnx_kwargs)
_mysql_connector.MySQLInterfaceError: Access denied for user 'root'@'localhost'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/wsx/anaconda3/lib/python3.7/site-packages/mysql/connector/__init__.py", line 172, in connect
    return CMySQLConnection(*args, **kwargs)
  File "/home/wsx/anaconda3/lib/python3.7/site-packages/mysql/connector/connection_cext.py", line 78, in __init__
    self.connect(**kwargs)
  File "/home/wsx/anaconda3/lib/python3.7/site-packages/mysql/connector/abstracts.py", line 731, in connect
    self._open_connection()
  File "/home/wsx/anaconda3/lib/python3.7/site-packages/mysql/connector/connection_cext.py", line 179, in _open_connection
    sqlstate=exc.sqlstate)
mysql.connector.errors.ProgrammingError: 1698 (28000): Access denied for user 'root'@'localhost'

奇怪的是,在mysql本身能够正常地进行登录和命令操作。(刚安装和刚接触mysql,搜索了一下午资料,满满的都是泪。)

问题的原因在于mysql 5.7+(目前大多数linux版本)采用的是auth_socket插件管理root用户,而不是密码的方式。尽管我还按照一些搜索的文章,如# MySQL初始化root用户密码对root进行了密码初始化,而且登录时确实也需要输入密码了,但plugin一项还是auth_socket

如果你也遇到相同的问题,而且下面命令中root的plugin值一下,说明本文的办法可能对你有用。

mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             | *D2001E4688ACE550C2628D84C4DD20872570521D | auth_socket           | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *85047C0F9644A80D212FF24D971538A34A6631D1 | mysql_native_password | localhost |
| wsx              | *D2001E4688ACE550C2628D84C4DD20872570521D | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+

键入语句:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'zd';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             | *D2001E4688ACE550C2628D84C4DD20872570521D | mysql_native_password | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *85047C0F9644A80D212FF24D971538A34A6631D1 | mysql_native_password | localhost |
| wsx              | *D2001E4688ACE550C2628D84C4DD20872570521D | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
5 rows in set (0.00 sec)



然后再试试python连接mysql。

>>> conn = mysql.connector.connect(user='root', password='zd')
>>> conn = mysql.connector.connect(user='wsx', password='zd')
>>> 

root和普通用户都没有报错,成功了!

重要的参考资料:https://stackoverflow.com/questions/17975120/access-denied-for-user-rootlocalhost-using-password-yes-no-privileges/48056403

作者:王诗翔