Python连接MySQL失败报错：不支持caching_sha2_password认证插件

问题描述 #

你碰到的这个错误，本质是MySQL 8.0及以上版本默认用caching_sha2_password作为用户认证插件，但你当前安装的mysql.connector版本不支持这个新插件。你的代码和报错详情如下：

你的代码：

import mysql.connector
connection = mysql.connector.connect(host = "localhost", user = "root",passwd = "268455")
print(connection)

报错信息：

D:\Python>"C:/Users/Abdul Muqeet/AppData/Local/Programs/Python/Python38/python.exe" "d:/Python/exmaple (mySQL).py"
Traceback (most recent call last):
  File "d:/Python/exmaple (mySQL).py", line 2, in <module>
    connection = mysql.connector.connect(host = "localhost", user = "root",passwd = "268455")
  File "C:\Users\Abdul Muqeet\AppData\Roaming\Python\Python38\site-packages\mysql\connector\__init__.py", line 179, in connect
    return MySQLConnection(*args, **kwargs)
  File "C:\Users\Abdul Muqeet\AppData\Roaming\Python\Python38\site-packages\mysql\connector\connection.py", line 95, in __init__
    self.connect(**kwargs)
  File "C:\Users\Abdul Muqeet\AppData\Roaming\Python\Python38\site-packages\mysql\connector\abstracts.py", line 716, in connect
    self._open_connection()
  File "C:\Users\Abdul Muqeet\AppData\Roaming\Python\Python38\site-packages\mysql\connector\connection.py", line 208, in _open_connection
    self._do_auth(self._user, self._password,
  File "C:\Users\Abdul Muqeet\AppData\Roaming\Python\Python38\site-packages\mysql\connector\connection.py", line 137, in _do_auth
    packet = self._protocol.make_auth(
  File "C:\Users\Abdul Muqeet\AppData\Roaming\Python\Python38\site-packages\mysql\connector\protocol.py", line 99, in make_auth
    packet += self._auth_response(client_flags, username, password,
  File "C:\Users\Abdul Muqeet\AppData\Roaming\Python\Python38\site-packages\mysql\connector\protocol.py", line 58, in _auth_response
    auth = get_auth_plugin(auth_plugin)(
  File "C:\Users\Abdul Muqeet\AppData\Roaming\Python\Python38\site-packages\mysql\connector\authentication.py", line 190, in get_auth_plugin
    raise errors.NotSupportedError(
mysql.connector.errors.NotSupportedError: Authentication plugin 'caching_sha2_password' is not supported

解决方案 #

这里有两种靠谱的解决办法，你可以根据自己的情况选：

方法1：升级mysql.connector到支持新插件的版本

MySQL官方维护的mysql-connector-python包（注意不是旧的mysql.connector）已经适配了caching_sha2_password，直接执行下面的命令升级就行：

pip install --upgrade mysql-connector-python

升级完成后再跑你的代码，应该就能正常连接了。

方法2：修改MySQL用户的认证方式为旧版兼容模式

如果你不想升级驱动，也可以把root用户的认证方式改回旧的mysql_native_password：

1. 打开MySQL命令行客户端，输入密码登录：

   mysql -u root -p
   

2. 执行SQL修改用户认证（把268455换成你的实际密码）：

   ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '268455';
   

3. 刷新权限让修改生效：

   FLUSH PRIVILEGES;
   

改完后关掉MySQL命令行，再运行你的Python代码就能连上了。

额外提醒 #

• 先确认你的MySQL服务是处于运行状态的，不然也会连接失败。
• 如果是远程连接MySQL，还要注意用户的主机权限（比如要用'root'@'%'而非'root'@'localhost'）。

内容的提问来源于stack exchange，提问作者Abdul Muqeet