GreatSQL社区

搜索

[待回复] mgr单主; 备库报错 Error_code: MY-001032

67 7 2022-11-22 17:34
本帖最后由 Young 于 2022-11-22 17:37 编辑

环境:

mysql 8.0.31 MGR; 3个节点,单主; 测试环境

报错的原因: 在 第二个从库,执行了 update mysql.user host='%' where user='root';
由于没有设置 SET SQL_LOG_BIN=0;  导致binlog 散播到了 第一个从节点; 但是 primary居然没有报错;

需要请教的问题:
1. 这种我知道的怎么跳过去
2. 不能跳过只能重做嘛


日志如下:

2022-11-22T09:25:38.785356Z 253 [ERROR] [MY-010584] [Repl] Slave SQL for channel 'group_replication_recovery': Worker 1 failed executing transaction '0b15d45f-6e29-11ec-99b3-08002790c7d5:1000078' at master log binlog.000006, end_log_pos 35847; Could not execute Update_rows event on table mysql.user; Can't find record in 'user', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log FIRST, end_log_pos 35847, Error_code: MY-001032
2022-11-22T09:25:38.785426Z 252 [Warning] [MY-010584] [Repl] Slave SQL for channel 'group_replication_recovery': ... The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details). Error_code: MY-001756
2022-11-22T09:25:38.792585Z 200 [System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='mgr1', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='mgr3', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.
2022-11-22T09:25:38.799128Z 257 [Warning] [MY-010897] [Repl] 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.
2022-11-22T09:25:38.799660Z 257 [System] [MY-010562] [Repl] Slave I/O thread for channel 'group_replication_recovery': connected to master 'repl@mgr3:3306',replication started in log 'FIRST' at position 4
2022-11-22T09:25:38.805538Z 259 [ERROR] [MY-010584] [Repl] Slave SQL for channel 'group_replication_recovery': Worker 1 failed executing transaction '0b15d45f-6e29-11ec-99b3-08002790c7d5:1000078' at master log binlog.000006, end_log_pos 35676; Could not execute Update_rows event on table mysql.user; Can't find record in 'user', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log FIRST, end_log_pos 35676, Error_code: MY-001032
2022-11-22T09:25:38.805824Z 258 [ERROR] [MY-010586] [Repl] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'binlog.000006' position 35060
2022-11-22T09:25:38.806252Z 200 [ERROR] [MY-011574] [Repl] Plugin group_replication reported: 'Maximum number of retries when trying to connect to a donor reached. Aborting group replication incremental recovery.'
2022-11-22T09:25:38.806270Z 200 [ERROR] [MY-011620] [Repl] Plugin group_replication reported: 'Fatal error during the incremental recovery process of Group Replication. The server will leave the group.'
2022-11-22T09:25:38.806320Z 200 [ERROR] [MY-011712] [Repl] Plugin group_replication reported: 'The server was automatically set into read only mode after an error was detected.'
2022-11-22T09:25:41.869919Z 0 [System] [MY-011504] [Repl] Plugin group_replication reported: 'Group membership changed: This member has left the group.'



全部回复(7)
yejr 2022-11-22 17:36:45
这种情况下,可以把这个update sql拿到primary节点去再执行一遍
另外两个节点从primary节点执行恢复操作
yejr 2022-11-22 17:37:30
另外,mgr单主模式下,务必不能在secondary节点执行dml等意外操作,也不要关闭read only模式
yejr 2022-11-22 17:39:25
至于这个操作为什么没有被复制到primary节点,就需要先确认一点:这个event有没有复制到priamry上的relay log中。如果有的话,再检查确认为何不能被apply。
Young 2022-11-22 23:24:35
yejr 发表于 2022-11-22 17:39
至于这个操作为什么没有被复制到primary节点,就需要先确认一点:这个event有没有复制到priamry上的relay l ...

谢谢叶总!event没有到primary; 我重复了一下过程; 原因是 slave 1 stop MGR了; slave 2没有stop mgr;  感觉有点奇怪;哈哈,但是知道怎么回事就好了
Young 2022-11-22 23:25:25
yejr 发表于 2022-11-22 17:39
至于这个操作为什么没有被复制到primary节点,就需要先确认一点:这个event有没有复制到priamry上的relay l ...

是的; 因为我懒,在看叶总的 mysqlshell 管理 MGR, 懒得建新用户, 采用了懒人模式,直接update root
yejr 2022-11-23 08:59:16
Young 发表于 2022-11-22 23:24
谢谢叶总!event没有到primary; 我重复了一下过程; 原因是 slave 1 stop MGR了; slave 2没有stop mgr; ...

slave1因为event冲突了,所以报错,stop退出mgr,是正常行为。

slave2按理应该也会报错退出的,有可能是8031版本有bug?你拿GreatSQL 8.0.25-16测试下看看呢。
yejr 2022-11-23 09:00:03
Young 发表于 2022-11-22 23:25
是的; 因为我懒,在看叶总的 mysqlshell 管理 MGR, 懒得建新用户, 采用了懒人模式,直接update root:l ...

mysql shell已经是最简懒人模式,就不要再偷懒了
Young

1

主题

0

博客

4

贡献

新手上路

Rank: 1

积分
7

合作电话:010-64087828

社区邮箱:greatsql@greatdb.com

社区公众号
群助手
QQ群
GMT+8, 2022-12-10 03:31 , Processed in 0.031693 second(s), 20 queries , Redis On.
快速回复 返回顶部 返回列表