mgr由于建表字段字符数过大，binlog空间占满等导致从节点脱离的处理恢复操作

mgr由于建表字段字符数过大，binlog空间占满等导致从节点脱离的处理恢复操作.

故障现象

从图上看节点只剩107，并且是offline状态，mgr集群脱离。
主节点107日志错误信息：可看出存在部分表缺少主键，字段长度过大等问题
tail -100 /data/mysql/xxx.err

2023-07-11T20:04:22.412760+08:00 2167458 [ERROR] [MY-011542] [Repl] Plugin group_replication reported: 'Table abirex_actual does not have any PRIMARY KEY. This is not compatible with Group Replication.'
2023-07-12T09:53:14.130594+08:00 2185076 [Warning] [MY-011825] [InnoDB] Cannot add field `RUDF272_VALUE` in table `kettle`.`mdm_master_product_info` because after adding it, the row size is 8136 which is greater than maximum allowed size (8126) for a record on index leaf page.
2023-07-12T09:53:14.893993+08:00 0 [Warning] [MY-011499] [Repl] Plugin group_replication reported: 'Members removed from the group: 192.168.125.108:3306'
2023-07-12T09:53:14.894069+08:00 0 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to 192.168.125.107:3306 on view 16816290336742361:27.'
2023-07-12T09:57:38.624273+08:00 2185192 [Warning] [MY-011825] [InnoDB] Cannot add field `RUDF272_VALUE` in table `kettle`.`mdm_master_product_info` because after adding it, the row size is 8136 which is greater than maximum allowed size (8126) for a record on index leaf page.
2023-07-12T09:57:38.679698+08:00 2185191 [ERROR] [MY-011542] [Repl] Plugin group_replication reported: 'Table mdm_master_product_info does not have any PRIMARY KEY. This is not compatible with Group Replication.'
2023-07-12T09:58:27.834411+08:00 2185042 [ERROR] [MY-011825] [InnoDB] Cannot add field `RUDF272_VALUE` in table `kettle`.`mdm_master_product_info` because after adding it, the row size is 8136 which is greater than maximum allowed size (8126) for a record on index leaf page.
2023-07-12T09:58:27.841181+08:00 2185042 [Warning] [MY-011825] [InnoDB] Cannot add field `RUDF272_VALUE` in table `kettle`.`#sql-ib2365-2829601330` because after adding it, the row size is 8136 which is greater than maximum allowed size (8126) for a record on index leaf page.
2023-07-12T09:58:27.882004+08:00 2185042 [Warning] [MY-011825] [InnoDB] Cannot add field `RUDF272_VALUE` in table `kettle`.`mdm_master_product_info` because after adding it, the row size is 8136 which is greater than maximum allowed size (8126) for a record on index leaf page.
2023-07-12T09:58:37.165908+08:00 2185042 [Warning] [MY-011825] [InnoDB] Cannot add field `RUDF272_VALUE` in table `kettle`.`#sql-ib2367-2829601332` because after adding it, the row size is 8134 which is greater than maximum allowed size (8126) for a record on index leaf page.
2023-07-12T09:58:37.306141+08:00 2185042 [Warning] [MY-011825] [InnoDB] Cannot add field `RUDF272_VALUE` in table `kettle`.`mdm_master_product_info` because after adding it, the row size is 8134 which is greater than maximum allowed size (8126) for a record on index leaf page.
2023-07-12T09:58:42.678297+08:00 2185042 [Warning] [MY-011825] [InnoDB] Cannot add field `RUDF272_VALUE` in table `kettle`.`#sql-a9e_215752` because after adding it, the row size is 8134 which is greater than maximum allowed size (8126) for a record on index leaf page.
2023-07-12T09:58:49.782625+08:00 2185227 [Warning] [MY-011825] [InnoDB] Cannot add field `RUDF272_VALUE` in table `kettle`.`mdm_master_product_info` because after adding it, the row size is 8134 which is greater than maximum allowed size (8126) for a record on index leaf page.
2023-07-12T14:09:04.863149+08:00 2192095 [ERROR] [MY-011542] [Repl] Plugin group_replication reported: 'Table l_storage_his does not have any PRIMARY KEY. This is not compatible with Group Replication.'
2023-07-12T14:23:35.920713+08:00 2192522 [ERROR] [MY-011542] [Repl] Plugin group_replication reported: 'Table l_storage_io does not have any PRIMARY KEY. This is not compatible with Group Replication.'
2023-07-13T00:10:08.268710+08:00 2209219 [Warning] [MY-011825] [InnoDB] Cannot add field `RUDF272_VALUE` in table `kettle`.`mdm_master_product_info` because after adding it, the row size is 8134 which is greater than maximum allowed size (8126) for a record on index leaf page.
2023-07-14T00:10:08.485917+08:00 2250203 [Warning] [MY-011825] [InnoDB] Cannot add field `RUDF272_VALUE` in table `kettle`.`mdm_master_product_info` because after adding it, the row size is 8134 which is greater than maximum allowed size (8126) for a record on index leaf page.
2023-08-14T14:07:17.495384+08:00 4171983 [ERROR] [MY-011542] [Repl] Plugin group_replication reported: 'Table badness_archives does not have any PRIMARY KEY. This is not compatible with Group Replication.'

从节点108日志报错信息：

2023-04-27T15:57:06.623855+08:00 24 [System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='192.168.125.107', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.
2023-04-27T15:57:06.636970+08:00 0 [System] [MY-011490] [Repl] Plugin group_replication reported: 'This server was declared online within the replication group.'
2023-07-12T09:53:14.174249+08:00 15 [ERROR] [MY-011825] [InnoDB] Cannot add field `RUDF272_VALUE` in table `kettle`.`mdm_master_product_info` because after adding it, the row size is 8136 which is greater than maximum allowed size (8126) for a record on index leaf page.
2023-07-12T09:53:14.208693+08:00 15 [ERROR] [MY-010584] [Repl] Slave SQL for channel 'group_replication_applier': Worker 1 failed executing transaction 'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb:379275'; Error 'Row size too large (> 8126). Chang , , Error_code: MY-0011180)0)OB may help. In current row format, BLOB prefix of 0 bytes is stored inline.' on query. Default database: 'kettle'. Query: 'CREATE TABLE MDM_MASTER_PRODUCT_INFO
2023-07-12T09:53:14.208970+08:00 14 [Warning] [MY-010584] [Repl] Slave SQL for channel 'group_replication_applier': ... 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
2023-07-12T09:53:14.209135+08:00 14 [ERROR] [MY-011451] [Repl] Plugin group_replication reported: 'The applier thread execution was aborted. Unable to process more transactions, this member will now leave the group.'2023-07-12T09:53:14.219146+08:00 12 [ERROR] [MY-011452] [Repl] Plugin group_replication reported: 'Fatal error during execution on the Applier process of Group Replication. The server will now leave the group.'
2023-07-12T09:53:14.219532+08:00 12 [ERROR] [MY-011712] [Repl] Plugin group_replication reported: 'The server was automatically set into read only mode after an error was detected.'
2023-07-12T09:53:14.220007+08:00 12 [System] [MY-011565] [Repl] Plugin group_replication reported: 'Setting super_read_only=ON.'
2023-07-12T09:53:17.797674+08:00 0 [System] [MY-011504] [Repl] Plugin group_replication reported: 'Group membership changed: This member has left the group.'

处理过程：

my.cnf增加参数：

[mysqld]
innodb_strict_mode=OFF ##字段长度过大给警告不中断
binlog_expire_logs_seconds=604800
max_allowed_packet=104857600
sql_generate_invisible_primary_key=ON ##无主键自动创建

由于从库已经脱离主库，并且binlog日志已经被手动删除https://www.modb.pro/db/1690978002171678720，所以只能考虑重建从库，通过clone的方式处理：

systemctl restart mysqld
show global variables like '%gtid%';
选择gtid值最大的作为主库，然后在107节点执行如下步骤：
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

108节点做clone恢复操作：参考：https://www.modb.pro/db/626065

mysql> select * from performance_schema.replication_group_members;
mysql> stop group_replication;
mysql> SET GLOBAL clone_valid_donor_list = '192.168.125.107:3306';
mysql> CLONE INSTANCE FROM 'root'@'192.168.125.107':3306 IDENTIFIED BY 'xxxxxx';
mysql> show variables like 'super_read_only';
mysql> set global super_read_only=off;
mysql> CLONE INSTANCE FROM 'root'@'192.168.125.107':3306 IDENTIFIED BY 'xxxxxxx';
如果提示无权限，则根据错误信息在107主节点操作（建议每个节点都操作）
mysql> grant CLONE_ADMIN,BACKUP_ADMIN on *.* to 'root@'%';
mysql> FLUSH PRIVILEGES;

处理结果恢复同步正常：

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 673c35f1-dc20-11ed-8b5b-005056a27233 | 192.168.125.107 |        3306 | ONLINE       | PRIMARY     | 8.0.32         | XCom                       |
| group_replication_applier | e64615df-dc20-11ed-80b9-005056a26559 | 192.168.125.108 |        3306 | ONLINE       | SECONDARY   | 8.0.32         | XCom                       |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
2 rows in set (0.00 sec)

主：
mysql> show variables like '%gtid%';
+----------------------------------------------+------------------------------------------------+
| Variable_name                                | Value                                          |
+----------------------------------------------+------------------------------------------------+
| binlog_gtid_simple_recovery                  | ON                                             |
| enforce_gtid_consistency                     | ON                                             |
| group_replication_gtid_assignment_block_size | 1000000                                        |
| gtid_executed                                | bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb:1-6110341 |
| gtid_executed_compression_period             | 0                                              |
| gtid_mode                                    | ON                                             |
| gtid_next                                    | AUTOMATIC                                      |
| gtid_owned                                   |                                                |
| gtid_purged                                  | bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb:1-3431582 |
| session_track_gtids                          | OFF                                            |
+----------------------------------------------+------------------------------------------------+
10 rows in set (0.00 sec)
mysql> show variables like '%gtid%';
+----------------------------------------------+------------------------------------------------+
| Variable_name                                | Value                                          |
+----------------------------------------------+------------------------------------------------+
| binlog_gtid_simple_recovery                  | ON                                             |
| enforce_gtid_consistency                     | ON                                             |
| group_replication_gtid_assignment_block_size | 1000000                                        |
| gtid_executed                                | bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb:1-6110445 |
| gtid_executed_compression_period             | 0                                              |
| gtid_mode                                    | ON                                             |
| gtid_next                                    | AUTOMATIC                                      |
| gtid_owned                                   |                                                |
| gtid_purged                                  | bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb:1-3431582 |
| session_track_gtids                          | OFF                                            |
+----------------------------------------------+------------------------------------------------+
10 rows in set (0.00 sec)


从：
mysql> show variables like '%gtid%';
+----------------------------------------------+------------------------------------------------+
| Variable_name                                | Value                                          |
+----------------------------------------------+------------------------------------------------+
| binlog_gtid_simple_recovery                  | ON                                             |
| enforce_gtid_consistency                     | ON                                             |
| group_replication_gtid_assignment_block_size | 1000000                                        |
| gtid_executed                                | bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb:1-6110462 |
| gtid_executed_compression_period             | 0                                              |
| gtid_mode                                    | ON                                             |
| gtid_next                                    | AUTOMATIC                                      |
| gtid_owned                                   |                                                |
| gtid_purged                                  | bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb:1-4919770 |
| session_track_gtids                          | OFF                                            |
+----------------------------------------------+------------------------------------------------+
10 rows in set (0.01 sec)

mysql> show variables like '%gtid%';
+----------------------------------------------+------------------------------------------------+
| Variable_name                                | Value                                          |
+----------------------------------------------+------------------------------------------------+
| binlog_gtid_simple_recovery                  | ON                                             |
| enforce_gtid_consistency                     | ON                                             |
| group_replication_gtid_assignment_block_size | 1000000                                        |
| gtid_executed                                | bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb:1-6110466 |
| gtid_executed_compression_period             | 0                                              |
| gtid_mode                                    | ON                                             |
| gtid_next                                    | AUTOMATIC                                      |
| gtid_owned                                   |                                                |
| gtid_purged                                  | bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb:1-4919770 |
| session_track_gtids                          | OFF                                            |
+----------------------------------------------+------------------------------------------------+
10 rows in set (0.00 sec)

小结：

1.规范开发要求，建表必须加主键，字段个数和长度限制问题
2.关注系统和mysql日志，定期巡检遇到问题及时分析处理