解决mysql数据库连接报错：Authentication plugin ‘caching_sha2_password‘ cannot be loaded

解决mysql数据库连接报错：Authentication plugin ‘caching_sha2_password’ cannot be loaded

OperationalError: (2059, “Authentication plugin ‘caching_sha2_password’ cannot be loaded: /usr/lib/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory”)

错误类似如下：

08:02:58 [INFO] sentry.utils.raven.SentryInternalClient: Not capturing exception due to filters: <class ‘django.db.utils.OperationalError’>
10.244.2.0 - - [13/Jun/2024:08:02:58 +0000] “POST /api/17/store/ HTTP/1.1” 500 337 “-” “sentry.python/0.9.5”
Traceback (most recent call last):
File “/usr/local/lib/python2.7/site-packages/sentry/web/api.py”, line 128, in dispatch
request, helper, project_id=project_id, origin=origin, *args, **kwargs
File “/usr/local/lib/python2.7/site-packages/sentry/web/api.py”, line 198, in _dispatch
project = self._get_project_from_id(project_id)
File “/usr/local/lib/python2.7/site-packages/sentry/web/api.py”, line 96, in _get_project_from_id
return Project.objects.get_from_cache(id=project_id)
File “/usr/local/lib/python2.7/site-packages/sentry/db/models/manager.py”, line 261, in get_from_cache
result = self.get(**kwargs)
File “/usr/local/lib/python2.7/site-packages/django/db/models/manager.py”, line 151, in get
return self.get_queryset().get(*args, **kwargs)
File “/usr/local/lib/python2.7/site-packages/django/db/models/query.py”, line 304, in get
num = len(clone)
File “/usr/local/lib/python2.7/site-packages/django/db/models/query.py”, line 77, in len
self._fetch_all()
File “/usr/local/lib/python2.7/site-packages/django/db/models/query.py”, line 857, in _fetch_all
self._result_cache = list(self.iterator())
File “/usr/local/lib/python2.7/site-packages/django/db/models/query.py”, line 220, in iterator
for row in compiler.results_iter():
File “/usr/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py”, line 713, in results_iter
for rows in self.execute_sql(MULTI):
File “/usr/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py”, line 785, in execute_sql
cursor = self.connection.cursor()
File “/usr/local/lib/python2.7/site-packages/django/db/backends/init.py”, line 162, in cursor
cursor = util.CursorWrapper(self._cursor(), self)
File “/usr/local/lib/python2.7/site-packages/django/db/backends/init.py”, line 132, in _cursor
self.ensure_connection()
File “/usr/local/lib/python2.7/site-packages/django/db/backends/init.py”, line 127, in ensure_connection
self.connect()
File “/usr/local/lib/python2.7/site-packages/django/db/utils.py”, line 99, in exit
six.reraise(dj_exc_type, dj_exc_value, traceback)
File “/usr/local/lib/python2.7/site-packages/django/db/backends/init.py”, line 127, in ensure_connection
self.connect()
File “/usr/local/lib/python2.7/site-packages/django/db/backends/init.py”, line 115, in connect
self.connection = self.get_new_connection(conn_params)
File “/usr/local/lib/python2.7/site-packages/django/db/backends/mysql/base.py”, line 435, in get_new_connection
conn = Database.connect(**conn_params)
File “/usr/local/lib/python2.7/site-packages/MySQLdb/init.py”, line 84, in Connect
return Connection(*args, **kwargs)
File “/usr/local/lib/python2.7/site-packages/MySQLdb/connections.py”, line 179, in init
super(Connection, self).init(*args, **kwargs2)
OperationalError: (2059, “Authentication plugin ‘caching_sha2_password’ cannot be loaded: /usr/lib/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory”)
08:03:04 [ERROR] sentry: (2059, “Authentication plugin ‘caching_sha2_password’ cannot be loaded: /usr/lib/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory”)
Traceback (most recent call last):
File “/usr/local/lib/python2.7/site-packages/sentry/web/api.py”, line 128, in dispatch
request, helper, project_id=project_id, origin=origin, *args, **kwargs
File “/usr/local/lib/python2.7/site-packages/sentry/web/api.py”, line 198, in _dispatch
project = self._get_project_from_id(project_id)
File “/usr/local/lib/python2.7/site-packages/sentry/web/api.py”, line 96, in _get_project_from_id
return Project.objects.get_from_cache(id=project_id)
File “/usr/local/lib/python2.7/site-packages/sentry/db/models/manager.py”, line 261, in get_from_cache
result = self.get(**kwargs)
File “/usr/local/lib/python2.7/site-packages/django/db/models/manager.py”, line 151, in get
return self.get_queryset().get(*args, **kwargs)
File “/usr/local/lib/python2.7/site-packages/django/db/models/query.py”, line 304, in get
num = len(clone)
File “/usr/local/lib/python2.7/site-packages/django/db/models/query.py”, line 77, in len
self._fetch_all()
File “/usr/local/lib/python2.7/site-packages/django/db/models/query.py”, line 857, in _fetch_all
self._result_cache = list(self.iterator())
File “/usr/local/lib/python2.7/site-packages/django/db/models/query.py”, line 220, in iterator
for row in compiler.results_iter():
File “/usr/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py”, line 713, in results_iter
for rows in self.execute_sql(MULTI):
File “/usr/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py”, line 785, in execute_sql
cursor = self.connection.cursor()
File “/usr/local/lib/python2.7/site-packages/django/db/backends/init.py”, line 162, in cursor
cursor = util.CursorWrapper(self._cursor(), self)
File “/usr/local/lib/python2.7/site-packages/django/db/backends/init.py”, line 132, in _cursor
self.ensure_connection()
File “/usr/local/lib/python2.7/site-packages/django/db/backends/init.py”, line 127, in ensure_connection
self.connect()
File “/usr/local/lib/python2.7/site-packages/django/db/utils.py”, line 99, in exit
six.reraise(dj_exc_type, dj_exc_value, traceback)
File “/usr/local/lib/python2.7/site-packages/django/db/backends/init.py”, line 127, in ensure_connection
self.connect()
File “/usr/local/lib/python2.7/site-packages/django/db/backends/init.py”, line 115, in connect
self.connection = self.get_new_connection(conn_params)
File “/usr/local/lib/python2.7/site-packages/django/db/backends/mysql/base.py”, line 435, in get_new_connection
conn = Database.connect(**conn_params)
File “/usr/local/lib/python2.7/site-packages/MySQLdb/init.py”, line 84, in Connect
return Connection(*args, **kwargs)
File “/usr/local/lib/python2.7/site-packages/MySQLdb/connections.py”, line 179, in init
super(Connection, self).init(*args, **kwargs2)
OperationalError: (2059, “Authentication plugin ‘caching_sha2_password’ cannot be loaded: /usr/lib/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory”)
08:03:04 [INFO] sentry.utils.raven.SentryInternalClient: Not capturing exception due to filters: <class ‘django.db.utils.OperationalError’>

排查思路：

可能是创建用户没有指定插件使用了8.0自带的插件，8.0版本的mysql的加密规则是 caching_sha2_password，需要改成 mysql_native_password

解决方案：

1、服务器上mysql版本过低，重新安装8.0版本的mysql再连接即可。

2、更换用户名

3、修改加密规则

1）使用root登录mysql，查看当前账号的加密规则

#查看mysql版本
select @@version;

#查看加密方式
show variables like 'default_authentication_plugin';

#查看用户信息
select host,user,plugin from mysql.user;

2）修改加密方式（这使用test账号做测试）

alter user 'test'@'%' identified with mysql_native_password by 'password';

3. 如果进不去mysql，可以通过修改配置文件:

编辑my.cnf配置文件

vim /etc/my.cnf

在[mysqld]下面加上一行:

default_authentication_plugin=mysql_native_password

这个意思是改变默认加密方式，然后重启 mysql 服务即可。

4）如果进不去mysql，修改配置文件：

编辑my.cnf配置文件

vim /etc/my.cnf 

在[mysqld]下面加上一行:

skip-grant-tables

这个意思是跳过密码验证，然后重启 mysql 服务即可。

# MySQL--Authentication Plugin ‘caching_sha2_password’ Cannot be Loaded

caching_sha2_password插件时MySQL8中默认的插件，所有使用identified with选项创建的用户，都使用该插件。

由于是内嵌的插件，本地client和server会正常工作。

但是远程客户端连接，就有可能遇到以下错误：

ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

这个错误并不总是与mysql 客户端的–plugin-dir有关。而通常是意味着你要升级mysql客户端的库文件了。

插件ching_sha2_password最初是在MySQL5.7中引入的。如果你仍然是使用5.6或更早的版本，你就会无法连接到MySQL。

MariaDB不支持caching_sha2_password插件，但是MariaDB Connector/C从3.2开始支持该插件。以单独的库文件形式提供，而不是内嵌的。所有使用MariaDB Connector/C的客户端支持使用caching_sha2_password插件进行认证。

MariaDB libmysqlclient库不支持caching_sha2_password插件。因此，如果你使用libmysqlclient库客户端连接库，你将无法使用caching_sha2_password插件进行身份验证。

使用不同API（例如 Python、PHP、Go、Java 和 ODBC）连接到MySQL的应用程序需要使用最新版本的连接器和数据库驱动程序。MySQL和MariaDB连接器都支持caching_sha2_password插件。

这也适用于 Percona Toolkit。

结论

为了使用caching_sha2_password身份验证插件

1.对于MySQL、Percona Server for MySQL、 Percona XtraDB Cluster，可以使用5.7或者更新的客户端，不需要其它操作，因为是内嵌的。

2.对于MariaDB，使用MariaDB Connector/C 3.0.2或者更新的客户端；如果你的客户端找不到caching_sha2_password.so库文件的位置，使用–pluggin-dir参数指定