MySQL_踩坑記錄

雪与冰心丶發表於2024-10-27
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

相關文章