MySQL_踩坑记录

=== MySQL_踩坑记录 ===

本文的所有解决方案并非万能，只是记录本人遇到的情况。

Authentication plugin 'mysql_native_password' cannot be loaded

初始问题及解决方案

Windows环境下使用 MySQL Connector/C++ 远程访问 Linux中的MySQL服务，下面是测试代码。

// 测试是否可以访问 MySQL
void TestConnectMySQL() {
	try {
		// 获取 MySQL 驱动实例，创建数据库连接
		sql::mysql::MySQL_Driver* driver = sql::mysql::get_mysql_driver_instance();
		sql::Connection* conn = driver->connect("ip:port", "username", "password");
		// 选择数据库
		conn->setSchema("db");
	}
	catch (sql::SQLException& E) {
		// 处理异常
		std::cerr << "MySQL ConnPool init failed, error: " << E.what() << std::endl;
		std::cerr << " (MySQL Error Code: " << E.getErrorCode()
			<< ", SQLState: " << E.getSQLState() << " )" << std::endl;
	}
}

程序执行出错

MySQL ConnPool init failed, error: Authentication plugin 'mysql_native_password' cannot be loaded: 找不到指定的模块。

解决方案

当时访问的MySQL版本是5.7，最终换成了8.0及以上的MySQL版本，创建一个新的支持远程连接的用户，默认会使用caching_sha2_password安全验证，程序中使用此用户即可消除此错误。

注意点

• MySQL版本8.0及以上
• 用户支持远程连接
• 用户使用caching_sha2_password安全验证方式而非mysql_native_password

此问题已解决！后续内容为解决此问题而引发的一系列问题，记录于此。

MySQL配置文件

# 查找配置文件的位置 my.cnf mysqld.cnf
find /etc -name "my.cnf" 2>/dev/null
find /etc -name "mysqld.cnf" 2>/dev/null

[mysqld]

# 指定本机访问 "0.0.0.0"表示任意ip都可访问，即允许远程连接
#bind-address = 127.0.0.1
bind-address = 0.0.0.0

# 允许同一客户端连接失败的次数
max_connect_errors = 10

# 设置默认的插件验证方式
#default_authentication_plugin = mysql_native_password
#default_authentication_plugin = caching_sha2_password

Host 'xxx' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

这个错误表明 MySQL 阻止了来自特定主机的连接，因为该主机尝试连接的错误次数过多。

解决方案

在 MySQL 中运行以下命令：

-- 查看最大错误连接数
show global variables like '%max_connect_errors%';
-- 设置为100次
SET GLOBAL max_connect_errors = 100;

或者在 MySQL 配置文件中添加或修改该设置：

[mysqld]
max_connect_errors = 100

然后重启 MySQL 服务：

sudo systemctl restart mysql

至此，即可继续连接。

Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

MySQL8.0.16版本中新增了一个system_user帐户类型，由于root用户没有SYSTEM_USER权限，会报此错误，授予权限即可。

grant system_user on *.* to 'root';

在Linux上跳过密码登录MySQL报错-bash: mysqld_safe: command not found

解决方案：

mysqld --user=mysql --skip-grant-tables --skip-networking &

至此，可以登录root用户而无需密码：

mysql -u root

常用方案命令整合

MySQL

# 创建新用户，并允许远程登录
#-----------------------------------------------------------------------------
# 创建新用户 指定本地登录
create user 'username'@'localhost' identified by 'password';
# 创建新用户 指定远程登录
create user 'username'@'%' identified by 'password';
# 授予新用户远程登录的所有权限
grant all privileges on *.* to 'username'@'%' with grant option;

# 设置用户登录的加密方式（身份验证插件）
#-----------------------------------------------------------------------------
# 查询使用 mysql_native_password 加密方式的用户信息
SELECT user, host, plugin from mysql.user WHERE plugin='mysql_native_password';
# 查询使用 caching_sha2_password 加密方式的用户信息
SELECT user, host, plugin from mysql.user WHERE plugin='caching_sha2_password';
# 设置 root 用户本地登录密码永久有效
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root密码' PASSWORD EXPIRE NEVER;
# 设置 root 用户本地登录的加密方式为 mysql_native_password
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root密码';
# 设置 root 用户远程登录密码永久有效
ALTER USER 'root'@'%' IDENTIFIED BY 'root密码' PASSWORD EXPIRE NEVER;
# 设置 root 用户远程登录的加密方式为 mysql_native_password
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root密码';
# 可仿照 root 用户，配置普通用户本地和远程登录的密码和加密方式
# 设置之后，刷新下权限
flush privileges;

# 插件管理
#-----------------------------------------------------------------------------
# 查看插件信息
SHOW PLUGINS;
# 安装 mysql_native_password 插件
INSTALL PLUGIN mysql_native_password SONAME 'mysql_native_password';
# 安装 auth_socket 插件
INSTALL PLUGIN auth_socket SONAME 'auth_socket.so';

# 修改身份验证插件（同上面的设置用户登录的加密方式一样）
#-----------------------------------------------------------------------------
# 修改本地登陆
# 修改密码过期规则----》永不过期
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER; 
# 更新用户的密码修改加密规则
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456'; 
# 刷新权限
FLUSH PRIVILEGES;
# 重置密码（==非必须==）
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
#-----------------------------------------------------------------------------
# 修改远程登陆
# 修改密码过期规则----》永不过期
ALTER USER 'root'@'%' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER; 
# 更新用户的密码修改加密规则
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
# 刷新权限
FLUSH PRIVILEGES;
# 重置密码（==非必须==）
ALTER USER 'root'@'%' IDENTIFIED BY '123456';
#-----------------------------------------------------------------------------
# 设置密码规则
# 查看密码策略。 默认 MEDIUM
SHOW VARIABLES LIKE 'validate_password%';
# 将密码位数设置为 6
set global validate_password.length = 6;
# 密码策略修改为LOW，原值为MEDIUM
set global validate_password.policy = LOW;
# 至此，可以设置较为简单的密码
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
# 刷新权限
FLUSH PRIVILEGES;
#-----------------------------------------------------------------------------
# 查看加密方式
SELECT Host, User, plugin from mysql.user;

Linux

# mysql 或 mysqld 具体取决于service
#-----------------------------------------------------------------------------
# 查看服务的状态
systemctl status mysql
# 重启
systemctl restart mysql
# 停止
systemctl stop mysql
# 启动
systemctl start mysql