问题是启动从服务时日志无线报以下错误
2022-04-26T02:11:00.616671Z 156 [ERROR] [MY-010584] [Repl] Slave SQL for channel 'group_replication_recovery': Could not execute Update_rows event on table zongmu.ht_biz_equipment_status; Can't find record in 'ht_biz_equipment_status', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000001, end_log_pos 820, Error_code: MY-001032 2022-04-26T02:11:00.616784Z 156 [Warning] [MY-010584] [Repl] Slave: Can't find record in 'ht_biz_equipment_status' Error_code: MY-001032 2022-04-26T02:11:00.616847Z 156 [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 'mysql-bin.000001' position 473
为了解决这个问题 用了以下方法
1、reset slave；reset master；主服务器 从服务器都执行了无数遍
2、删除主服务器带binlog的文件、删除从服务器带binlog文件，重启mysql服务器，最后启动仍然报错
3、我发现我被这一行日志误导了 Could not execute Update_rows 一直觉得是原有数据的日志有问题，导致的主从失败。
最后解决方式：从binlog日志入手 查看binlog日志
mysql> show binlog events; +---------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | binlog.000001 | 4 | Format_desc | 2222 | 125 | Server ver: 8.0.25, Binlog ver: 4 | | binlog.000001 | 125 | Previous_gtids | 2222 | 152 | | | binlog.000001 | 152 | Gtid | 1111 | 234 | SET @@SESSION.GTID_NEXT= 'ce9be252-2b71-11e6-b8f4-00212844f999:1' | | binlog.000001 | 234 | Query | 1111 | 301 | BEGIN | | binlog.000001 | 301 | View_change | 1111 | 400 | view_id=16509389200450231:1 | | binlog.000001 | 400 | Query | 1111 | 473 | COMMIT | +---------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
在主库中找到通步报错的语句pos 473 - 820
mysql> show binlog events; +------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+ | mysql-bin.000001 | 4 | Format_desc | 1111 | 125 | Server ver: 8.0.25, Binlog ver: 4 | | mysql-bin.000001 | 125 | Previous_gtids | 1111 | 152 | | | mysql-bin.000001 | 152 | Gtid | 1111 | 234 | SET @@SESSION.GTID_NEXT= 'ce9be252-2b71-11e6-b8f4-00212844f999:1' | | mysql-bin.000001 | 234 | Query | 1111 | 301 | BEGIN | | mysql-bin.000001 | 301 | View_change | 1111 | 400 | view_id=16509389200450231:1 | | mysql-bin.000001 | 400 | Query | 1111 | 473 | COMMIT | | mysql-bin.000001 | 473 | Gtid | 1111 | 555 | SET @@SESSION.GTID_NEXT= 'ce9be252-2b71-11e6-b8f4-00212844f999:2' | | mysql-bin.000001 | 555 | Query | 1111 | 641 | BEGIN | | mysql-bin.000001 | 641 | Table_map | 1111 | 718 | table_id: 146 (zongmu.ht_biz_equipment_status) | | mysql-bin.000001 | 718 | Update_rows | 1111 | 820 | table_id: 146 flags: STMT_END_F | | mysql-bin.000001 | 820 | Xid | 1111 | 847 | COMMIT /* xid=35231 */ | | mysql-bin.000001 | 847 | Gtid | 1111 | 929 | SET @@SESSION.GTID_NEXT= 'ce9be252-2b71-11e6-b8f4-00212844f999:3' | | mysql-bin.000001 | 929 | Query | 1111 | 1015 | BEGIN |
可以发现 这里没有任何的行数据 猜想可能是表结构问题 查看表结构 发现该表使用的是联合主键
而MGR对表的主键有限制 更改表结构 添加唯一主键 然后重置日志（reset master/slave;）重启 发现成功了！
mysql> reset master; Query OK, 0 rows affected (0.13 sec) mysql> reset slave; Query OK, 0 rows affected, 1 warning (0.44 sec) mysql> change master to master_user='slave',master_password='XM_zm2019' for channel 'group_replication_recovery'; Query OK, 0 rows affected, 5 warnings (0.30 sec) mysql> start group_replication;
mysql> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 0578a9ec-b168-11ec-bed2-00155d0a6e15 | SUSE68 | 3306 | ONLINE | PRIMARY | 8.0.25 | | group_replication_applier | d67b19a8-b15d-11ec-a1eb-00155d0a6e14 | SUSE67 | 3306 | ONLINE | PRIMARY | 8.0.25 | | group_replication_applier | d67b19a8-b15d-11ec-a1eb-00155d0a6e16 | SUSE65 | 3306 | ONLINE | PRIMARY | 8.0.25 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.00 sec)
mysql主从 和mgr感觉都太不稳定了 极容易出问题 可能也与原来业务使用不规范建表有关系 fk !