【方案】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 "<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