mysql8.30 mgr安装

最新推荐文章于 2024-03-11 20:06:39 发布

家有两宝92

最新推荐文章于 2024-03-11 20:06:39 发布

阅读量552

点赞数 1

文章标签：数据库 mysql Powered by 金山文档

本文链接：https://blog.csdn.net/weixin_65195414/article/details/128877798

版权

1、环境准备

192.168.31.31
192.168.31.32
192.168.31.33
OS : CentOS 7.4
mysql: 8.30(和mysql使用基本没区别)

在虚拟机192.168.31.31安装mysql 8.30，然后复制两台，并修改ip为192.168.31.32，192.168.31.33

由于这些操作太基础，这里就不做演示了

设置192.168.31.31 /etc/my.cnf文件内容，三机机器基本都一样，只有两个属性需要修改"server_id","loose-group_replication_local_address"：

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

server_id = 1  #服务 ID，每台机器不同
gtid_mode = ON  #全局事务
enforce_gtid_consistency = ON #强制 GTID 的一致性
master_info_repository = TABLE #将 master.info 元数据保存在系统表中
relay_log_info_repository = TABLE #将 relay.info 元数据保存在系统表中
binlog_checksum = NONE  #禁用二进制日志事件校验
log_slave_updates = ON  #级联复制
log_bin = binlog #开启二进制日志记录
binlog_format = ROW #以行的格式记录

# Group Replication
#加载组复制组件
plugin_load_add='group_replication.so'
transaction_write_set_extraction = XXHASH64 #使用哈希算法将其编码为散列
loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212844f856' #加入的组名,这里都得一致
loose-group_replication_start_on_boot = off #不自动启用组复制集群
loose-group_replication_local_address = '192.168.31.31:33061' #以本机端口 33061 接受来自组中成员的传入连接
loose-group_replication_group_seeds = '192.168.31.31:33061,192.168.31.32:33061,192.168.31.33:33061' #组中成员访问表
loose-group_replication_bootstrap_group = off #不启用引导组 ，组中只在一台机器上设置该变量为On，三台机器都设置为Off

第二个节点修改内容如下：

server_id=2
loose-group_replication_local_address= "192.168.31.32:33061"

第三个节点修改内容如下：

server_id=3
loose-group_replication_local_address= "192.168.31.33:33061"

三个节点添加完配置信息之后，分别重启MySQL服务，以使配置生效：

service mysql restart

4.2、创建复制账号（三个节点均需配置）

登录mysql，各节点执行如下命令

SET SQL_LOG_BIN=0;
CREATE USER mgruser@'%' IDENTIFIED BY 'a&#bdef4gB';
GRANT REPLICATION SLAVE ON *.* TO mgruser@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='mgruser', MASTER_PASSWORD='a&#bdef4gB' FOR CHANNEL 'group_replication_recovery';

安装MGR复制插件

mysql> install PLUGIN group_replication SONAME 'group_replication.so'; #这步不需要做，因为配置文件中已经配置了
# 查看group replication组件，status为active表示生效
mysql> show plugins;
+--------------------------+----------+--------------------+----------------------+---------+
| Name                     | Status   | Type               | Library              | License |
+--------------------------+----------+--------------------+----------------------+---------+
| group_replication        | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |
+--------------------------+----------+--------------------+----------------------+---------+
47 rows in set (0.00 sec)

放开端口，三台机器都要执行：

firewall-cmd --zone=public add-port=3306/tcp add-port=33061/tcp --permanent
firewall-cmd --reload
firewall-cmd --list-ports

4.4、启动MGR单主模式

4.4.1、主节点操作

1> 在主节点（当前主节点为：192.168.31.31）启动MGR，执行如下命令

mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION; #这里会报错，叫查看日志文件
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

报错后我们查询日错误信息：

> cat /var/log/mysqld.log

2023-02-03T14:42:15.166098Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] The group communication engine failed to test connectivity to the local group communication engine on 192.168.31.31:33061. This may be due to one or more invalid configuration settings. Double-check your group replication local address, firewall, SE Linux and TLS configurations and try restarting Group Replication on this server.'
2023-02-03T14:42:15.216606Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 33061'
2023-02-03T14:42:20.724995Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] The group communication engine failed to test connectivity to the local group communication engine on 192.168.31.31:33061. This may be due to one or more invalid configuration settings. Double-check your group replication local address, firewall, SE Linux and TLS configurations and try restarting Group Replication on this server.'

解决措施如下：

# 1.关闭SELinux，不太安全，不特别推荐
setenforce 0
# 2.开放通讯端口（推荐）
yum install -y policycoreutils-python
semanage port -a -t mysqld_port_t -p tcp 33061

2> 查看MGR组信息

mysql> SELECT * FROM performance_schema.replication_group_members;

4.4.2、从节点操作

1> 从节点加入MGR，在从库（192.168.91.132，192.168.91.133）上执行如下命令

mysql> START GROUP_REPLICATION;  -- 此时会报错，查看日志文件
[ERROR] [MY-011516] [Repl] Plugin group_replication reported: 'There is already a member with server_uuid b78319f1-9809-11ed-823c-000c292fe3af. The member will now exit the group.'

原因分析：克隆的虚拟机导致mysql主从UUID一致，

修改方案：

select uuid(); -- 通过mysql生成一个uuid用于修改；

进入配置文件，修改uuid

vim /var/lib/mysql/auto.cnf

[auto]
server-uuid=2dd38585-a3d8-11ed-932e-000c291894d9

重启：

#重启服务器 
systemctl restart network
#重启Mysql服务
systemctl restart mysqld
#重新登录数据库。

第二个问题：

2023-02-03T15:35:18.978503Z 0 [ERROR] [MY-011526] [Repl] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: b78319f1-9809-11ed-823c-000c292fe3af:1-2 > Group transactions: ce9be252-2b71-11e6-b8f4-00212844f856:1-5'

主从gtid不一致，重启mster即可：

reset master;

第三个问题，查看成员信息时，status始终为连接中，查看错误信息如下：

2023-02-03T16:01:25.722218Z 163 [ERROR] [MY-010584] [Repl] Slave I/O for channel 'group_replication_recovery': error connecting to master 'mgruser@localhost.localdomain:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061
2023-02-03T16:01:25.724281Z 134 [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-02-03T16:01:25.724299Z 134 [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.'

在三台机器的配置，根据情况修改：

report_host=192.168.31.31
report_port=3306

再次执行命令：

START GROUP_REPLICATION;

再次查看MGR组信息

mysql> select CHANNEL_NAME,MEMBER_STATE,MEMBER_ROLE from performance_schema.replication_group_members;
+---------------------------+--------------+-------------+
| CHANNEL_NAME              | MEMBER_STATE | START |
+---------------------------+--------------+-------------+
| group_replication_applier | ONLINE       | SECONDARY   |
| group_replication_applier | ONLINE       | SECONDARY   |
| group_replication_applier | ONLINE       | PRIMARY     |
+---------------------------+--------------+-------------+
3 rows in set (0.00 sec)

至此成功