MySQL：处理 alchemy 连接 mysql 报 Authentication plugin 'caching_sha2_password' is not supported 错误

1. 环境

  • windows 10；
  • MySQL 8.0.20；
  • Alchemy 20.5；
  • Python 3.7.5；

2. 问题

  • alchemy 连接 mysql 数据库时，报 Authentication plugin 'caching_sha2_password' is not supported 错误；
  • 报错内容：

Traceback (most recent call last):
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\engine\base.py", line 2339, in _wrap_pool_connect
return fn()
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 364, in connect
return _ConnectionFairy._checkout(self)
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 778, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 495, in checkout
rec = pool._do_get()
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\impl.py", line 140, in _do_get
self.dec_overflow()
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\util\langhelpers.py", line 69, in exit
exc_value, with_traceback=exc_tb,
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\util\compat.py", line 178, in raise
raise exception
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\impl.py", line 137, in _do_get
return self._create_connection()
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 309, in _create_connection
return _ConnectionRecord(self)
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 440, in init
self.__connect(first_connect_check=True)
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 661, in _connect
pool.logger.debug("Error on connect(): %s", e)
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\util\langhelpers.py", line 69, in exit
exc_value, with_traceback=exc_tb,
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\util\compat.py", line 178, in raise
raise exception
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 656, in __connect
connection = pool.invoke_creator(self)
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\engine\strategies.py", line 114, in connect
return dialect.connect(*cargs, *cparams)
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\engine\default.py", line 493, in connect
return self.dbapi.connect(cargs, **cparams)
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector_init.py", line 179, in connect
return MySQLConnection(*args, kwargs)
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\connection.py", line 95, in init
self.connect(kwargs)
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\abstracts.py", line 716, in connect
self._open_connection()
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\connection.py", line 210, in _open_connection
self._ssl)
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\connection.py", line 142, in _do_auth
auth_plugin=self._auth_plugin)
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\protocol.py", line 102, in make_auth
auth_data, ssl_enabled)
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\protocol.py", line 58, in _auth_response
auth = get_auth_plugin(auth_plugin)(
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\authentication.py", line 191, in get_auth_plugin
"Authentication plugin '{0}' is not supported".format(plugin_name))
mysql.connector.errors.NotSupportedError: Authentication plugin 'caching_sha2_password' is not supported

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "C:\Users\Administrator\Desktop-\magic_method.py", line 748, in <module>
Base.metadata.create_all(engine)
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\sql\schema.py", line 4547, in create_all
ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\engine\base.py", line 2097, in _run_visitor
with self._optional_conn_ctx_manager(connection) as conn:
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\contextlib.py", line 112, in enter
return next(self.gen)
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\engine\base.py", line 2089, in _optional_conn_ctx_manager
with self._contextual_connect() as conn:
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\engine\base.py", line 2305, in _contextual_connect
self._wrap_pool_connect(self.pool.connect, None),
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\engine\base.py", line 2343, in wrap_pool_connect
e, dialect, self
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\engine\base.py", line 1585, in handle_dbapi_exception_noconnection
sqlalchemy_exception, with_traceback=exc_info[2], from=e
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\util\compat.py", line 178, in raise
raise exception
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\engine\base.py", line 2339, in _wrap_pool_connect
return fn()
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 364, in connect
return _ConnectionFairy._checkout(self)
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 778, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 495, in checkout
rec = pool._do_get()
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\impl.py", line 140, in _do_get
self.dec_overflow()
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\util\langhelpers.py", line 69, in exit
exc_value, with_traceback=exc_tb,
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\util\compat.py", line 178, in raise
raise exception
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\impl.py", line 137, in _do_get
return self._create_connection()
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 309, in _create_connection
return _ConnectionRecord(self)
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 440, in init
self.__connect(first_connect_check=True)
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 661, in _connect
pool.logger.debug("Error on connect(): %s", e)
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\util\langhelpers.py", line 69, in exit
exc_value, with_traceback=exc_tb,
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\util\compat.py", line 178, in raise
raise exception
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 656, in __connect
connection = pool.invoke_creator(self)
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\engine\strategies.py", line 114, in connect
return dialect.connect(*cargs, *cparams)
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\engine\default.py", line 493, in connect
return self.dbapi.connect(cargs, **cparams)
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector_init.py", line 179, in connect
return MySQLConnection(*args, kwargs)
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\connection.py", line 95, in init
self.connect(kwargs)
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\abstracts.py", line 716, in connect
self._open_connection()
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\connection.py", line 210, in _open_connection
self._ssl)
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\connection.py", line 142, in _do_auth
auth_plugin=self._auth_plugin)
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\protocol.py", line 102, in make_auth
auth_data, ssl_enabled)
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\protocol.py", line 58, in _auth_response
auth = get_auth_plugin(auth_plugin)(
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\authentication.py", line 191, in get_auth_plugin
"Authentication plugin '{0}' is not supported".format(plugin_name))
sqlalchemy.exc.NotSupportedError: (mysql.connector.errors.NotSupportedError) Authentication plugin 'caching_sha2_password' is not supported

3. 原因：

  • 在MySQL 8.0以后，默认的身份认证是caching_sha2_password而不是mysql_native_password。

4. 解决

  • 打开 mysql cli 终端，输入如下代码：
alter user root@localhost identified with mysql_native_password by 'passwd';
  • 查看修改结果：
use mysql;
select * from user\G;
  • 修改连接方式：
engine = create_engine(
"mysql+mysqlconnector://username:password@host:port/database?auth_plugin=mysql_native_password"
)
  • 连接成功，问题解决；
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 162,158评论 4赞 370
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 68,600评论 1赞 307
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 111,785评论 0赞 254
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 44,655评论 0赞 220
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 53,075评论 3赞 295
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 41,002评论 1赞 225
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 32,146评论 2赞 318
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 30,918评论 0赞 211
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 34,671评论 1赞 250
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 30,838评论 2赞 254
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 32,318评论 1赞 265
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 28,636评论 3赞 263
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 33,343评论 3赞 244
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 26,187评论 0赞 8
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,982评论 0赞 201
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 36,126评论 2赞 285
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 35,934评论 2赞 279