无法使用mysql.connector：caching_sha2_password认证插件不支持求助

问题背景 #

正在学习Flask教程，在第9课创建MySQL表时遇到问题。环境为Mac系统 + VSCode虚拟环境，已安装以下MySQL相关包：

mysql-connector==2.2.9
mysql-connector-python==8.2.0
mysql-connector-python-rf==2.2.2

完全按照教程操作后，运行create_db.py出现报错：

Traceback (most recent call last):
  File "/Users/nikitagudkov/Documents/Dev/flasker/create_db.py", line 3, in <module>
    mydb = mysql.connector.connect(
  File "/Users/nikitagudkov/Documents/Dev/flasker/virt/lib/python3.9/site-packages/mysql/connector/__init__.py", line 179, in connect
    return MySQLConnection(*args, **kwargs)
  File "/Users/nikitagudkov/Documents/Dev/flasker/virt/lib/python3.9/site-packages/mysql/connector/connection.py", line 95, in __init__
    self.connect(**kwargs)
  File "/Users/nikitagudkov/Documents/Dev/flasker/virt/lib/python3.9/site-packages/mysql/connector/abstracts.py", line 716, in connect
    self._open_connection()
  File "/Users/nikitagudkov/Documents/Dev/flasker/virt/lib/python3.9/site-packages/mysql/connector/connection.py", line 208, in _open_connection
    self._do_auth(self._user, self._password,
  File "/Users/nikitagudkov/Documents/Dev/flasker/virt/lib/python3.9/site-packages/mysql/connector/connection.py", line 137, in _do_auth
    packet = self._protocol.make_auth(
  File "/Users/nikitagudkov/Documents/Dev/flasker/virt/lib/python3.9/site-packages/mysql/connector/protocol.py", line 99, in make_auth
    packet += self._auth_response(client_flags, username, password,
  File "/Users/nikitagudkov/Documents/Dev/flasker/virt/lib/python3.9/site-packages/mysql/connector/protocol.py", line 58, in _auth_response
    auth = get_auth_plugin(auth_plugin)(
  File "/Users/nikitagudkov/Documents/Dev/flasker/virt/lib/python3.9/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

涉及代码文件 #

create_db.py代码：

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="my_passord"
)

my_cursor = mydb.cursor()
my_cursor.execute("CRETE DATABASE our_users")
my_cursor.execute("SHOW DATABASES")

for db in my_cursor:
    print(db)

requirements.txt内容：

blinker==1.6.3
click==8.1.7
Flask==3.0.0
Flask-SQLAlchemy==3.1.1
Flask-WTF==1.2.1
importlib-metadata==6.8.0
itsdangerous==2.1.2
Jinja2==3.1.2
MarkupSafe==2.1.3
mysql-connector==2.2.9
mysql-connector-python==8.2.0
mysql-connector-python-rf==2.2.2
protobuf==4.21.12
SQLAlchemy==2.0.22
typing_extensions==4.8.0
Werkzeug==3.0.0
WTForms==3.1.0
zipp==3.17.0

解决建议 #

1. 清理冲突的MySQL连接器包 #

同时安装多个版本的MySQL连接器会导致依赖冲突，保留最新的mysql-connector-python==8.2.0即可：

pip uninstall mysql-connector mysql-connector-python-rf
pip install --force-reinstall mysql-connector-python==8.2.0

2. 解决认证插件不兼容问题 #

MySQL 8.0+默认使用caching_sha2_password认证插件，可通过两种方式解决：

• 方式一：修改连接代码
  在connect方法中指定兼容的认证插件：

  mydb = mysql.connector.connect(
      host="localhost",
      user="root",
      passwd="my_passord",
      auth_plugin='mysql_native_password'
  )
  

• 方式二：修改MySQL用户认证插件
  登录MySQL终端后执行以下SQL：

  ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的root密码';
  FLUSH PRIVILEGES;
  

3. 修正代码拼写错误 #

create_db.py中的SQL语句存在拼写错误：CRETE DATABASE应改为CREATE DATABASE，否则会导致数据库创建失败。

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