MySQL HA的全新篇章：Semisynchronous Replication迁移至InnoDB Cluster的实用指南_checking whether existing tables comply with group-优快云博客

1、概述

临时接了一个搭建InnoDB Cluster的活儿，客户给我说是有数据的，我当时想这不是非常简单吗？干活儿的时候，才发现并没有这么简单，接手的时候发现是Semisynchronous Replication的环境，然后把从库切换成InnoDB Cluster的primary。

2、环境复现

2.1、从库5.140信息采集

mysql> show replica status\G
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 192.168.5.130
                  Source_User: repuser
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: mydb-binlog.000001
          Read_Source_Log_Pos: 342
               Relay_Log_File: mydb-relay.000002
                Relay_Log_Pos: 562
        Relay_Source_Log_File: mydb-binlog.000001
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
...
1 row in set (0.00 sec)


mysql> select PLUGIN_NAME,PLUGIN_STATUS from information_schema.plugins where plugin_name like '%semi%';
+----------------------+---------------+
| PLUGIN_NAME          | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
| rpl_semi_sync_slave  | ACTIVE        |
+----------------------+---------------+
2 rows in set (0.00 sec)

mysql>
mysql>
mysql> show variables like '%semi_sync%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 5000       |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
| rpl_semi_sync_slave_enabled               | ON         |
| rpl_semi_sync_slave_trace_level           | 32         |
+-------------------------------------------+------------+
8 rows in set (0.00 sec)

mysql>
mysql>
mysql> show plugins;
+----------------------------------+----------+--------------------+--------------------+---------+
| Name                             | Status   | Type               | Library            | License |
+----------------------------------+----------+--------------------+--------------------+---------+
...
| rpl_semi_sync_master             | ACTIVE   | REPLICATION        | semisync_master.so | GPL     |
| rpl_semi_sync_slave              | ACTIVE   | REPLICATION        | semisync_slave.so  | GPL     |
+----------------------------------+----------+--------------------+--------------------+---------+
50 rows in set (0.00 sec)

mysql> select PLUGIN_NAME,PLUGIN_STATUS from information_schema.plugins where plugin_name like '%semi%';
+----------------------+---------------+
| PLUGIN_NAME          | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
| rpl_semi_sync_slave  | ACTIVE        |
+----------------------+---------------+
2 rows in set (0.00 sec)

2.2、从库5.140 InnoDB Cluster准备工作

mysql> stop replica;
Query OK, 0 rows affected (0.00 sec)


mysql> show replica status\G
*************************** 1. row ***************************
             Replica_IO_State:
                  Source_Host: 192.168.5.130
                  Source_User: repuser
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: mydb-binlog.000001
          Read_Source_Log_Pos: 342
               Relay_Log_File: mydb-relay.000002
                Relay_Log_Pos: 562
        Relay_Source_Log_File: mydb-binlog.000001
           Replica_IO_Running: No
          Replica_SQL_Running: No
      
1 row in set (0.00 sec)

mysql>

mysql> show variables like '%read_only%';
+-----------------------------------------+---------------------------+
| Variable_name                           | Value                     |
+-----------------------------------------+---------------------------+
| read_only                               | ON                        |
| super_read_only                         | OFF                       |
+-----------------------------------------+---------------------------+
2 rows in set (0.00 sec)

mysql> \q
Bye

2.3、从库5.140参数调整

[root@mydb02 ~]# vi /mysql/data/my.cnf

这里只调整了read_only

2.4、踩坑–从库5.140搭建InnoDB Cluster

2.4.1、InnoDB Cluster配置与检查

[root@mydb02 ~]# mysqlsh
MySQL Shell 8.3.0

Copyright (c) 2016, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
Creating a Classic session to 'root@localhost'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 21
Server version: 8.0.36 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  localhost  JS >
 MySQL  localhost  JS >
 MySQL  localhost  JS >
 MySQL  localhost  JS > \sql show databases;
Fetching global names for auto-completion... Press ^C to stop.
+--------------------+
| Database           |
+--------------------+
| db01               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.0005 sec)
 MySQL  localhost  JS > dba.checkInstanceConfiguration('root@192.168.5.140')
Please provide the password for 'root@192.168.5.140': ****
Save password for 'root@192.168.5.140'? [Y]es/[N]o/Ne[v]er (default No): Y
Validating local MySQL instance listening at port 3306 for use in an InnoDB Cluster...

This instance reports its own address as mydb02:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.
NOTE: Found unexpected replication channel '' at mydb02:3306
WARNING: The instance 'mydb02:3306' cannot be added to an InnoDB cluster because it has asynchronous (source-replica) replication channel(s) configured. MySQL InnoDB Cluster does not support manually configured channels as they are not managed using the AdminAPI (e.g. when PRIMARY moves to another member) which may cause cause replication to break or even create split-brain scenarios (data loss).

Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected

Checking instance configuration...

NOTE: Some configuration options need to be fixed:
+----------------------------------------+---------------+----------------+----------------------------+
| Variable                               | Current Value | Required Value | Note                       |
+----------------------------------------+---------------+----------------+----------------------------+
| binlog_transaction_dependency_tracking | COMMIT_ORDER  | WRITESET       | Update the server variable |
+----------------------------------------+---------------+----------------+----------------------------+

NOTE: Please use the dba.configureInstance() command to repair these issues.

{
    "config_errors": [
        {
            "action": "server_update",
            "current": "COMMIT_ORDER",
            "option": "binlog_transaction_dependency_tracking",
            "required": "WRITESET"
        }
    ],
    "status": "error"
}

这里出现了WARNING，原因也讲了

WARNING: The instance 'mydb02:3306' cannot be added to an InnoDB cluster because it has asynchronous (source-replica) replication channel(s) configured. MySQL InnoDB Cluster does not support manually configured channels as they are not managed using the AdminAPI (e.g. when PRIMARY moves to another member) which may cause cause replication to break or even create split-brain scenarios (data loss).

尝试配置这个实例

 MySQL  localhost  JS >
 MySQL  localhost  JS >
 MySQL  localhost  JS > dba.configureInstance('root@192.168.5.140')
Dba.configureInstance: This function is not available through a session to an instance belonging to an unmanaged asynchronous replication topology (RuntimeError)

提示IC的命令不能管理asynchronous replication

2.4.2、插件处理

首先怀疑是插件的问题，然后卸载插件

MySQL  localhost  JS > \sql select PLUGIN_NAME,PLUGIN_STATUS from information_schema.plugins where plugin_name like '%semi%';
+----------------------+---------------+
| PLUGIN_NAME          | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
| rpl_semi_sync_slave  | ACTIVE        |
+----------------------+---------------+
2 rows in set (0.0010 sec)

 MySQL  localhost  JS > \sql uninstall plugin rpl_semi_sync_master;
Query OK, 0 rows affected (0.0026 sec)
 MySQL  localhost  JS > \sql uninstall plugin rpl_semi_sync_slave;
Query OK, 0 rows affected (0.0011 sec)

最后发现这个插件的卸载是非必须的

2.4.3、踩坑–再次尝试配置与检查

 MySQL  localhost  JS > dba.checkInstanceConfiguration('root@192.168.5.140')
Validating local MySQL instance listening at port 3306 for use in an InnoDB Cluster...

This instance reports its own address as mydb02:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.
NOTE: Found unexpected replication channel '' at mydb02:3306
WARNING: The instance 'mydb02:3306' cannot be added to an InnoDB cluster because it has asynchronous (source-replica) replication channel(s) configured. MySQL InnoDB Cluster does not support manually configured channels as they are not managed using the AdminAPI (e.g. when PRIMARY moves to another member) which may cause cause replication to break or even create split-brain scenarios (data loss).

Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected

Checking instance configuration...

NOTE: Some configuration options need to be fixed:
+----------------------------------------+---------------+----------------+----------------------------+
| Variable                               | Current Value | Required Value | Note                       |
+----------------------------------------+---------------+----------------+----------------------------+
| binlog_transaction_dependency_tracking | COMMIT_ORDER  | WRITESET       | Update the server variable |
+----------------------------------------+---------------+----------------+----------------------------+

NOTE: Please use the dba.configureInstance() command to repair these issues.

{
    "config_errors": [
        {
            "action": "server_update",
            "current": "COMMIT_ORDER",
            "option": "binlog_transaction_dependency_tracking",
            "required": "WRITESET"
        }
    ],
    "status": "error"
}

再次失败，得到了相同的信息

2.4.4、处理replica信息

因为是Semisynchronous Replication，所以前面处理了plugin，没有成功，所以排除法想到处理replica。

 MySQL  localhost  JS > \sql reset replica;
Query OK, 0 rows affected (0.0106 sec)

2.4.5、再次踩坑-再次检查

MySQL  localhost  JS > dba.checkInstanceConfiguration('root@192.168.5.140')
Validating local MySQL instance listening at port 3306 for use in an InnoDB Cluster...

This instance reports its own address as mydb02:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.
NOTE: Found unexpected replication channel '' at mydb02:3306
WARNING: The instance 'mydb02:3306' cannot be added to an InnoDB cluster because it has asynchronous (source-replica) replication channel(s) configured. MySQL InnoDB Cluster does not support manually configured channels as they are not managed using the AdminAPI (e.g. when PRIMARY moves to another member) which may cause cause replication to break or even create split-brain scenarios (data loss).

Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected

Checking instance configuration...

NOTE: Some configuration options need to be fixed:
+----------------------------------------+---------------+----------------+----------------------------+
| Variable                               | Current Value | Required Value | Note                       |
+----------------------------------------+---------------+----------------+----------------------------+
| binlog_transaction_dependency_tracking | COMMIT_ORDER  | WRITESET       | Update the server variable |
+----------------------------------------+---------------+----------------+----------------------------+

NOTE: Please use the dba.configureInstance() command to repair these issues.

{
    "config_errors": [
        {
            "action": "server_update",
            "current": "COMMIT_ORDER",
            "option": "binlog_transaction_dependency_tracking",
            "required": "WRITESET"
        }
    ],
    "status": "error"
}

2.4.6、再次处理replica

上一步中忘记去核对replica的信息了，这里检查下，发现replica的信息依然存在

 MySQL  localhost  JS > \sql show replica status\G
*************************** 1. row ***************************
             Replica_IO_State:
                  Source_Host: 192.168.5.130
                  Source_User: repuser
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File:
          Read_Source_Log_Pos: 4
               Relay_Log_File: mydb-relay.000001
                Relay_Log_Pos: 4
        Relay_Source_Log_File:
           Replica_IO_Running: No
          Replica_SQL_Running: No
...
1 row in set (0.0004 sec)

再次处理，reset all

 MySQL  localhost  JS > \sql reset replica all;
Query OK, 0 rows affected (0.0021 sec)

replica info 检查

 MySQL  localhost  JS > \sql show replica status\G
Empty set (0.0004 sec)

InnoDB Cluster Check检查

MySQL  localhost  JS > dba.checkInstanceConfiguration('root@192.168.5.140')
Validating local MySQL instance listening at port 3306 for use in an InnoDB Cluster...

This instance reports its own address as mydb02:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected

Checking instance configuration...

NOTE: Some configuration options need to be fixed:
+----------------------------------------+---------------+----------------+----------------------------+
| Variable                               | Current Value | Required Value | Note                       |
+----------------------------------------+---------------+----------------+----------------------------+
| binlog_transaction_dependency_tracking | COMMIT_ORDER  | WRITESET       | Update the server variable |
+----------------------------------------+---------------+----------------+----------------------------+

NOTE: Please use the dba.configureInstance() command to repair these issues.

{
    "config_errors": [
        {
            "action": "server_update",
            "current": "COMMIT_ORDER",
            "option": "binlog_transaction_dependency_tracking",
            "required": "WRITESET"
        }
    ],
    "status": "error"
}

ok，得到了想要的结果，虽然status是error，但是检查的结果告诉我们是一些参数问题。

2.4.7、最简单的IC搭建方法

自动配置实例，创建集群，使用API就可以完成，不赘述了

MySQL  localhost  JS > dba.configureInstance('root@192.168.5.140')
Configuring local MySQL instance listening at port 3306 for use in an InnoDB Cluster...

This instance reports its own address as mydb02:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

applierWorkerThreads will be set to the default value of 4.

NOTE: Some configuration options need to be fixed:
+----------------------------------------+---------------+----------------+----------------------------+
| Variable                               | Current Value | Required Value | Note                       |
+----------------------------------------+---------------+----------------+----------------------------+
| binlog_transaction_dependency_tracking | COMMIT_ORDER  | WRITESET       | Update the server variable |
+----------------------------------------+---------------+----------------+----------------------------+

Do you want to perform the required configuration changes? [y/n]: y
Configuring instance...

WARNING: '@@binlog_transaction_dependency_tracking' is deprecated and will be removed in a future release. (Code 1287).
The instance 'mydb02:3306' was configured to be used in an InnoDB Cluster.

创建集群

 MySQL  localhost  JS > var mycluster=dba.createCluster('demoCluster')
A new InnoDB Cluster will be created on instance 'mydb02:3306'.

Validating instance configuration at /tmp%2Fmysql.sock...

This instance reports its own address as mydb02:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'mydb02:3306'. Use the localAddress option to override.

* Checking connectivity and SSL configuration...

Creating InnoDB Cluster 'demoCluster' on 'mydb02:3306'...

Adding Seed Instance...
Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.

2.4.8、添加实例

这里添加的是Semisynchronous Replication中的主，以验证插件不会影响IC的搭建

 MySQL  localhost  JS > mycluster.addInstance('root@192.168.5.130')
ERROR: Unable to connect to the target instance '192.168.5.130'. Please verify the connection settings, make sure the instance is available and try again.
Cluster.addInstance: Could not open connection to '192.168.5.130': Can't connect to MySQL server on '192.168.5.130:3306' (111) (MySQL Error 2003)
 MySQL  localhost  JS > mycluster.addInstance('root@192.168.5.130')
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'mydb01:3306' with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.

The incremental state recovery may be safely used if you are sure all updates ever executed in the cluster were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the cluster or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.

Incremental state recovery was selected because it seems to be safely usable.

Validating instance configuration at 192.168.5.130:3306...

This instance reports its own address as mydb01:3306

NOTE: Some configuration options need to be fixed:
+----------------------------------------+---------------+----------------+----------------------------+
| Variable                               | Current Value | Required Value | Note                       |
+----------------------------------------+---------------+----------------+----------------------------+
| binlog_transaction_dependency_tracking | COMMIT_ORDER  | WRITESET       | Update the server variable |
+----------------------------------------+---------------+----------------+----------------------------+

NOTE: Please use the dba.configureInstance() command to repair these issues.

ERROR: Instance must be configured and validated with dba.checkInstanceConfiguration() and dba.configureInstance() before it can be used in an InnoDB cluster.
Cluster.addInstance: Instance check failed (RuntimeError)
 MySQL  localhost  JS > dba.checkInstanceConfiguration('root@192.168.5.130')
Please provide the password for 'root@192.168.5.130': ****
Save password for 'root@192.168.5.130'? [Y]es/[N]o/Ne[v]er (default No): Y
Validating MySQL instance at mydb01:3306 for use in an InnoDB Cluster...

This instance reports its own address as mydb01:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected

Checking instance configuration...

NOTE: Some configuration options need to be fixed:
+----------------------------------------+---------------+----------------+----------------------------+
| Variable                               | Current Value | Required Value | Note                       |
+----------------------------------------+---------------+----------------+----------------------------+
| binlog_transaction_dependency_tracking | COMMIT_ORDER  | WRITESET       | Update the server variable |
+----------------------------------------+---------------+----------------+----------------------------+

NOTE: Please use the dba.configureInstance() command to repair these issues.

{
    "config_errors": [
        {
            "action": "server_update",
            "current": "COMMIT_ORDER",
            "option": "binlog_transaction_dependency_tracking",
            "required": "WRITESET"
        }
    ],
    "status": "error"
}
 MySQL  localhost  JS > dba.configureInstance()('root@192.168.5.130')
The instance 'mydb02:3306' belongs to an InnoDB Cluster.
Configuring local MySQL instance listening at port 3306 for use in an InnoDB Cluster...

This instance reports its own address as mydb02:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

applierWorkerThreads will be set to the default value of 4.

The instance 'mydb02:3306' is valid for InnoDB Cluster usage.
WARNING: The changes on the value of replica_parallel_workers will only take place after the instance leaves and rejoins the Cluster.

Successfully set the value of replica_parallel_workers.
TypeError: dba.configureInstance(...) is not a function
 MySQL  localhost  JS > dba.configureInstance('root@192.168.5.130')
Configuring MySQL instance at mydb01:3306 for use in an InnoDB Cluster...

This instance reports its own address as mydb01:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

applierWorkerThreads will be set to the default value of 4.

NOTE: Some configuration options need to be fixed:
+----------------------------------------+---------------+----------------+----------------------------+
| Variable                               | Current Value | Required Value | Note                       |
+----------------------------------------+---------------+----------------+----------------------------+
| binlog_transaction_dependency_tracking | COMMIT_ORDER  | WRITESET       | Update the server variable |
+----------------------------------------+---------------+----------------+----------------------------+

Do you want to perform the required configuration changes? [y/n]: y
Configuring instance...

WARNING: '@@binlog_transaction_dependency_tracking' is deprecated and will be removed in a future release. (Code 1287).
The instance 'mydb01:3306' was configured to be used in an InnoDB Cluster.
 MySQL  localhost  JS > mycluster.addInstance('root@192.168.5.130')
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'mydb01:3306' with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.

The incremental state recovery may be safely used if you are sure all updates ever executed in the cluster were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the cluster or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.

Incremental state recovery was selected because it seems to be safely usable.

Validating instance configuration at 192.168.5.130:3306...

This instance reports its own address as mydb01:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'mydb01:3306'. Use the localAddress option to override.

* Checking connectivity and SSL configuration...

A new instance will be added to the InnoDB Cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster...

Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
Incremental state recovery is now in progress.

* Waiting for distributed recovery to finish...
  NOTE: 'mydb01:3306' is being recovered from 'mydb02:3306'
* Distributed recovery has finished

The instance 'mydb01:3306' was successfully added to the cluster.

2.4.9、集群状态检查

 MySQL  localhost  JS > mycluster.status()
{
    "clusterName": "demoCluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "mydb02:3306",
        "ssl": "REQUIRED",
        "status": "OK_NO_TOLERANCE",
        "statusText": "Cluster is NOT tolerant to any failures.",
        "topology": {
            "mydb01:3306": {
                "address": "mydb01:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.36"
            },
            "mydb02:3306": {
                "address": "mydb02:3306",
                "memberRole": "PRIMARY",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.36"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "mydb02:3306"
}
 MySQL  localhost  JS >

至此，完成Semisynchronous Replication到InnoDB Cluster的切换。

3、总结

从操作过程查看时reset replica和reset replica all的区别。

那么这两个参数有什么区别呢？直接上官网给答案。

RESET REPLICA does not change any replication connection parameters, which include the source’s host name and port, the replication user account and its password,

the PRIVILEGE_CHECKS_USER account, the REQUIRE_ROW_FORMAT option, the REQUIRE_TABLE_PRIMARY_KEY_CHECK option,and the ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS option. If you want to change any of the replication connection parameters, you can do this using a CHANGE REPLICATION SOURCE TO statement (from MySQL 8.0.23) or CHANGE MASTER TO statement (before MySQL 8.0.23) after the server start.

If you want to remove all of the replication connection parameters, use RESET REPLICA ALL.

RESET REPLICA ALL also clears the IGNORE_SERVER_IDS list set by CHANGE REPLICATION SOURCE TO | CHANGE MASTER TO. When you have used RESET REPLICA ALL, if you want to use the instance as a replica again, you need to issue a CHANGE REPLICATION SOURCE TO | CHANGE MASTER TO statement after the server start to specify new connection parameters.

划分了一下重点，其实就是replication connection parameters的处理。

完成，收钱！