记录一次基于已有数据库搭建mysql MGR 搭建遇到的问题

问题是启动从服务时日志无线报以下错误

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)