起因
在GreatSQL社區(qū)上有一位用戶提出了“手工構(gòu)建MGR碰到的次節(jié)點一直處于recovering狀態(tài)”，經(jīng)過排查后，發(fā)現(xiàn)了是因為新密碼驗證插件caching_sha2_password導致的從節(jié)點一直無法連接主節(jié)點，帖子地址：(https://greatsql.cn/thread-420-2-1.html)）
復現(xiàn)
環(huán)境介紹
本文驗證環(huán)境，以及本文所采用數(shù)據(jù)庫為GreatSQL 8.0.32-24
$ cat /etc/system-release
Red Hat Enterprise Linux Server release 7.9 (Maipo)
$ uname -a
Linux gip 3.10.0-1160.el7.x86_64 #1 SMP Tue Aug 18 14:50:17 EDT 2020 x86_64 x86_64 x86_64 GNU/Linux
部署準備：
采用的是單機多實例的部署方式，如何部署單機多實例可以前往(https://gitee.com/GreatSQL/GreatSQL-Manual/blob/master/6-oper-guide/6-6-multi-instances.md)
| IP | 端口 | 角色 |
|---|---|---|
| 172.17.139.77 | 3306 | mgr01 |
| 172.17.139.77 | 3307 | mgr02 |
MGR有關(guān)配置參數(shù)：
#mgr settings
loose-plugin_load_add = 'mysql_clone.so'
loose-plugin_load_add = 'group_replication.so'
loose-group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1"
loose-group_replication_group_seeds = '172.17.139.77:33061,172.17.139.77:33071'
loose-group_replication_start_on_boot = ON
loose-group_replication_bootstrap_group = OFF
loose-group_replication_exit_state_action = READ_ONLY
loose-group_replication_flow_control_mode = "DISABLED"
loose-group_replication_single_primary_mode = ON
loose-group_replication_communication_max_message_size = 10M
loose-group_replication_transaction_size_limit = 3G
loose-group_replication_arbitrator = 0
loose-group_replication_single_primary_fast_mode = 0
loose-group_replication_request_time_threshold = 20000
report_host = "172.17.139.77"
MGR01節(jié)點配置如下：
[mysqld@mgr01]
datadir=/data/GreatSQL/mgr01
socket=/data/GreatSQL/mgr01/mysql.sock
port=3306
server_id=103306
log-error=/data/GreatSQL/mgr01/error.log
loose-group_replication_local_address= "172.17.139.77:33061"
MGR02節(jié)點配置如下：
[mysqld@mgr02]
datadir=/data/GreatSQL/mgr02
socket=/data/GreatSQL/mgr02/mysql.sock
port=3307
server_id=103317
log-error=/data/GreatSQL/mgr02/error.log
loose-group_replication_local_address= "172.17.139.77:33071"
啟動MGR01實例、MGR02實例，并修改密碼
#啟動兩個實例
$ systemctl restart greatsql@mgr01 &
$ systemctl restart greatsql@mgr02 &
#獲取初始化密碼
$ grep root /data/GreatSQL/mgr01/error.log
$ grep root /data/GreatSQL/mgr02/error.log
#登錄數(shù)據(jù)庫并修改密碼
$ mysql -S /data/GreatSQL/mgr01/mysql.sock -uroot -p
greatsql> alter user root@'localhost' identified by 'GreatSQL@666';
$ mysql -S /data/GreatSQL/mgr02/mysql.sock -uroot -p
greatsql> alter user root@'localhost' identified by 'GreatSQL@666';
檢查兩個實例是否正確加載group_replicaiton 插件
greatsql> show plugins;
+----------------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+----------------------------------+----------+--------------------+----------------------+---------+
| group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL |
+----------------------------------+----------+--------------------+----------------------+---------+
沒有加載的話可以手動加載這個plugin
greatsql> install plugin group_replication soname 'group_replication.so';
搭建MGR
接下來就可以手工搭建MGR，流程如下可參考安裝部署MGR集群 | 深入淺出MGR(https://gitee.com/GreatSQL/GreatSQL-Doc/blob/master/deep-dive-mgr/deep-dive-mgr-03.md)）
MGR01實例操作：
greatsql> set session sql_log_bin=0;
# 特別注意下面因為8.0.4版本開始使用的默認是“caching_sha2_password”，所以這樣創(chuàng)建會采用最新的身份認證插件
greatsql> create user repl@'%' identified by 'GreatSQL@666';
greatsql> GRANT BACKUP_ADMIN, REPLICATION SLAVE ON *.* TO `repl`@`%`;
greatsql> set session sql_log_bin=1;
greatsql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='GreatSQL@666' FOR CHANNEL 'group_replication_recovery';
接下來即可啟動MGR集群：
greatsql> set global group_replication_bootstrap_group=ON;
greatsql> start group_replication;
greatsql> select * from performance_schema.replication_group_members\G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 2920447e-35bf-11ee-89a5-00163e566da1
MEMBER_HOST: 172.17.139.77
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.32
MEMBER_COMMUNICATION_STACK: XCom
MGR02實例操作：
greatsql> set session sql_log_bin=0;
greatsql> create user repl@'%' identified by 'GreatSQL@666';
greatsql> GRANT BACKUP_ADMIN, REPLICATION SLAVE ON *.* TO `repl`@`%`;
greatsql> set session sql_log_bin=1;
greatsql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='GreatSQL@666' FOR CHANNEL 'group_replication_recovery';
greatsql> start group_replication;
Query OK, 0 rows affected (5.39 sec)
此時創(chuàng)建的用戶采用的都是caching_sha2_password身份認證插件
greatsql> SELECT USER,PLUGIN FROM mysql.`user` ;
+------------------+-----------------------+
| USER | PLUGIN |
+------------------+-----------------------+
| repl | caching_sha2_password |
| mysql.infoschema | caching_sha2_password |
| mysql.session | caching_sha2_password |
| mysql.sys | caching_sha2_password |
| root | caching_sha2_password |
+------------------+-----------------------+
雖然啟動MGR成功，但是查看下節(jié)點狀態(tài)：
greatsql> select * from performance_schema.replication_group_members\G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 2920447e-35bf-11ee-89a5-00163e566da1
MEMBER_HOST: 172.17.139.77
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.32
MEMBER_COMMUNICATION_STACK: XCom
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 2a4f068b-35bf-11ee-9504-00163e566da1
MEMBER_HOST: 172.17.139.77
MEMBER_PORT: 3307
MEMBER_STATE: RECOVERING
MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.32
MEMBER_COMMUNICATION_STACK: XCom
2 rows in set (0.00 sec)
此時節(jié)點一直處于RECOVERING狀態(tài)，查看mgr02實例的錯誤日志如下：
2023-08-08T08:00:47.034870Z 42 [ERROR] [MY-010584] [Repl] Slave I/O for channel 'group_replication_recovery': error connecting to master 'repl@172.17.139.77:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061
2023-08-08T08:00:47.037631Z 35 [ERROR] [MY-011582] [Repl] Plugin group_replication reported: 'There was an error when connecting to the donor server. Please check that group_replication_recovery channel credentials and all MEMBER_HOST column values of performance_schema.replication_group_members table are correct and DNS resolvable.'
2023-08-08T08:00:47.037671Z 35 [ERROR] [MY-011583] [Repl] Plugin group_replication reported: 'For details please check performance_schema.replication_connection_status table and error log messages of Slave I/O for channel group_replication_recovery.'
這是由于caching_sha2_password 是 MySQL 8.0.4 引入的一個新的身份驗證插件，caching_sha2_password 對密碼安全性要求更高，要求用戶認證過程中在網(wǎng)絡傳輸?shù)拿艽a是加密的，所以導致的這個問題的出現(xiàn)，caching_sha2_password的介紹可以看社區(qū)文章“淺談 MySQL 新的身份驗證插件 caching_sha2_password【微信導入、微信導入】”
解決方式
1、采用舊密碼驗證插件
舊的身份驗證插件mysql_native_password，mysql_native_password的特點是不需要加密的連接。該插件驗證速度特別快，但是不夠安全，只需要更改創(chuàng)建用戶的語句
create user repl@'%' identified with mysql_native_password by 'GreatSQL@666';
舊密碼驗證插件容易被破解，如果有 GreatSQL 服務要公網(wǎng)上使用，建議還是盡量使用 caching_sha2_password作為認證插件
2、啟用group_replication_recovery_get_public_key
設置 group_replication_recovery_get_public_key=ON 可以確保從節(jié)點在連接到主節(jié)點時能夠獲取所需的公鑰，從而允許安全連接并成功進行身份驗證，避免了連接錯誤和身份驗證問題。
手冊中也有明確說明：
18.6.3.1.1 Replication User With The Caching SHA-2 Authentication Plugin
By default, users created in MySQL 8 use Section 6.4.1.2, “Caching SHA-2 Pluggable Authentication”. If the replication user you configure for distributed recovery uses the caching SHA-2 authentication plugin, and you are not using SSL for distributed recovery connections, RSA key-pairs are used for password exchange. For more information on RSA key-pairs, see Section 6.3.3, “Creating SSL and RSA Certificates and Keys”.
In this situation, you can either copy the public key of the to the joining member, or configure the donors to provide the public key when requested. The more secure approach is to copy the public key of the replication user account to the joining member. Then you need to configure the group_replication_recovery_public_key_path system variable on the joining member with the path to the public key for the replication user account. rpl_user
The less secure approach is to set group_replication_recovery_get_public_key=ON on donors so that they provide the public key of the replication user account to joining members. There is no way to verify the identity of a server, therefore only set group_replication_recovery_get_public_key=ON when you are sure there is no risk of server identity being compromised, for example by a man-in-the-middle attack.
可以看到，當確認環(huán)境安全以及沒人任何人攻擊集群時，如果不配置ssl，可以最低配置group_replication_recovery_get_public_key=ON來在請求復制用戶密鑰時給公鑰
3、為組復制通道啟用SSL支持
以下操作方法僅使用于 GreatSQL/MySQL 8.0.27版本及以上
更安全的方法是將repl用戶所需的公鑰文件復制到joiner節(jié)點的Server所在主機中。然后，在joiner節(jié)點的Server中配置group_replication_recovery_public_key_path系統(tǒng)變量，指定rpl_user用戶所需的公鑰文件路徑。
使用caching_sha2_password 插件身份驗證會在數(shù)據(jù)目錄下生成如下兩個RSA文件：
private_key.pem
public_key.pem
-
private_key.pem：RSA私鑰 -
public_key.pem： RSA公鑰
對于 MGR ，如果設置 group_replication_ssl_mode=DISABLED 必須使用下面的變量來指定 RSA 公鑰，否則報錯：
-
group_replication_recovery_get_public_key：向服務端請求 RSA 公鑰； -
group_replication_recovery_public_key_path：指定本地 RSA 公鑰文件；
指定本地RSA公鑰，首先需要全局MGR配置開啟SSL
[mysqld]
#開啟use_ssl，指定組成員之間的組復制分布式恢復連接是否應使用 SSL
loose-group_replication_recovery_use_ssl=ON
進入MGR01實例配置
greatsql> set session sql_log_bin=0;
# 此時就可以使用“caching_sha2_password”身份認證插件
greatsql> create user repl@'%' identified by 'GreatSQL@666';
greatsql> GRANT BACKUP_ADMIN, REPLICATION SLAVE ON *.* TO `repl`@`%`;
greatsql> set session sql_log_bin=1;
greatsql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='GreatSQL@666' FOR CHANNEL 'group_replication_recovery';
啟動MGR01實例的MGR集群
greatsql> set global group_replication_bootstrap_group=ON;
greatsql> start group_replication;
greatsql> select * from performance_schema.replication_group_members\G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 35b653d2-3658-11ee-93c9-00163e566da1
MEMBER_HOST: 172.17.139.77
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.32
MEMBER_COMMUNICATION_STACK: XCom
啟動成功后，需要把MGR01節(jié)點的RSA公鑰拷貝到MGR02節(jié)點上,因為MGR02也會生成此公鑰，所以最好創(chuàng)建一個文件夾
$ mkdir mgr01_key
$ chown mysql:mysql mgr01_key/
# 將public_key.pem移動到MGR02
$ mv /data/GreatSQL/mgr01/public_key.pem /data/GreatSQL/mgr02/mgr01_key/
當然，如果有多個節(jié)點，也需要把主節(jié)點的RSA公鑰移動到各個節(jié)點上
MGR02節(jié)點操作
greatsql> set session sql_log_bin=0;
greatsql> create user repl@'%' identified by 'GreatSQL@666';
greatsql> GRANT BACKUP_ADMIN, REPLICATION SLAVE ON *.* TO `repl`@`%`;
greatsql> set session sql_log_bin=1;
greatsql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='GreatSQL@666' FOR CHANNEL 'group_replication_recovery';
# 此命令設置完成后，最好寫進my.cnf文件中持久化
greatsql> set global group_replication_recovery_public_key_path = "/data/GreatSQL/mgr02/mgr01key/public_key.pem";
greatsql> start group_replication;
greatsql> select * from performance_schema.replication_group_members\G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 35b653d2-3658-11ee-93c9-00163e566da1
MEMBER_HOST: 172.17.139.77
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.32
MEMBER_COMMUNICATION_STACK: XCom
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: aa031fb9-365a-11ee-9925-00163e566da1
MEMBER_HOST: 172.17.139.77
MEMBER_PORT: 3307
MEMBER_STATE: ONLINE
MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.32
MEMBER_COMMUNICATION_STACK: XCom
可以看到雙節(jié)點ONLINE，新加入的節(jié)點不會一直是RECOVERING狀態(tài)
總結(jié)
新身份驗證插件caching_sha2_password安全度相比其他的身份驗證插件，既解決安全性問題又解決性能問題，建議使用新密碼驗證插件。
也感謝社區(qū)用戶指出GreatSQL社區(qū)文檔中的不足，并給予用戶金幣獎勵，同時歡迎大家來GreatSQL社區(qū)捉蟲~
Enjoy GreatSQL ??
關(guān)于 GreatSQL
GreatSQL是適用于金融級應用的國內(nèi)自主開源數(shù)據(jù)庫，具備高性能、高可靠、高易用性、高安全等多個核心特性，可以作為MySQL或Percona Server的可選替換，用于線上生產(chǎn)環(huán)境，且完全免費并兼容MySQL或Percona Server。
相關(guān)鏈接： GreatSQL社區(qū) Gitee GitHub Bilibili
GreatSQL社區(qū)：
社區(qū)博客有獎征稿詳情：https://greatsql.cn/thread-100-1-1.html
技術(shù)交流群：
微信：掃碼添加
GreatSQL社區(qū)助手微信好友，發(fā)送驗證信息加群。文章來源：http://www.zghlxwxcb.cn/news/detail-707993.html
文章來源地址http://www.zghlxwxcb.cn/news/detail-707993.html
到了這里，關(guān)于MGR新節(jié)點RECOVERING狀態(tài)的分析與解決：caching_sha2_password驗證插件的影響的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
