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

先放错误：

>>> 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 "", line 1, in

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和普通用户都没有报错，成功了！