MySQL MGR搭建过程中常遇见的问题及解决办法

MGR搭建过程中遇到的一些故障

实际中我一共部署了三套MGR环境,分别是单机多实例的MGR环境,多机同网段的MGR环境,多机不同网段的MGR环境,部署的过程大同小异,但是还是有一些有出入的地方,这里把部署过程遇到的故障列举出来,供大家参考,如果能有幸解决您在部署时候的问题,那是极好的。

01 常见故障1

[ERROR] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: bb874065-c485-11e8-8b52-000c2934472e:1 > Group transactions: 3db33b36-0e51-409f-a61d-c99756e90155:1-11'
[ERROR] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'
[Note] Plugin group_replication reported: ‘To force this member into the group you can use the group_replication_allow_local_disjoint_gtids_join option'

解决方案:

根据提示打开set global group_replication_allow_local_disjoint_gtids_join=ON;

02 常见故障2

[ERROR] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: bb874065-c485-11e8-8b52-000c2934472e:1 > Group transactions: 3db33b36-0e51-409f-a61d-c99756e90155:1-15'
[Warning] Plugin group_replication reported: 'The member contains transactions not present in the group. It is only allowed to join due to group_replication_allow_local_disjoint_gtids_join option'
[Note] Plugin group_replication reported: 'This server is working as secondary member with primary member address localhost.localdomaion:3306.'

解决方案:

该故障和故障1的不同之处在于该问题出现时,参数group_replication_allow_local_disjoint_gtids_join已经设置成为on了。解决该问题的方法是执行reset master就行,然后重新在主节点和从节点开启通道,即

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';

03 常见故障3

本机测试时,遇到下面的问题

[Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
 [ERROR] Slave I/O for channel 'group_replication_recovery': error connecting to master 'rpl_user@localhost.localdomaion:' - retry-time: 60 retries: 1, Error_code: 2005
 [ERROR] 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.'
 [ERROR] 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.'
 [Note] Plugin group_replication reported: 'Retrying group recovery connection with another donor. Attempt /'

解决方案:

这个问题是由于测试环境上三台主机的hostname设置成为了同一个名称,改了hostname之后,这个问题就解决了。

04 常见故障4

#在线上正式环境操作时,出现下面的错误,
mysql--root@localhost:(none) ::>>START GROUP_REPLICATION;
ERROR (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.
#查看log文件,发现只有一个warning:
2019-02-20T07::30.233937Z [Warning] Plugin group_replication reported: 'Group Replication requires slave-preserve-commit-order to be set to ON when using more than 1 applier threads.

解决方案:

mysql--root@localhost:(none) ::>>show variables like "%preserve%";
+--------------------------------+---------+
| Variable_name    | Value |
+--------------------------------+---------+
| slave_preserve_commit_order | OFF |
+--------------------------------+---------+
 row in set (0.01 sec)
mysql--root@localhost:(none) ::>>set global slave_preserve_commit_order=;
Query OK, rows affected (0.00 sec)

05 常见问题5

2019-02-20T08::31.088437Z [Warning] Plugin group_replication reported: '[GCS] Connection attempt from IP address 192.168.9.208 refused.
Address is not in the IP whitelist.'
2019-02-20T08::32.088676Z [Warning] Plugin group_replication reported: '[GCS] Connection attempt from IP address 192.168.9.208 refused.
 Address is not in the IP whitelist.'

解决方法:

在my.cnf中配置group_replication_ip_whitelist参数即可解决

06 常见问题6

2019-02-20T08::44.087492Z [Warning] Plugin group_replication reported: 'read failed'
2019-02-20T08::44.096171Z [ERROR] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 24801'
2019-02-20T08::14.065775Z [ERROR] Plugin group_replication reported: 'Timeout on wait for view after joining group

解决方案:

将my.cnf中的参数group_replication_group_seeds设置为只包含除自身外其他group成员的ip地址以及内部通信端口,如果写成group所有成员的IP地址,则会出现这个错误,这和相同网段的MGR部署方式有些差异。

07 常见问题7

 [ERROR] Plugin group_replication reported: ‘[GCS] Error on opening a connection to oceanbase07: on local port: '.'
 [ERROR] Plugin group_replication reported: ‘[GCS] Error on opening a connection to oceanbase08: on local port: '.'
 [ERROR] Plugin group_replication reported: ‘[GCS] Error on opening a connection to oceanbase07: on local port: '.'

解决方案:

未开通防火墙上的固定端口,开通防火墙之后即可解决

08 常见问题8

[Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
 [ERROR] Slave I/O for channel 'group_replication_recovery': Master command COM_REGISTER_SLAVE failed: Access denied for user 'rpl_user'@'%' (using password: YES) (Errno: 1045), Error_code: 1597
 [ERROR] Slave I/O thread couldn't register on master
 [Note] Slave I/O thread exiting for channel 'group_replication_recovery', read up to log 'FIRST', position 

解决方案:

漏掉了某个节点的用户,为了保险起见,在group节点上执行

CREATE USER rpl_user@'%';

GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';

09 常见问题9

 [ERROR] Failed to open the relay log './localhost-relay-bin.000011' (relay_log_pos ).
 [ERROR] Could not find target log file mentioned in relay log info in the index file './work_NAT_1-relay-bin. index' during relay log initialization.
 [ERROR] Slave: Failed to initialize the master info structure for channel ''; its record may still be present in 'mysql.slave_master_info' table, consider deleting it.
 [ERROR] Failed to open the relay log './localhost-relay-bin-group_replication_recovery.000001' (relay_log_pos  ).
 [ERROR] Could not find target log file mentioned in relay log info in the index file './work_NAT_1-relay-bin-group_replication_recovery.index' during relay log initialization.
 [ERROR] Slave: Failed to initialize the master info structure for channel 'group_replication_recovery'; its record may still be present in 'mysql.slave_master_info' table, consider deleting it.
 [ERROR] Failed to create or recover replication info repositories.
 [ERROR] Slave SQL for channel '': Slave failed to initialize relay log info structure from the repository, Error_code:
 [ERROR] /usr/local/mysql/bin/mysqld: Slave failed to initialize relay log info structure from the repository
 [ERROR] Failed to start slave threads for channel ''

解决方案:

这个错误是由于slave节点由于某种原因导致找不到relay-log的位置了,需要重新reset slave

以上就是MySQL MGR搭建过程中常遇见的问题及解决办法的详细内容,更多关于MySQL MGR搭建的资料请关注我们其它相关文章!

(0)

相关推荐

  • 详解MySQL 5.7 MGR单主确定主节点方法

    我们行MGR年底要上线了,每天都要看官方文档学习,做测试,坚持每天写一个小知识点,有想一起学习的么~ MySQL 5.7 MGR单主确定主节点是哪个,我们可以通过成员ID来判断,然后结合read_only参数来确认. [root@localhost ~]# mysql -uroot -p -P 3306 -h 127.0.0.1 mysql: [Warning] Using a password on the command line interface can be insecure. Wel

  • mysql 8.0.18 mgr 搭建及其切换功能

    一.系统安装包 yum -y install make gcc-c++ cmake bison-devel ncurses-devel readline-devel libaio-devel perl libaio wget lrzsz vim libnuma* bzip2 xz 二.关闭防火墙和selinux sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config setenforce 0 /etc/init.d/i

  • MySQL 8.0.18使用clone plugin重建MGR的实现

    假设三节点MGR某个节点异常,需要重新把这个节点加入到MGR集群中,具体操作过程如下: 贡献者端执行(192.168.1.11) DROP USER 'donor_clone_user'@'192.168.1.12'; CREATE USER 'donor_clone_user'@'192.168.1.12' IDENTIFIED BY 'password'; GRANT BACKUP_ADMIN on *.* to 'donor_clone_user'@'192.168.1.12'; INST

  • MySQL 8.0.15配置MGR单主多从的方法

    一.简介 MySQL Group Replication(简称MGR)字面意思是mysql组复制的意思,但其实他是一个高可用的集群架构,暂时只支持mysql5.7和mysql8.0版本. 是MySQL官方于2016年12月推出的一个全新的高可用与高扩展的解决方案,提供了高可用.高扩展.高可靠的MySQL集群服务. 也是mysql官方基于组复制概念并充分参考MariaDB Galera Cluster和Percona XtraDB Cluster结合而来的新的高可用集群架构. MySQL Grou

  • MySQL MGR 有哪些优点

    MGR(Mysql Group Replication)是5.7版本新加的特性,是一个MySQL插件. MGR 是一个新的高可用与高扩展的方案,集群中的任何节点数据都是一样的,可以实现任何节点都可以写入,实现了真正意义上的多主. 主要包含以下几部分: API层:负责完成和MySQL Server的交互,得到Server状态,完成事务的管理. 组件层:主要包括3个特定组件,Capture负责收集事务执行的相关信息,Applier负责应用集群事务到本地,Recovery负责节点的数据恢复. 复制层:

  • mysql MGR 单主多主模式切换知识点详解

    主库执行 CREATE DATABASE test CHARACTER SET utf8 COLLATE utf8_general_ci; use test; create table if not exists h1 (id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL); insert into test.h1 values(1,"wang"),(2,"guo"),(3,"ya

  • MySQL MGR搭建过程中常遇见的问题及解决办法

    MGR搭建过程中遇到的一些故障 实际中我一共部署了三套MGR环境,分别是单机多实例的MGR环境,多机同网段的MGR环境,多机不同网段的MGR环境,部署的过程大同小异,但是还是有一些有出入的地方,这里把部署过程遇到的故障列举出来,供大家参考,如果能有幸解决您在部署时候的问题,那是极好的. 01 常见故障1 [ERROR] Plugin group_replication reported: 'This member has more executed transactions than those

  • MySQL 处理插入过程中的主键唯一键重复值的解决方法

    本篇文章主要介绍在插入数据到表中遇到键重复避免插入重复值的处理方法,主要涉及到IGNORE,ON DUPLICATE KEY UPDATE,REPLACE:接下来就分别看看这三种方式的处理办法. IGNORE 使用ignore当插入的值遇到主键(PRIMARY KEY)或者唯一键(UNIQUE KEY)重复时自动忽略重复的记录行,不影响后面的记录行的插入, 创建测试表 CREATE TABLE Tignore (ID INT NOT NULL PRIMARY KEY , NAME1 INT )d

  • Hadoop环境搭建过程中遇到的问题及解决方法

    1.启动hadoop之前,ssh免密登录slave主机正常,使用命令start-all.sh启动hadoop时,需要输入slave主机的密码,说明ssh文件权限有问题,需要执行以下操作: 1)进入.ssh目录下查看是否有公钥私钥文件authorized_keys.id_rsa.id_rsa.pub 2)如果没有公钥私钥文件,则执行ssh-keygen -t rsa生成秘钥(master主机和slave主机都需要执行) 3)公钥私钥文件生成完成后,执行cat id_rsa.pub >> auth

  • wamp中mysql安装时能启动重启后无法启动的解决办法

    第一次安装wamp之后,所有服务可以正常使用,但是重启之后wamp的图标就变成黄色的了,重装了也这样 查看一下错误日志: 日志显示的错误是这样的: 日志提示可能是3306端口被占用的错误,那来看一下是哪个程序占用了3306端口: windows下运行cmd ,输入 netstat -aon|findstr "3306" 可以看到是pid为2092这个程序占用了3306端口,把他结束掉 输入指令: taskkill /f /pid 2092 成功之后重启wamp,正常启动! 总结 以上所

  • VisualStudio Community2019在安装的过程中无法进入安装界面的解决方法

    今天在安装VS2019的时候,在安装的过程中一直无法进入安装界面,在网上找了各种方法试了将近40分钟都没有找到有效的办法,不过就快放弃的时候,问题解决了,哈哈哈!!!! 1.下载地址:https://visualstudio.microsoft.com/zh-hans/thank-you-downloading-visual-studio/?sku=Community&rel=16(官网) 2.运行之后,读完进度条之后,就退出了,无法进入到安装界面.类似下面的截图,截图是网上找的,懒得自己在重新

  • PhpMyAdmin中无法导入sql文件的解决办法

    PhpMyAdmin中无法导入sql文件的解决办法 在命令窗口: mysql>source d:/datafilename.sql

  • JSP 中使用cache取值出错解决办法

     JSP  中使用cache取值出错解决办法 这段时间发现系统取数据过程中,偶尔出现取数据错乱的问题,按逻辑应该取出A数据,结果取出了B数据.仔细检查了代码, 发现代码逻辑没有问题,瞬间就蒙了,是哪里出现问题了呢.仔细想了一下,以前都没出现问题,自从加了缓存之后就偶尔出现了问题,那肯定问题是缓存有问题. 仔细研究了缓存的源码,原来问题出现在DefaultKeyGenerator生成key上面.代码如下: public class DefaultKeyGenerator implements Ke

  • Android开发中requestfocus()无效的原因及解决办法

    前言 最近做公司项目的时候,经常会遇到一个问题,就是我为某个控件如EditText设置requestfocus()的时候不管用,比如说登陆的时候,我判断下用户输入的密码,如果正确就登陆,错误就提示密码错误,并且输入框获取焦点,但是实际中确不起作用 package com.example.hfs.requestfocusdemo; import android.content.Intent; import android.support.v7.app.AppCompatActivity; impo

  • 解决Mysql数据库插入数据出现问号(?)的解决办法

    首先,我用的mysql数据库是5.7.12版本. 出现的问题: 1.插入数据显示错误,插入不成功,出现:Incorrect string value: '\xCD\xF5\xD5\xBC\xBE\xA9' for column 'Sname' at row 1 2.插入中文,虽然插入成功,但是显示:?? 解决方法: 在my.ini文件中的 [mysqld] 中加入 #character-set-server=utf8 如图所示,必须在蓝圈的上方,就是说,蓝圈内的内容必须在[mysqld]的最下面

  • Java中的内存泄露问题和解决办法

    目录 为什么会产生内存泄漏? 内存泄漏对程序的影响? 如何检查和分析内存泄漏? 常见的内存泄漏及解决方法 1.单例造成的内存泄漏 2.非静态内部类创建静态实例造成的内存泄漏[已无] 3.Handler造成的内存泄漏 4.线程造成的内存泄漏 5.资源未关闭造成的内存泄漏 6.使用ListView时造成的内存泄漏 7.集合容器中的内存泄露 8.WebView造成的泄露 如何避免内存泄漏? 总结 (Memory Leak,内存泄漏) 为什么会产生内存泄漏? 当一个对象已经不需要再使用本该被回收时,另外

随机推荐