发帖
翼度科技»论坛 › 编程开发 › mysql › 查看内容

MySQL The instance is already part of another Replication Group

张飞飞

6

主题

6

帖子

18

积分

新手上路

Rank: 1

积分
18
显示全部楼层
MySQL InnoDB Cluster(测试环境为MySQL 8.0.35)将一个实例重新加入集群时，遇到了下面这个错误"The instance 'dbu03:3306' is already part of another Replication Group"
  1. MySQL  10.160.2.55:3306 ssl  JS > cluster.addInstance('cdmin@10.160.2.62:3306')
  2. ERROR: RuntimeError: The instance 'dbu03:3306' is already part of another Replication Group
  3. Cluster.addInstance: The instance 'dbu03:3306' is already part of another Replication Group (RuntimeError)
  4. MySQL  10.160.2.55:3306 ssl  JS > 
复制代码
那么如何解决这个问题呢？官方文档介绍，碰到这种情况是遇到了一个Bug，可以通过mysql shell连接到这个实例(加入cluster遇到问题的实例，此案例为:10.160.2.62)
然后执行下面命令
  1. shell.options.verbose=3
  2. shell.options["dba.logSql"]=2
  3. shell.options["logLevel"]=8
  4. \sql
  5. stop group_replication;
  6. \js
  7. dba.dropMetadataSchema();
复制代码
实际执行过程如下所示：
  1.  MySQL  10.160.2.62:3306 ssl  JS > shell.options.verbose=3
  2. 3
  3.  MySQL  10.160.2.62:3306 ssl  JS > shell.options["dba.logSql"]=2
  4. 2
  5.  MySQL  10.160.2.62:3306 ssl  JS > shell.options["logLevel"]=8
  6. 8
  7.  MySQL  10.160.2.62:3306 ssl  JS > \sql
  8. Switching to SQL mode... Commands end with ;
  9. Fetching global names for auto-completion... Press ^C to stop.
  10. stop group_replication; MySQL  10.16stop group_replication;
  11. Query OK, 0 rows affected (0.0050 sec)
  12.  MySQL  10.160.2.62:3306 ssl  SQL > \js
  13. Switching to JavaScript mode...
  14.  MySQL  10.160.2.62:3306 ssl  JS > \js
  15.  MySQL  10.160.2.62:3306 ssl  JS > dba.dropMetadataSchema();
  16. verbose: 2024-06-13T08:33:55Z: Connecting to MySQL at: mysql://cdmin@10.160.2.62:3306?connect-timeout=5000
  17. verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: CONNECTED: 10.160.2.62:3306
  18. verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SET SESSION `autocommit` = 1
  19. verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SET SESSION `sql_mode` = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
  20. verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SET SESSION `group_replication_consistency` = 'EVENTUAL'
  21. verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SET SESSION `collation_connection` = 'utf8mb4_0900_ai_ci'
  22. verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SET SESSION `group_concat_max_len` = 1073741824
  23. verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SELECT COALESCE(@@report_host, @@hostname),  COALESCE(@@report_port, @@port)
  24. verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SELECT @@server_uuid
  25. verbose: 2024-06-13T08:33:55Z: Metadata operations will use dbu03:3306
  26. verbose: 2024-06-13T08:33:55Z: Metadata operations will use dbu03:3306
  27. verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SHOW DATABASES LIKE 'mysql_innodb_cluster_metadata'
  28. verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SHOW DATABASES LIKE 'mysql_innodb_cluster_metadata_bkp'
  29. verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: select count(*) FROM performance_schema.replication_group_members WHERE MEMBER_ID = @@server_uuid AND MEMBER_STATE NOT IN ('OFFLINE', 'UNREACHABLE')
  30. verbose: 2024-06-13T08:33:55Z: Instance type check: dbu03:3306: GR is installed but not active
  31. verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SELECT
  32.     c.channel_name, c.host, c.port, c.user,
  33.     s.source_uuid, s.group_name, s.last_heartbeat_timestamp,
  34.     s.service_state io_state, st.processlist_state io_thread_state,
  35.     s.last_error_number io_errno, s.last_error_message io_errmsg,
  36.     s.last_error_timestamp io_errtime,
  37.     co.service_state co_state, cot.processlist_state co_thread_state,
  38.     co.last_error_number co_errno, co.last_error_message co_errmsg,
  39.     co.last_error_timestamp co_errtime,
  40.     w.service_state w_state, wt.processlist_state w_thread_state,
  41.     w.last_error_number w_errno, w.last_error_message w_errmsg,
  42.     w.last_error_timestamp w_errtime,
  43.     /*!80011 TIMEDIFF(NOW(6),
  44.       IF(TIMEDIFF(s.LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP,
  45.           s.LAST_HEARTBEAT_TIMESTAMP) >= 0,
  46.         s.LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP,
  47.         s.LAST_HEARTBEAT_TIMESTAMP
  48.       )) as time_since_last_message,
  49.     IF(s.LAST_QUEUED_TRANSACTION='' OR s.LAST_QUEUED_TRANSACTION=latest_w.LAST_APPLIED_TRANSACTION,
  50.       'IDLE',
  51.       'APPLYING') as applier_busy_state,
  52.     IF(s.LAST_QUEUED_TRANSACTION='' OR s.LAST_QUEUED_TRANSACTION=latest_w.LAST_APPLIED_TRANSACTION,
  53.       NULL,
  54.       TIMEDIFF(latest_w.LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP,
  55.         latest_w.LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP)
  56.       ) as lag_from_original,
  57.     IF(s.LAST_QUEUED_TRANSACTION='' OR s.LAST_QUEUED_TRANSACTION=latest_w.LAST_APPLIED_TRANSACTION,
  58.       NULL,
  59.       TIMEDIFF(latest_w.LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP,
  60.         latest_w.LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP)
  61.       ) as lag_from_immediate,
  62.     */
  63.     GTID_SUBTRACT(s.RECEIVED_TRANSACTION_SET, @@global.gtid_executed)
  64.       as queued_gtid_set_to_apply
  65.   FROM performance_schema.replication_connection_configuration c
  66.   JOIN performance_schema.replication_connection_status s
  67.     ON c.channel_name = s.channel_name
  68.   LEFT JOIN performance_schema.replication_applier_status_by_coordinator co
  69.     ON c.channel_name = co.channel_name
  70.   JOIN performance_schema.replication_applier_status a
  71.     ON c.channel_name = a.channel_name
  72.   JOIN performance_schema.replication_applier_status_by_worker w
  73.     ON c.channel_name = w.channel_name
  74.   LEFT JOIN
  75.   /* if parallel replication, fetch owner of most recently applied tx */
  76.     (SELECT *
  77.       FROM performance_schema.replication_applier_status_by_worker
  78.       /*!80011 ORDER BY LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP DESC */
  79.       LIMIT 1) latest_w
  80.     ON c.channel_name = latest_w.channel_name
  81.   LEFT JOIN performance_schema.threads st
  82.     ON s.thread_id = st.thread_id
  83.   LEFT JOIN performance_schema.threads cot
  84.     ON co.thread_id = cot.thread_id
  85.   LEFT JOIN performance_schema.threads wt
  86.     ON w.thread_id = wt.thread_id
  87. ORDER BY channel_name
  88. verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SHOW SLAVE HOSTS
  89. verbose: 2024-06-13T08:33:55Z: Refreshing metadata cache from 'dbu03:3306'
  90. verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SHOW DATABASES LIKE 'mysql_innodb_cluster_metadata'
  91. verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SHOW DATABASES LIKE 'mysql_innodb_cluster_metadata_bkp'
  92. verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SHOW DATABASES LIKE 'mysql_innodb_cluster_metadata'
  93. verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SHOW DATABASES LIKE 'mysql_innodb_cluster_metadata_bkp'
  94. verbose: 2024-06-13T08:33:55Z: DONE!
  95. verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SHOW DATABASES LIKE 'mysql_innodb_cluster_metadata'
  96. verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SHOW DATABASES LIKE 'mysql_innodb_cluster_metadata_bkp'
  97. verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: select count(*) FROM performance_schema.replication_group_members WHERE MEMBER_ID = @@server_uuid AND MEMBER_STATE NOT IN ('OFFLINE', 'UNREACHABLE')
  98. verbose: 2024-06-13T08:33:55Z: Instance type check: dbu03:3306: GR is installed but not active
  99. verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SELECT
  100.     c.channel_name, c.host, c.port, c.user,
  101.     s.source_uuid, s.group_name, s.last_heartbeat_timestamp,
  102.     s.service_state io_state, st.processlist_state io_thread_state,
  103.     s.last_error_number io_errno, s.last_error_message io_errmsg,
  104.     s.last_error_timestamp io_errtime,
  105.     co.service_state co_state, cot.processlist_state co_thread_state,
  106.     co.last_error_number co_errno, co.last_error_message co_errmsg,
  107.     co.last_error_timestamp co_errtime,
  108.     w.service_state w_state, wt.processlist_state w_thread_state,
  109.     w.last_error_number w_errno, w.last_error_message w_errmsg,
  110.     w.last_error_timestamp w_errtime,
  111.     /*!80011 TIMEDIFF(NOW(6),
  112.       IF(TIMEDIFF(s.LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP,
  113.           s.LAST_HEARTBEAT_TIMESTAMP) >= 0,
  114.         s.LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP,
  115.         s.LAST_HEARTBEAT_TIMESTAMP
  116.       )) as time_since_last_message,
  117.     IF(s.LAST_QUEUED_TRANSACTION='' OR s.LAST_QUEUED_TRANSACTION=latest_w.LAST_APPLIED_TRANSACTION,
  118.       'IDLE',
  119.       'APPLYING') as applier_busy_state,
  120.     IF(s.LAST_QUEUED_TRANSACTION='' OR s.LAST_QUEUED_TRANSACTION=latest_w.LAST_APPLIED_TRANSACTION,
  121.       NULL,
  122.       TIMEDIFF(latest_w.LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP,
  123.         latest_w.LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP)
  124.       ) as lag_from_original,
  125.     IF(s.LAST_QUEUED_TRANSACTION='' OR s.LAST_QUEUED_TRANSACTION=latest_w.LAST_APPLIED_TRANSACTION,
  126.       NULL,
  127.       TIMEDIFF(latest_w.LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP,
  128.         latest_w.LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP)
  129.       ) as lag_from_immediate,
  130.     */
  131.     GTID_SUBTRACT(s.RECEIVED_TRANSACTION_SET, @@global.gtid_executed)
  132.       as queued_gtid_set_to_apply
  133.   FROM performance_schema.replication_connection_configuration c
  134.   JOIN performance_schema.replication_connection_status s
  135.     ON c.channel_name = s.channel_name
  136.   LEFT JOIN performance_schema.replication_applier_status_by_coordinator co
  137.     ON c.channel_name = co.channel_name
  138.   JOIN performance_schema.replication_applier_status a
  139.     ON c.channel_name = a.channel_name
  140.   JOIN performance_schema.replication_applier_status_by_worker w
  141.     ON c.channel_name = w.channel_name
  142.   LEFT JOIN
  143.   /* if parallel replication, fetch owner of most recently applied tx */
  144.     (SELECT *
  145.       FROM performance_schema.replication_applier_status_by_worker
  146.       /*!80011 ORDER BY LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP DESC */
  147.       LIMIT 1) latest_w
  148.     ON c.channel_name = latest_w.channel_name
  149.   LEFT JOIN performance_schema.threads st
  150.     ON s.thread_id = st.thread_id
  151.   LEFT JOIN performance_schema.threads cot
  152.     ON co.thread_id = cot.thread_id
  153.   LEFT JOIN performance_schema.threads wt
  154.     ON w.thread_id = wt.thread_id
  155. ORDER BY channel_name
  156. verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SHOW SLAVE HOSTS
  157. Dba.dropMetadataSchema: This function is not available through a session to a standalone instance (MYSQLSH 51300)
  158.  MySQL  10.160.2.62:3306 ssl  JS > 
复制代码
如果上面命令没有成功，那么我们就必须连接到数据库，手工执行下面命令
  1. stop group_replication;
  2. drop schema mysql_innodb_cluster_metadata;
复制代码
然后在主节点执行下面命令，就可以重新将实例加入MySQL InnoDB Cluster。
  1. var cluster=dba.getCluster()
  2. cluster.addInstance('cdmin@10.160.2.62:3306')
  3. cluster.status()
复制代码
关于这个问题，官方文档The instance 'mysqlNode:3306' is already part of another Replication Group, How To Solve? (Doc ID 2809308.1)[1]中介绍，是因为遇到了Bug #33294010 clusterset: rejoinInstance() on broken GR member fail[2]如下截图所示，这个Bug在8.1.0中已经Fix掉了。
参考资料

[1] 1: https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=425530417327847&id=2809308.1&_afrWindowMode=0&_adf.ctrl-state=3s7yvgwzw_4
[2] 2: https://support.oracle.com/epmos/faces/BugDisplay?_afrLoop=426942735497044&id=33294010&_afrWindowMode=0&_adf.ctrl-state=3s7yvgwzw_53
扫描上面二维码关注我如果你真心觉得文章写得不错，而且对你有所帮助，那就不妨帮忙“推荐"一下，您的“推荐”和”打赏“将是我最大的写作动力！本文版权归作者所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接.
来源:https://www.cnblogs.com/kerrycode/p/18246155
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！

本帖子中包含更多资源

您需要 登录 才可以下载或查看，没有账号？立即注册

x

上一篇: 腾讯云 BI 数据分析与可视化的快速入门指南

下一篇: MySql 表数据的增、删、改、查

发表于 2024-6-14 02:28:39

举报 回复 使用道具

返回列表 发新帖

Archiver|手机版|小黑屋|翼度科技

Copyright © 2001-2020, Tencent Cloud.　Powered by Discuz! X3.4