MySQL MGR成员故障处理(一)

作者简介:沈嵬,现就职于北京海天起点技术服务股份有限公司,具有多年oracle数据库维护经验,有11G OCP认证,致力于帮助客户解决生产环境出现的各种问题。

系统环境

主机名 IP 软件
mgr1 192.168.1.31 MySQL 5.7.22 配置Group Replication
mgr2 192.168.1.32 MySQL 5.7.22 配置Group Replication
mgr3 192.168.1.33 MySQL 5.7.22 配置Group Replication

故障现象

通过制造数据不一致,binlog过期造成mgr3一直处于RECOVERING状态无法同步直到放弃同步。

节点mgr1状态

节点mgr2状态

节点mgr3状态

Mgr3中mysqld.log日志中报错如下:

通过跳过GTID方式解决

修改gtid_purged值

在mgr3执行以下操作

先重置,清除gtid_purged值

When binlog_gtid_simple_recovery=TRUE, which is the default in MySQL 5.7.7 and later, the server iterates only the oldest and the newest binary log files and the values of gtid_purged and gtid_executed are computed based only on Previous_gtids_log_event or Gtid_log_event found in these files. This ensures only two binary log files are iterated during server restart or when binary logs are being purged.

Note

If this option is enabled, gtid_executed and gtid_purged may be initialized incorrectly in the following situations:

  • The newest binary log was generated by MySQL 5.7.5 or older, and gtid_mode was ON for some binary logs but OFF for the newest binary log.
  • A SET GTID_PURGED statement was issued on a MySQL version prior to 5.7.7, and the binary log that was active at the time of the SET GTID_PURGED has not yet been purged.

If an incorrect GTID set is computed in either situation, it will remain incorrect even if the server is later restarted, regardless of the value of this option.

[root@mgr1 data]# more binlog.index

./binlog.000027

./binlog.000028

[root@mgr1 data]# mysqlbinlog -vv –base64-output=decode-rows binlog.000027 |more

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#181110 15:06:48 server id 1 end_log_pos 123     Start: binlog v 4, server v 5.7.22-enterprise-commercial-advanced-log created 181110 15:06:48 at startup

ROLLBACK/*!*/;

# at 123

#181110 15:06:48 server id 1 end_log_pos 206     Previous-GTIDs

# aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-97:1000014

# at 206

#181110 15:15:57 server id 1 end_log_pos 225     Stop

SET @@SESSION.GTID_NEXT= ‘AUTOMATIC’ /* added by mysqlbinlog */ /*!*/;

DELIMITER ;

# End of log file

官方文档描述gtid_purged值是基于最旧binlog日志文件的Previous_gtids_log_event值,即1-97:1000014。

根据mgr1与mgr2的gtid_purged ,更改mgr3的gtid_purged,使其从该gtid_purged开始复制,即不再复制主库之前执行过已经丢失binlog的gtid,这种跳过事务同步的方法存在数据不一致的隐患。

测试

Mgr1:

Mgr3:

在mgr1中插入一条记录然后删除一条记录

在mgr3中查看

新记录已经同步,但是由于之前制造故障删了一条记录所以这条记录需要同步前手工加上否则用到时会导致同步报错断开。

通过备份恢复方式解决

安装MySQL企业级备份工具(MEB)

Mgr1执行:

将备份传到mgr3:

Mgr3执行:

 

 

未经允许不得转载:Oracle一体机用户组 » MySQL MGR成员故障处理(一)

相关推荐