解决 MySQL 中 caching_sha2_password 连接报错和认证方式改为 mysql_native_password 的方法

A kitten
蒋川
B 端产品经理,卡拉云联合创始人
阅读时长 1 分钟

解决 MySQL 中 caching_sha2_password 连接报错和认证方式改为 mysql_native_password 的方法

一. 概述caching_sha2_passwordauth_socket 认证方式

1.caching_sha2_password 认证方式

MySQL 8.0 调整了账号认证方式,把 caching_sha2_password 插件认证方式作为默认首选,这就导致很多需要使用密码登录的客户端,远程登录 MySQL 时报错:

MySQL said: Authentication plugin 'caching_sha2_password'
cannot be loaded: dlopen(
/usr/local/lib/plugin/caching_sha2_password.so, 2): image not found

2.auth_socket认证方式

在 MySQL 5.7 及更高版本中 ,即便你给 root 账号配置了密码,你也无法通过 root + 密码的方式登录 MySQL,因为,root 账号已经在使用auth_socket插件作为身份验证方式,所以使用密码方式登录会报错:

$ mysql -u root -p

使用密码方式登录会报错:

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

auth_socket 的验证方式是通过启动 SO_PEERCRED 选项的 socket 连接到 MySQL 服务器,服务器通过 socket 检测运行客户端(终端)的系统用户名是否为要登录的 MySQL 用户名。如果用户名相同,则认证成功。如果不同,则拒绝连接。 所以插件不需要也不会验证 root 密码。它只检查用户是否使用 UNIX socket 进行连接,然后比较用户名,判断一致性,决定是否拒绝。 auth_socket插件提高了数据库安全性,但对于某些通过远程登录使用数据库的外部应用来说就不是那么好搞了。比如 phpMyadmin 这种目前只能通过密码认证方式远程登录 MySQL 数据库的管理工具来说,就得变更 MySQL 登录认证方式。

本教程将详细讲解如何将 caching_sha2_passwordauth_socket 改为密码认证方式(mysql_native_password)登录,以适应外部应用远程登录 MySQL 服务器的需求。

我们开始吧。

二. 将 caching_sha2_password 更改为 mysql_native_password(密码认证)

caching_sha2_password 无法远程登录

当我们使用外部程序远程登录 MySQL 服务器时,会 caching_sha2_password 报错,我们需要把 SHA2 插件认证方式变为密码认证方式

方法一:你可以创建一个使用密码作为认证方式的账号:

CREATE USER 'kalacloud'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

方法二:当然你也可以把现有使用 caching_sha2_plugin 认证的账号改为使用密码认证。

ALTER USER 'kalacloud'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

接着,根据自己的需要,给你用于远程访问的账号赋予权限。下面的例子是给账号全局权限,包括创建(CREATE)、修改(ALTER)、删除(DROP) 数据库、表、用户,任意表的插入(INSERT)、更新(UPDATE)、删除(DELETE)操作权限。可以使用 SELECT 查询数据,使用 REFERENCES 建立外键关系权限,以及使用 RELOAD 权限执行 FLUSH 操作的权限。当然,你也可以根据自己都需求,对账号权限进行调整。

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'kalacloud'@'%' WITH GRANT OPTION;

最后,运行 FLUSH PRIVILEGES 命令,刷新 MySQL 的系统权限相关表,更新缓存。

FLUSH PRIVILEGES;

我们可以使用以下代码验证

SELECT user,authentication_string,plugin,host FROM mysql.user;

返回值:

caching_sha2_password

此时,我们就可以使用 MySQL Workbench 等第三方 MySQL 管理工具远程登录数据库进行管理。

三. 将 auth_socket 更改为 mysql_native_password(密码认证)

登录 MySQL,我们先来看看 root 账号的认证方式:

auth_socket

接着我们来将更改 root 的 auth_socket 认证方式为 mysql_native_password

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

刷新 MySQL 的系统权限相关表,更新缓存。

FLUSH PRIVILEGES;

现在使用命令检查身份验证方法是否已更改:

SELECT user,authentication_string,plugin,host FROM mysql.user;

返回值:

mysql_native_password

将 MySQL root 用户的身份验证插件更改为 mysql_native_password

四. 结论与推荐

本教程中,我们讲解了 MySQL 三种认证方式(auth_socketcaching_sha2_passwordmysql_native_password)的原理及修改方式。三种认证方式各有利弊,每种方式都有对应都应用场景,大家可以根据自己的实际情况,选择合适自己的认证方式。

接着推荐一下卡拉云,只要你会写 MySQL ,就能使用卡拉云搭建自己的数据工具,比如,数据看板,企业 CRM、ERP,权限管理后台,对账系统等。

卡拉云是新一代低代码开发工具,免安装部署,可一键接入包括 MySQL 在内的常见数据库及 API。可根据自己的工作流,定制开发。无需繁琐的前端开发,只需要简单拖拽,即可快速搭建企业内部工具。数月的开发工作量,使用卡拉云后可缩减至数天,欢迎免费试用卡拉云

卡拉云可一键接入常见的数据库及 API

卡拉云可一键接入常见的数据库及 API

卡拉云可根据公司工作流需求,轻松搭建数据看板或其他内部工具,并且可一键分享给组内的小伙伴。

使用卡拉云轻松搭建企业内部工具

下图为使用卡拉云在 5 分钟内搭建的「优惠券发放核销」后台,仅需要简单拖拽即可快速生成前端组件,只要会写 SQL,便可搭建一套趁手的数据库工具。欢迎免费试用卡拉云

使用卡拉云在 5 分钟内搭建的「优惠券发放核销」后台

更多数据库相关教程可访问 卡拉云 查看。如果你还有什么疑问,欢迎一起讨论。我的微信 HiJiangChuan。

卡拉云联合创始人蒋川的微信

有关 MySQL 教程,可继续拓展学习:

© 2021 卡拉云 Built with ❤️ in Beijing

京ICP备15049164号-9