英文:

Unable to establish connection to MySQL server 'Authentication plugin is not supported'

问题

我正在尝试从IDLE连接到MySQL服务器，但我无法弄清楚为什么会出错。

```python
>>> dbconfig = {'host': '127.0.0.1',
	    'user': 'vsearch',
	    'password': 'vsearchpasswd',
	    'database': 'vsearchlogDB'}
>>> import mysql.connector
>>> conn = mysql.connector.connect(**dbconfig)

Traceback (most recent call last):
  File "<pyshell#5>", line 1, in <module>
    conn = mysql.connector.connect(**dbconfig)
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/mysql/connector/__init__.py", line 179, in connect
    return MySQLConnection(*args, **kwargs)
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/mysql/connector/connection.py", line 95, in __init__
    self.connect(**kwargs)
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/mysql/connector/abstracts.py", line 719, in connect
    self._open_connection()
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/mysql/connector/connection.py", line 208, in _open_connection
    self._do_auth(self._user, self._password,
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/mysql/connector/connection.py", line 137, in _do_auth
    packet = self._protocol.make_auth(
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/mysql/connector/protocol.py", line 99, in make_auth
    packet += self._auth_response(client_flags, username, password,
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/mysql/connector/protocol.py", line 58, in _auth_response
    auth = get_auth_plugin(auth_plugin)(
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/mysql/connector/authentication.py", line 190, in get_auth_plugin
    raise errors.NotSupportedError(
mysql.connector.errors.NotSupportedError: 不支持认证插件 'caching_sha2_password'

我尝试过在stackoverflow上找到的以下建议：

• 关闭mysql.server，安装mysql-connector-python，然后重新启动mysql.server
• 在connect()方法中传递一个auth_plugin参数（如下所示）

auth_plugin='mysql_native_password'

• 确保MySQL正在监听端口3306（下面是答案）

localhost:mysql (LISTEN)

版本：

• mysql 版本 8.0.32 适用于macos12.6 on x86_64（Homebrew）
• Python 3.9.1
• MacOS 12

<details>
<summary>英文:</summary>

I&#39;m trying to establish a connection to MySQL server from IDLE and I can&#39;t figure out why I&#39;m getting an error.

>>> dbconfig = {'host': '127.0.0.1',
'user': 'vsearch',
'password': 'vsearchpasswd',
'database': 'vsearchlogDB'}
>>> import mysql.connector
>>> conn = mysql.connector.connect(**dbconfig)

Traceback (most recent call last):
File "<pyshell#5>", line 1, in <module>
conn = mysql.connector.connect(**dbconfig)
File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/mysql/connector/init.py", line 179, in connect
return MySQLConnection(*args, **kwargs)
File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/mysql/connector/connection.py", line 95, in init
self.connect(**kwargs)
File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/mysql/connector/abstracts.py", line 719, in connect
self._open_connection()
File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/mysql/connector/connection.py", line 208, in _open_connection
self._do_auth(self._user, self._password,
File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/mysql/connector/connection.py", line 137, in _do_auth
packet = self._protocol.make_auth(
File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/mysql/connector/protocol.py", line 99, in make_auth
packet += self._auth_response(client_flags, username, password,
File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/mysql/connector/protocol.py", line 58, in _auth_response
auth = get_auth_plugin(auth_plugin)(
File "/Library/Frameworks/Python.framework/Versions/3.9/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

I've tried these suggestions I found on stackoverflow :
- shutting down mysql.server, installing mysql-connector-python and restarting mysql.server
- passing an auth_plugin argument to the ```connect()``` method (as seen below)

auth_plugin='mysql_native_password'

- made sure MySQL in listening to port 3306 (answer below)

localhost:mysql (LISTEN)

---
Versions:
- mysql  Ver 8.0.32 for macos12.6 on x86_64 (Homebrew)
- Python 3.9.1
- MacOS 12


</details>


# 答案1
**得分**: 1

成功修复问题的方法是卸载MySQL连接器，然后重新安装它。出现了权限问题，所以确保您有权限在连接器目录中进行读写操作。

如果您正在尝试使用MySQL连接器连接到MySQL，可以参考以下教程：
https://pynative.com/python-mysql-database-connection/

<details>
<summary>英文:</summary>

Managed to fix issue by uninstalling MySQL connector and reinstalling it. Had issues with permissions so make sure you have permissions to read and write in connector directories.

This tutorial might help if your trying to connect to MySQL with MySQL-connector-python :
https://pynative.com/python-mysql-database-connection/

</details>



# 答案2
**得分**: 0

MySQL 8引入了一个名为caching_sha2_password的新默认身份验证插件。错误可能是因为您的客户端/连接器目前不支持caching_sha2_password插件。

要解决此问题，您可以考虑将mysql_native_password作为MySQL 8.0服务器的默认身份验证方式。

在MySQL配置文件中添加以下条目：
1. [mysqld]
2. default-authentication-plugin=mysql_native_password

重新启动MySQL服务器以加载更改。

然后，您可能需要重新创建数据库用户以使用此身份验证插件。

首先，删除用户：
1. DROP USER vsearch@127.0.0.1;

然后使用mysql_native_password插件重新创建用户：
1. CREATE USER 'vsearch'@'127.0.0.1' IDENTIFIED WITH mysql_native_password BY 'vsearchpasswd';

为数据库vsearchlogDB分配适当的权限：
1. GRANT ALL PRIVILEGES ON vsearchlogDB.* to 'vsearch'@'127.0.0.1';

重新运行您的应用程序，应该就可以了。

<details>
<summary>英文:</summary>

MySQL 8 introduces a new default authentication plugin called caching_sha2_password. The error is likely because your clients/connectors do not (yet) support the caching_sha2_password plugin. 

To resolve this issue, you may consider using mysql_native_password as the default authentication for MySQL 8.0 server. 

Add the following entry in the MySQL configuration file:
1. [mysqld] 
2. default-authentication-plugin=mysql_native_password 
Restart the MySQL server to load the change. 

Then, you might need to recreate your database user to use this authentication plugin. 
First, drop the user:

1DROP USER vsearch@127.0.0.1; 

Then create it again with the mysql_native_password plugin:

1CREATE USER &#39;vsearch&#39;@&#39;127.0.0.1&#39; IDENTIFIED WITH mysql_native_password BY &#39;vsearchpasswd&#39;; 

Assign the proper privileges for database vsearchlogDB:
1GRANT ALL PRIVILEGES ON vsearchlogDB.* to &#39;vsearch&#39;@&#39;127.0.0.1&#39;; 

Re-run your application and you should be good.

</details>