MySQL InnoDB Cluster(测试环境为MySQL 8.0.35)将一个实例重新加入集群时，碰到了下面这个错误"The instance 'dbu03:3306' is already part of another Replication Group"- MySQL 10.160.2.55:3306 ssl JS > cluster.addInstance('cdmin@10.160.2.62:3306')<br>ERROR: RuntimeError: The instance 'dbu03:3306' is already part of another Replication Group<br>Cluster.addInstance: The instance 'dbu03:3306' is already part of another Replication Group (RuntimeError)<br>MySQL 10.160.2.55:3306 ssl JS > <br>
然后执行下面命令- shell.options.verbose=3<br>shell.options["dba.logSql"]=2<br>shell.options["logLevel"]=8<br>\sql<br>stop group_replication;<br>\js<br>dba.dropMetadataSchema();<br>
- MySQL 10.160.2.62:3306 ssl JS > shell.options.verbose=3<br>3<br> MySQL 10.160.2.62:3306 ssl JS > shell.options["dba.logSql"]=2<br>2<br> MySQL 10.160.2.62:3306 ssl JS > shell.options["logLevel"]=8<br>8<br> MySQL 10.160.2.62:3306 ssl JS > \sql<br>Switching to SQL mode... Commands end with ;<br>Fetching global names for auto-completion... Press ^C to stop.<br>stop group_replication; MySQL 10.16stop group_replication;<br>Query OK, 0 rows affected (0.0050 sec)<br> MySQL 10.160.2.62:3306 ssl SQL > \js<br>Switching to JavaScript mode...<br> MySQL 10.160.2.62:3306 ssl JS > \js<br> MySQL 10.160.2.62:3306 ssl JS > dba.dropMetadataSchema();<br>verbose: 2024-06-13T08:33:55Z: Connecting to MySQL at: mysql://cdmin@10.160.2.62:3306?connect-timeout=5000<br>verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: CONNECTED: 10.160.2.62:3306<br>verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SET SESSION `autocommit` = 1<br>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'<br>verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SET SESSION `group_replication_consistency` = 'EVENTUAL'<br>verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SET SESSION `collation_connection` = 'utf8mb4_0900_ai_ci'<br>verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SET SESSION `group_concat_max_len` = 1073741824<br>verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SELECT COALESCE(@@report_host, @@hostname), COALESCE(@@report_port, @@port)<br>verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SELECT @@server_uuid<br>verbose: 2024-06-13T08:33:55Z: Metadata operations will use dbu03:3306<br>verbose: 2024-06-13T08:33:55Z: Metadata operations will use dbu03:3306<br>verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SHOW DATABASES LIKE 'mysql_innodb_cluster_metadata'<br>verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SHOW DATABASES LIKE 'mysql_innodb_cluster_metadata_bkp'<br>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')<br>verbose: 2024-06-13T08:33:55Z: Instance type check: dbu03:3306: GR is installed but not active<br>verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SELECT<br> c.channel_name, c.host, c.port, c.user,<br> s.source_uuid, s.group_name, s.last_heartbeat_timestamp,<br> s.service_state io_state, st.processlist_state io_thread_state,<br> s.last_error_number io_errno, s.last_error_message io_errmsg,<br> s.last_error_timestamp io_errtime,<br> co.service_state co_state, cot.processlist_state co_thread_state,<br> co.last_error_number co_errno, co.last_error_message co_errmsg,<br> co.last_error_timestamp co_errtime,<br> w.service_state w_state, wt.processlist_state w_thread_state,<br> w.last_error_number w_errno, w.last_error_message w_errmsg,<br> w.last_error_timestamp w_errtime,<br> /*!80011 TIMEDIFF(NOW(6),<br> IF(TIMEDIFF(s.LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP,<br> s.LAST_HEARTBEAT_TIMESTAMP) >= 0,<br> s.LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP,<br> s.LAST_HEARTBEAT_TIMESTAMP<br> )) as time_since_last_message,<br> IF(s.LAST_QUEUED_TRANSACTION='' OR s.LAST_QUEUED_TRANSACTION=latest_w.LAST_APPLIED_TRANSACTION,<br> 'IDLE',<br> 'APPLYING') as applier_busy_state,<br> IF(s.LAST_QUEUED_TRANSACTION='' OR s.LAST_QUEUED_TRANSACTION=latest_w.LAST_APPLIED_TRANSACTION,<br> NULL,<br> TIMEDIFF(latest_w.LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP,<br> latest_w.LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP)<br> ) as lag_from_original,<br> IF(s.LAST_QUEUED_TRANSACTION='' OR s.LAST_QUEUED_TRANSACTION=latest_w.LAST_APPLIED_TRANSACTION,<br> NULL,<br> TIMEDIFF(latest_w.LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP,<br> latest_w.LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP)<br> ) as lag_from_immediate,<br> */<br> GTID_SUBTRACT(s.RECEIVED_TRANSACTION_SET, @@global.gtid_executed)<br> as queued_gtid_set_to_apply<br> FROM performance_schema.replication_connection_configuration c<br> JOIN performance_schema.replication_connection_status s<br> ON c.channel_name = s.channel_name<br> LEFT JOIN performance_schema.replication_applier_status_by_coordinator co<br> ON c.channel_name = co.channel_name<br> JOIN performance_schema.replication_applier_status a<br> ON c.channel_name = a.channel_name<br> JOIN performance_schema.replication_applier_status_by_worker w<br> ON c.channel_name = w.channel_name<br> LEFT JOIN<br> /* if parallel replication, fetch owner of most recently applied tx */<br> (SELECT *<br> FROM performance_schema.replication_applier_status_by_worker<br> /*!80011 ORDER BY LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP DESC */<br> LIMIT 1) latest_w<br> ON c.channel_name = latest_w.channel_name<br> LEFT JOIN performance_schema.threads st<br> ON s.thread_id = st.thread_id<br> LEFT JOIN performance_schema.threads cot<br> ON co.thread_id = cot.thread_id<br> LEFT JOIN performance_schema.threads wt<br> ON w.thread_id = wt.thread_id<br>ORDER BY channel_name<br>verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SHOW SLAVE HOSTS<br>verbose: 2024-06-13T08:33:55Z: Refreshing metadata cache from 'dbu03:3306'<br>verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SHOW DATABASES LIKE 'mysql_innodb_cluster_metadata'<br>verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SHOW DATABASES LIKE 'mysql_innodb_cluster_metadata_bkp'<br>verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SHOW DATABASES LIKE 'mysql_innodb_cluster_metadata'<br>verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SHOW DATABASES LIKE 'mysql_innodb_cluster_metadata_bkp'<br>verbose: 2024-06-13T08:33:55Z: DONE!<br>verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SHOW DATABASES LIKE 'mysql_innodb_cluster_metadata'<br>verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SHOW DATABASES LIKE 'mysql_innodb_cluster_metadata_bkp'<br>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')<br>verbose: 2024-06-13T08:33:55Z: Instance type check: dbu03:3306: GR is installed but not active<br>verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SELECT<br> c.channel_name, c.host, c.port, c.user,<br> s.source_uuid, s.group_name, s.last_heartbeat_timestamp,<br> s.service_state io_state, st.processlist_state io_thread_state,<br> s.last_error_number io_errno, s.last_error_message io_errmsg,<br> s.last_error_timestamp io_errtime,<br> co.service_state co_state, cot.processlist_state co_thread_state,<br> co.last_error_number co_errno, co.last_error_message co_errmsg,<br> co.last_error_timestamp co_errtime,<br> w.service_state w_state, wt.processlist_state w_thread_state,<br> w.last_error_number w_errno, w.last_error_message w_errmsg,<br> w.last_error_timestamp w_errtime,<br> /*!80011 TIMEDIFF(NOW(6),<br> IF(TIMEDIFF(s.LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP,<br> s.LAST_HEARTBEAT_TIMESTAMP) >= 0,<br> s.LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP,<br> s.LAST_HEARTBEAT_TIMESTAMP<br> )) as time_since_last_message,<br> IF(s.LAST_QUEUED_TRANSACTION='' OR s.LAST_QUEUED_TRANSACTION=latest_w.LAST_APPLIED_TRANSACTION,<br> 'IDLE',<br> 'APPLYING') as applier_busy_state,<br> IF(s.LAST_QUEUED_TRANSACTION='' OR s.LAST_QUEUED_TRANSACTION=latest_w.LAST_APPLIED_TRANSACTION,<br> NULL,<br> TIMEDIFF(latest_w.LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP,<br> latest_w.LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP)<br> ) as lag_from_original,<br> IF(s.LAST_QUEUED_TRANSACTION='' OR s.LAST_QUEUED_TRANSACTION=latest_w.LAST_APPLIED_TRANSACTION,<br> NULL,<br> TIMEDIFF(latest_w.LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP,<br> latest_w.LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP)<br> ) as lag_from_immediate,<br> */<br> GTID_SUBTRACT(s.RECEIVED_TRANSACTION_SET, @@global.gtid_executed)<br> as queued_gtid_set_to_apply<br> FROM performance_schema.replication_connection_configuration c<br> JOIN performance_schema.replication_connection_status s<br> ON c.channel_name = s.channel_name<br> LEFT JOIN performance_schema.replication_applier_status_by_coordinator co<br> ON c.channel_name = co.channel_name<br> JOIN performance_schema.replication_applier_status a<br> ON c.channel_name = a.channel_name<br> JOIN performance_schema.replication_applier_status_by_worker w<br> ON c.channel_name = w.channel_name<br> LEFT JOIN<br> /* if parallel replication, fetch owner of most recently applied tx */<br> (SELECT *<br> FROM performance_schema.replication_applier_status_by_worker<br> /*!80011 ORDER BY LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP DESC */<br> LIMIT 1) latest_w<br> ON c.channel_name = latest_w.channel_name<br> LEFT JOIN performance_schema.threads st<br> ON s.thread_id = st.thread_id<br> LEFT JOIN performance_schema.threads cot<br> ON co.thread_id = cot.thread_id<br> LEFT JOIN performance_schema.threads wt<br> ON w.thread_id = wt.thread_id<br>ORDER BY channel_name<br>verbose: 2024-06-13T08:33:55Z: Dba.dropMetadataSchema: tid=530: SQL: SHOW SLAVE HOSTS<br>Dba.dropMetadataSchema: This function is not available through a session to a standalone instance (MYSQLSH 51300)<br> MySQL 10.160.2.62:3306 ssl JS > <br>
- stop group_replication;<br>drop schema mysql_innodb_cluster_metadata;<br>
- var cluster=dba.getCluster()<br>cluster.addInstance('cdmin@10.160.2.62:3306')<br>cluster.status()<br>
参考资料
[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
扫描上面二维码关注我如果你真心以为文章写得不错，而且对你有所帮助，那就不妨帮忙“推荐"一下，您的“推荐”和”打赏“将是我最大的写作动力！本文版权归作者所有，接待转载，但未经作者同意必须保留此段声明，且在文章页面显着位置给出原文连接.
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。 |
