佛山网站公司,网站建设进项填那个服务,快速建立平台网站开发网站模板设计,网站优化柳州起因
在GreatSQL社区上有一位用户提出了“手工构建MGR碰到的次节点一直处于recovering状态”#xff0c;经过排查后#xff0c;发现了是因为新密码验证插件caching_sha2_password导致的从节点一直无法连接主节点#xff0c;帖子地址#xff1a;(https://greatsql.cn/threa…起因
在GreatSQL社区上有一位用户提出了“手工构建MGR碰到的次节点一直处于recovering状态”经过排查后发现了是因为新密码验证插件caching_sha2_password导致的从节点一直无法连接主节点帖子地址(https://greatsql.cn/thread-420-2-1.html)
复现
环境介绍
本文验证环境以及本文所采用数据库为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.773306mgr01172.17.139.773307mgr02
MGR有关配置参数
#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节点配置如下
[mysqldmgr01]
datadir/data/GreatSQL/mgr01
socket/data/GreatSQL/mgr01/mysql.sock
port3306
server_id103306
log-error/data/GreatSQL/mgr01/error.log
loose-group_replication_local_address 172.17.139.77:33061
MGR02节点配置如下
[mysqldmgr02]
datadir/data/GreatSQL/mgr02
socket/data/GreatSQL/mgr02/mysql.sock
port3307
server_id103317
log-error/data/GreatSQL/mgr02/error.log
loose-group_replication_local_address 172.17.139.77:33071
启动MGR01实例、MGR02实例并修改密码
#启动两个实例
$ systemctl restart greatsqlmgr01
$ systemctl restart greatsqlmgr02
#获取初始化密码
$ grep root /data/GreatSQL/mgr01/error.log
$ grep root /data/GreatSQL/mgr02/error.log
#登录数据库并修改密码
$ mysql -S /data/GreatSQL/mgr01/mysql.sock -uroot -p
greatsql alter user rootlocalhost identified by GreatSQL666;
$ mysql -S /data/GreatSQL/mgr02/mysql.sock -uroot -p
greatsql alter user rootlocalhost identified by GreatSQL666;
检查两个实例是否正确加载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_bin0;
# 特别注意下面因为8.0.4版本开始使用的默认是“caching_sha2_password”所以这样创建会采用最新的身份认证插件
greatsql create user repl% identified by GreatSQL666;
greatsql GRANT BACKUP_ADMIN, REPLICATION SLAVE ON *.* TO repl%;
greatsql set session sql_log_bin1;
greatsql CHANGE MASTER TO MASTER_USERrepl, MASTER_PASSWORDGreatSQL666 FOR CHANNEL group_replication_recovery;
接下来即可启动MGR集群
greatsql set global group_replication_bootstrap_groupON;greatsql start group_replication;greatsql select * from performance_schema.replication_group_members\G
*************************** 1. row ***************************CHANNEL_NAME: group_replication_applierMEMBER_ID: 2920447e-35bf-11ee-89a5-00163e566da1MEMBER_HOST: 172.17.139.77MEMBER_PORT: 3306MEMBER_STATE: ONLINEMEMBER_ROLE: PRIMARYMEMBER_VERSION: 8.0.32
MEMBER_COMMUNICATION_STACK: XCom
MGR02实例操作
greatsql set session sql_log_bin0;
greatsql create user repl% identified by GreatSQL666;
greatsql GRANT BACKUP_ADMIN, REPLICATION SLAVE ON *.* TO repl%;
greatsql set session sql_log_bin1;
greatsql CHANGE MASTER TO MASTER_USERrepl, MASTER_PASSWORDGreatSQL666 FOR CHANNEL group_replication_recovery;
greatsql start group_replication;
Query OK, 0 rows affected (5.39 sec)
此时创建的用户采用的都是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成功但是查看下节点状态
greatsql select * from performance_schema.replication_group_members\G
*************************** 1. row ***************************CHANNEL_NAME: group_replication_applierMEMBER_ID: 2920447e-35bf-11ee-89a5-00163e566da1MEMBER_HOST: 172.17.139.77MEMBER_PORT: 3306MEMBER_STATE: ONLINEMEMBER_ROLE: PRIMARYMEMBER_VERSION: 8.0.32
MEMBER_COMMUNICATION_STACK: XCom
*************************** 2. row ***************************CHANNEL_NAME: group_replication_applierMEMBER_ID: 2a4f068b-35bf-11ee-9504-00163e566da1MEMBER_HOST: 172.17.139.77MEMBER_PORT: 3307MEMBER_STATE: RECOVERINGMEMBER_ROLE: SECONDARYMEMBER_VERSION: 8.0.32
MEMBER_COMMUNICATION_STACK: XCom
2 rows in set (0.00 sec)
此时节点一直处于RECOVERING状态查看mgr02实例的错误日志如下
2023-08-08T08:00:47.034870Z 42 [ERROR] [MY-010584] [Repl] Slave I/O for channel group_replication_recovery: error connecting to master repl172.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 对密码安全性要求更高要求用户认证过程中在网络传输的密码是加密的所以导致的这个问题的出现caching_sha2_password的介绍可以看社区文章“浅谈 MySQL 新的身份验证插件 caching_sha2_password【微信导入、微信导入】”
解决方式
1、采用旧密码验证插件
旧的身份验证插件mysql_native_passwordmysql_native_password的特点是不需要加密的连接。该插件验证速度特别快但是不够安全只需要更改创建用户的语句
create user repl% identified with mysql_native_password by GreatSQL666;
旧密码验证插件容易被破解如果有 GreatSQL 服务要公网上使用建议还是尽量使用 caching_sha2_password作为认证插件
2、启用group_replication_recovery_get_public_key
设置 group_replication_recovery_get_public_keyON 可以确保从节点在连接到主节点时能够获取所需的公钥从而允许安全连接并成功进行身份验证避免了连接错误和身份验证问题。
手册中也有明确说明
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_userThe less secure approach is to set group_replication_recovery_get_public_keyON 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_keyON when you are sure there is no risk of server identity being compromised, for example by a man-in-the-middle attack.
可以看到当确认环境安全以及没人任何人攻击集群时如果不配置ssl可以最低配置group_replication_recovery_get_public_keyON来在请求复制用户密钥时给公钥
3、为组复制通道启用SSL支持 以下操作方法仅使用于 GreatSQL/MySQL 8.0.27版本及以上 更安全的方法是将repl用户所需的公钥文件复制到joiner节点的Server所在主机中。然后在joiner节点的Server中配置group_replication_recovery_public_key_path系统变量指定rpl_user用户所需的公钥文件路径。
使用caching_sha2_password 插件身份验证会在数据目录下生成如下两个RSA文件
private_key.pem
public_key.pem
private_key.pemRSA私钥public_key.pem RSA公钥
对于 MGR 如果设置 group_replication_ssl_modeDISABLED 必须使用下面的变量来指定 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_sslON
进入MGR01实例配置
greatsql set session sql_log_bin0;
# 此时就可以使用“caching_sha2_password”身份认证插件
greatsql create user repl% identified by GreatSQL666;
greatsql GRANT BACKUP_ADMIN, REPLICATION SLAVE ON *.* TO repl%;
greatsql set session sql_log_bin1;
greatsql CHANGE MASTER TO MASTER_USERrepl, MASTER_PASSWORDGreatSQL666 FOR CHANNEL group_replication_recovery;
启动MGR01实例的MGR集群
greatsql set global group_replication_bootstrap_groupON;
greatsql start group_replication;
greatsql select * from performance_schema.replication_group_members\G
*************************** 1. row ***************************CHANNEL_NAME: group_replication_applierMEMBER_ID: 35b653d2-3658-11ee-93c9-00163e566da1MEMBER_HOST: 172.17.139.77MEMBER_PORT: 3306MEMBER_STATE: ONLINEMEMBER_ROLE: PRIMARYMEMBER_VERSION: 8.0.32
MEMBER_COMMUNICATION_STACK: XCom
启动成功后需要把MGR01节点的RSA公钥拷贝到MGR02节点上,因为MGR02也会生成此公钥所以最好创建一个文件夹
$ mkdir mgr01_key
$ chown mysql:mysql mgr01_key/
# 将public_key.pem移动到MGR02
$ mv /data/GreatSQL/mgr01/public_key.pem /data/GreatSQL/mgr02/mgr01_key/ 当然如果有多个节点也需要把主节点的RSA公钥移动到各个节点上 MGR02节点操作
greatsql set session sql_log_bin0;
greatsql create user repl% identified by GreatSQL666;
greatsql GRANT BACKUP_ADMIN, REPLICATION SLAVE ON *.* TO repl%;
greatsql set session sql_log_bin1;
greatsql CHANGE MASTER TO MASTER_USERrepl, MASTER_PASSWORDGreatSQL666 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_applierMEMBER_ID: 35b653d2-3658-11ee-93c9-00163e566da1MEMBER_HOST: 172.17.139.77MEMBER_PORT: 3306MEMBER_STATE: ONLINEMEMBER_ROLE: PRIMARYMEMBER_VERSION: 8.0.32
MEMBER_COMMUNICATION_STACK: XCom
*************************** 2. row ***************************CHANNEL_NAME: group_replication_applierMEMBER_ID: aa031fb9-365a-11ee-9925-00163e566da1MEMBER_HOST: 172.17.139.77MEMBER_PORT: 3307MEMBER_STATE: ONLINEMEMBER_ROLE: SECONDARYMEMBER_VERSION: 8.0.32
MEMBER_COMMUNICATION_STACK: XCom
可以看到双节点ONLINE新加入的节点不会一直是RECOVERING状态
总结
新身份验证插件caching_sha2_password安全度相比其他的身份验证插件既解决安全性问题又解决性能问题建议使用新密码验证插件。
也感谢社区用户指出GreatSQL社区文档中的不足并给予用户金币奖励同时欢迎大家来GreatSQL社区捉虫~
Enjoy GreatSQL :)
关于 GreatSQL
GreatSQL是适用于金融级应用的国内自主开源数据库具备高性能、高可靠、高易用性、高安全等多个核心特性可以作为MySQL或Percona Server的可选替换用于线上生产环境且完全免费并兼容MySQL或Percona Server。
相关链接 GreatSQL社区 Gitee GitHub Bilibili
GreatSQL社区 社区有奖建议反馈 https://greatsql.cn/thread-54-1-1.html
社区博客有奖征稿详情 https://greatsql.cn/thread-100-1-1.html
对文章有疑问或者有独到见解都可以去社区官网提出或分享哦~
技术交流群
微信QQ群
QQ群533341697
微信群添加GreatSQL社区助手微信号wanlidbc 好友待社区助手拉您进群。
