• 欢迎访问DBA的辛酸事儿，推荐使用最新版火狐浏览器和Chrome浏览器访问本网站
  • 欢迎大家关注博主公众号：DBA的辛酸事儿
  • 博文中若有错误的地方，请大家指正，大家的指正是我前进的动力
> 关系型数据库 > MySQL > MySQL 8.0新特性：InnoDB ReplicaSet

MySQL 8.0新特性：InnoDB ReplicaSet

MySQL SEian.G 3年前 (2021-03-23) 2675次浏览 已收录 0个评论
文章目录[隐藏]

一、InnoDB ReplicaSet 介绍

MySQL InnoDB ReplicaSet在 MySQL 8.0.19 版本之后开始支持，本质还是是基于 GTID 的异步复制；并且MySQL Shell的AdminAPI新增了 对InnoDB ReplicaSet的支持，可以用与InnoDB Cluster类似的方式管理一组运行基于GTID的异步复制的MySQL实例。InnoDB ReplicaSet由一个主和多个从组成。您可以使用ReplicaSet对象和AdminAPI操作，例如检查InnoDB ReplicaSet的状态，并在发生故障时手动故障转移到新的主数据库。与InnoDB Cluster类似，MySQL Router支持针对InnoDB ReplicaSet的引导，这意味着可以自动配置MySQL Router以使用InnoDB ReplicaSet，而无需手动配置它。这使InnoDB ReplicaSet成为启动和运行MySQL复制和MySQL Router的快速简便方法，使其非常适合横向扩展读取。

二、部署mysql 8.0.23实例

关于MySQL实例的部署这里不做太多介绍，这里我们部署三个测试实例，端口分别是8401、8402、8403

三、部署InnoDB ReplicaSet

仅支持要求运行MySQL 8.0及更高版本的实例

仅支持基于GTID的复制，二进制日志文件位置复制与InnoDB ReplicaSet不兼容

仅支持基于行的复制（RBR），不支持基于语句的复制（SBR）

不支持复制过滤器

在任何实例下都不允许Unmanaged replication channels

一个ReplicaSet最多包含一个主实例，并且支持一个或多个第二实例。尽管可以添加到副本集的辅助副本数量没有限制，但是连接到副本集的每个MySQL Router都会监视每个实例。因此，添加到ReplicaSet的实例越多，必须执行的监视越多。

ReplicaSet必须完全由MySQL Shell管理。例如，复制帐户由MySQL Shell创建和管理。不支持在MySQL Shell之外对实例进行配置更改，例如，直接使用SQL语句更改主实例。应该始终将MySQL Shell与InnoDB ReplicaSet一起使用。

四、配置InnoDB ReplicaSet

1、进入MySQL Shell

[root@hb30-dbs-mysql-124-186 bin]# ./mysqlsh 
MySQL Shell 8.0.23

Copyright (c) 2016, 2021, 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.
 MySQL  JS >

2、创建rsadmin的集群管理员

MySQL  JS > dba.configureReplicaSetInstance('dba_admin@127.0.0.1:8401', {clusterAdmin: "'rsadmin'@'%'"});
Please provide the password for 'dba_admin@127.0.0.1:8401': ****************
Save password for 'dba_admin@127.0.0.1:8401'? [Y]es/[N]o/Ne[v]er (default No): y
Configuring local MySQL instance listening at port 8401 for use in an InnoDB ReplicaSet...
ERROR: The account 'dba_admin'@'127.0.0.1' is missing privileges required to manage an InnoDB cluster:
GRANT BACKUP_ADMIN, CLONE_ADMIN, PERSIST_RO_VARIABLES_ADMIN, REPLICATION_APPLIER, SYSTEM_VARIABLES_ADMIN ON *.* TO 'dba_admin'@'127.0.0.1' WITH GRANT OPTION;
GRANT CREATE TEMPORARY TABLES, REFERENCES ON mysql_innodb_cluster_metadata.* TO 'dba_admin'@'127.0.0.1' WITH GRANT OPTION;
GRANT CREATE TEMPORARY TABLES, REFERENCES ON mysql_innodb_cluster_metadata_bkp.* TO 'dba_admin'@'127.0.0.1' WITH GRANT OPTION;
GRANT CREATE TEMPORARY TABLES, REFERENCES ON mysql_innodb_cluster_metadata_previous.* TO 'dba_admin'@'127.0.0.1' WITH GRANT OPTION;
For more information, see the online documentation.
Dba.configureReplicaSetInstance: The account 'dba_admin'@'127.0.0.1' is missing privileges required to manage an InnoDB cluster. (RuntimeError)
 MySQL  JS >

注意：

1、dba.configureReplicaSetInstance()函数可以创建管理员帐户。使用正确的权限来创建帐户来管理InnoDB集群和InnoDB副本集。

2、在同一集群或副本集的所有实例中，管理员帐户必须具有相同的用户名和密码。

3、实例的账户必须要有BACKUP_ADMIN, CLONE_ADMIN, PERSIST_RO_VARIABLES_ADMIN, REPLICATION_APPLIER, SYSTEM_VARIABLES_ADMIN、CREATE TEMPORARY TABLES, REFERENCES账户的权限，否则就会出现上述的报错提示

 MySQL  JS > dba.configureReplicaSetInstance('dba_admin@127.0.0.1:8401', {clusterAdmin: "'rsadmin'@'%'"});
Configuring local MySQL instance listening at port 8401 for use in an InnoDB ReplicaSet...

This instance reports its own address as hb30-dbs-mysql-124-186:8401
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.
Password for new account: *******
Confirm password: *******

applierWorkerThreads will be set to the default value of 4.

NOTE: Some configuration options need to be fixed:
+-----------------------------+---------------+----------------+----------------------------+
| Variable                    | Current Value | Required Value | Note                       |
+-----------------------------+---------------+----------------+----------------------------+
| slave_preserve_commit_order | OFF           | ON             | Update the server variable |
+-----------------------------+---------------+----------------+----------------------------+

Do you want to perform the required configuration changes? [y/n]: y
Cluster admin user 'rsadmin'@'%' created.
Configuring instance...
The instance 'hb30-dbs-mysql-124-186:8401' was configured to be used in an InnoDB ReplicaSet.

3、创建Innodb ReplicaSet

MySQL  JS > \connect dba_admin@127.0.0.1:8401
Creating a session to 'dba_admin@127.0.0.1:8401'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 20
Server version: 8.0.23 MySQL Community Server - GPL
No default schema selected; type \use  to set one.
 MySQL  127.0.0.1:8401 ssl  JS > 
 MySQL  127.0.0.1:8401 ssl  JS > var rs = dba.createReplicaSet("innodb_replicaset_example")
A new replicaset with instance 'hb30-dbs-mysql-124-186:8401' will be created.

* Checking MySQL instance at hb30-dbs-mysql-124-186:8401

This instance reports its own address as hb30-dbs-mysql-124-186:8401
hb30-dbs-mysql-124-186:8401: Instance configuration is suitable.

* Updating metadata...

ReplicaSet object successfully created for hb30-dbs-mysql-124-186:8401.
Use rs.addInstance() to add more asynchronously replicated instances to this replicaset and rs.status() to check its status.

4、查看ReplicaSet状态

MySQL  127.0.0.1:8401 ssl  JS > rs.status()
{
    "replicaSet": {
        "name": "innodb_replicaset_example", 
        "primary": "hb30-dbs-mysql-124-186:8401", 
        "status": "AVAILABLE", 
        "statusText": "All instances available.", 
        "topology": {
            "hb30-dbs-mysql-124-186:8401": {
                "address": "hb30-dbs-mysql-124-186:8401", 
                "instanceRole": "PRIMARY", 
                "mode": "R/W", 
                "status": "ONLINE"
            }
        }, 
        "type": "ASYNC"
    }
}

5、添加10.30.124.186:8402实例进入集群

MySQL  127.0.0.1:8401 ssl  JS > rs.addInstance('10.30.124.186:8402')
ERROR: The administrative account credentials for 10.30.124.186:8402 do not match the cluster's administrative account. The cluster administrative account user name and password must be the same on all instances that belong to it.
ReplicaSet.addInstance: Could not open connection to '10.30.124.186:8402': Access denied for user 'dba_admin'@'10.30.124.186' (using password: YES) (MySQL Error 1045)
 MySQL  127.0.0.1:8401 ssl  JS > 
 MySQL  127.0.0.1:8401 ssl  JS > rs.addInstance('127.0.0.1:8402')
Adding instance to the replicaset...

* Performing validation checks

This instance reports its own address as hb30-dbs-mysql-124-186:8402

NOTE: Some configuration options need to be fixed:
+-----------------------------+---------------+----------------+----------------------------+
| Variable                    | Current Value | Required Value | Note                       |
+-----------------------------+---------------+----------------+----------------------------+
| slave_preserve_commit_order | OFF           | ON             | Update the server variable |
+-----------------------------+---------------+----------------+----------------------------+

ERROR: hb30-dbs-mysql-124-186:8402: Instance must be configured and validated with dba.configureReplicaSetInstance() before it can be used in a replicaset.
ReplicaSet.addInstance: Instance check failed (MYSQLSH 51150)

出现上述的报错信息，是由于8402端口的实例参数slave_preserve_commit_order设置的存在问题导致的

需要登录到8402端口的实例上将参数slave_preserve_commit_order设置为ON

mysql> show variables like '%slave_preserve_commit_order%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| slave_preserve_commit_order | OFF   |
+-----------------------------+-------+
1 row in set (0.01 sec)

mysql> set global slave_preserve_commit_order=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> show variables like '%slave_preserve_commit_order%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| slave_preserve_commit_order | ON    |
+-----------------------------+-------+
1 row in set (0.00 sec)

重新添加节点到复制集中

MySQL  127.0.0.1:8401 ssl  JS > rs.addInstance('127.0.0.1:8402')
Adding instance to the replicaset...

* Performing validation checks

This instance reports its own address as hb30-dbs-mysql-124-186:8402
hb30-dbs-mysql-124-186:8402: Instance configuration is suitable.

* Checking async replication topology...

* Checking transaction state of the instance...

WARNING: A GTID set check of the MySQL instance at 'hb30-dbs-mysql-124-186:8402' determined that it contains transactions that do not originate from the replicaset, which must be discarded before it can join the replicaset.

hb30-dbs-mysql-124-186:8402 has the following errant GTIDs that do not exist in the replicaset:
d65bca29-73f4-11eb-b637-005056b7656c:1-38

WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of hb30-dbs-mysql-124-186:8402 with a physical snapshot from an existing replicaset member. To use this method by default, set the 'recoveryMethod' option to 'clone'.

Having extra GTID events is not expected, and it is recommended to investigate this further and ensure that the data can be removed prior to choosing the clone recovery method.

Please select a recovery method [C]lone/[A]bort (default Abort): c
* Updating topology
Waiting for clone process of the new member to complete. Press ^C to abort the operation.
* Waiting for clone to finish...
NOTE: hb30-dbs-mysql-124-186:8402 is being cloned from hb30-dbs-mysql-124-186:8401
** Stage DROP DATA: Completed
** Clone Transfer  
    FILE COPY  ############################################################  100%  Completed
    PAGE COPY  ############################################################  100%  Completed
    REDO COPY  ############################################################  100%  Completed

NOTE: hb30-dbs-mysql-124-186:8402 is shutting down...

* Waiting for server restart... /
* Waiting for server restart... timeout
WARNING: Clone process appears to have finished and tried to restart the MySQL server, but it has not yet started back up.

Please make sure the MySQL server at 'hb30-dbs-mysql-124-186:8402' is properly restarted. The operation will be reverted, but you may retry adding the instance after restarting it. 
ERROR: Error adding instance to replicaset: MYSQLSH 51156: Timeout waiting for server to restart
Reverting topology changes...
ERROR: Error while reverting replication changes: MySQL Error 2013 (HY000): Lost connection to MySQL server during query

Changes successfully reverted.
ERROR: hb30-dbs-mysql-124-186:8402 could not be added to the replicaset
ReplicaSet.addInstance: Timeout waiting for server to restart (MYSQLSH 51156)

上述克隆完成之后，8402实例启动实例，原因是由于实例的启动方式不是通过mysqld_safe的方式启动的

调整8402实例启动方式为mysqld_safe的启动方式或者手动启动，然后重新添加实例节点，这里有点需要注意的是，重新添加后，就不在需要重新进行实例克隆了

6、查看集群状态

 MySQL  127.0.0.1:8401 ssl  JS > rs.status()
{
    "replicaSet": {
        "name": "innodb_replicaset_example", 
        "primary": "hb30-dbs-mysql-124-186:8401", 
        "status": "AVAILABLE", 
        "statusText": "All instances available.", 
        "topology": {
            "hb30-dbs-mysql-124-186:8401": {
                "address": "hb30-dbs-mysql-124-186:8401", 
                "instanceRole": "PRIMARY", 
                "mode": "R/W", 
                "status": "ONLINE"
            }, 
            "hb30-dbs-mysql-124-186:8402": {
                "address": "hb30-dbs-mysql-124-186:8402", 
                "instanceRole": "SECONDARY", 
                "mode": "R/O", 
                "replication": {
                    "applierStatus": "APPLIED_ALL", 
                    "applierThreadState": "Waiting for an event from Coordinator", 
                    "applierWorkerThreads": 8, 
                    "receiverStatus": "ON", 
                    "receiverThreadState": "Waiting for master to send event", 
                    "replicationLag": null
                }, 
                "status": "ONLINE"
            }
        }, 
        "type": "ASYNC"
    }
}

查看副本集状态，已添加到副本集的实例 8402的角色为 Secondary ，并自动与Primary 节点 8401 建立了复制关系

创建副本集的过程会自动创建名为mysql_innodb_cluster_metadata的元数据库，其中有6张表，9个视图表

mysql> show tables;
+-----------------------------------------+
| Tables_in_mysql_innodb_cluster_metadata |
+-----------------------------------------+
| async_cluster_members                   |
| async_cluster_views                     |
| clusters                                |
| instances                               |
| router_rest_accounts                    |
| routers                                 |
| schema_version                          |
| v2_ar_clusters                          |
| v2_ar_members                           |
| v2_clusters                             |
| v2_gr_clusters                          |
| v2_instances                            |
| v2_router_rest_accounts                 |
| v2_routers                              |
| v2_this_instance                        |
+-----------------------------------------+
15 rows in set (0.00 sec)

7、在集群中新增几个节点8403

MySQL 127.0.0.1:8401 ssl JS > rs.addInstance('127.0.0.1:8403')

8、再次查看集群的状态

MySQL  127.0.0.1:8403 ssl  JS > rs.status()
{
    "replicaSet": {
        "name": "innodb_replicaset_example", 
        "primary": "hb30-dbs-mysql-124-186:8401", 
        "status": "AVAILABLE", 
        "statusText": "All instances available.", 
        "topology": {
            "hb30-dbs-mysql-124-186:8401": {
                "address": "hb30-dbs-mysql-124-186:8401", 
                "instanceRole": "PRIMARY", 
                "mode": "R/W", 
                "status": "ONLINE"
            }, 
            "hb30-dbs-mysql-124-186:8402": {
                "address": "hb30-dbs-mysql-124-186:8402", 
                "instanceRole": "SECONDARY", 
                "mode": "R/O", 
                "replication": {
                    "applierStatus": "APPLIED_ALL", 
                    "applierThreadState": "Waiting for an event from Coordinator", 
                    "applierWorkerThreads": 8, 
                    "receiverStatus": "ON", 
                    "receiverThreadState": "Waiting for master to send event", 
                    "replicationLag": null
                }, 
                "status": "ONLINE"
            }, 
            "hb30-dbs-mysql-124-186:8403": {
                "address": "hb30-dbs-mysql-124-186:8403", 
                "instanceRole": "SECONDARY", 
                "mode": "R/O", 
                "replication": {
                    "applierStatus": "APPLYING", 
                    "applierThreadState": "Waiting for an event from Coordinator", 
                    "applierWorkerThreads": 4, 
                    "receiverStatus": "ON", 
                    "receiverThreadState": "Waiting for master to send event", 
                    "replicationLag": "00:00:00.000000"
                }, 
                "status": "ONLINE"
            }
        }, 
        "type": "ASYNC"
    }
}

五、副本集在线主从切换

1、手工在线将实例8402切换为 Primary 节点

 MySQL  127.0.0.1:8403 ssl  JS > rs.setPrimaryInstance('127.0.0.1:8402')
hb30-dbs-mysql-124-186:8402 will be promoted to PRIMARY of 'innodb_replicaset_example'.
The current PRIMARY is hb30-dbs-mysql-124-186:8401.

* Connecting to replicaset instances
** Connecting to hb30-dbs-mysql-124-186:8401
** Connecting to hb30-dbs-mysql-124-186:8402
** Connecting to hb30-dbs-mysql-124-186:8403
** Connecting to hb30-dbs-mysql-124-186:8401
** Connecting to hb30-dbs-mysql-124-186:8402
** Connecting to hb30-dbs-mysql-124-186:8403

* Performing validation checks
** Checking async replication topology...
** Checking transaction state of the instance...

* Synchronizing transaction backlog at hb30-dbs-mysql-124-186:8402

* Updating metadata

* Acquiring locks in replicaset instances
** Pre-synchronizing SECONDARIES
** Acquiring global lock at PRIMARY
** Acquiring global lock at SECONDARIES

* Updating replication topology
** Configuring hb30-dbs-mysql-124-186:8401 to replicate from hb30-dbs-mysql-124-186:8402
** Changing replication source of hb30-dbs-mysql-124-186:8403 to hb30-dbs-mysql-124-186:8402

hb30-dbs-mysql-124-186:8402 was promoted to PRIMARY.

实例 8402 被提升为 Primary 后，副本集将自动将 实例 8401 降级为 Secondary 并与 8402 建立复制关系，副本集中其它实例 8403 也将自动与8402建立复制与同步

MySQL  127.0.0.1:8403 ssl  JS > rs.status()
{
    "replicaSet": {
        "name": "innodb_replicaset_example", 
        "primary": "hb30-dbs-mysql-124-186:8402", 
        "status": "AVAILABLE", 
        "statusText": "All instances available.", 
        "topology": {
            "hb30-dbs-mysql-124-186:8401": {
                "address": "hb30-dbs-mysql-124-186:8401", 
                "instanceRole": "SECONDARY", 
                "mode": "R/O", 
                "replication": {
                    "applierStatus": "APPLIED_ALL", 
                    "applierThreadState": "Waiting for an event from Coordinator", 
                    "applierWorkerThreads": 4, 
                    "receiverStatus": "ON", 
                    "receiverThreadState": "Waiting for master to send event", 
                    "replicationLag": null
                }, 
                "status": "ONLINE"
            }, 
            "hb30-dbs-mysql-124-186:8402": {
                "address": "hb30-dbs-mysql-124-186:8402", 
                "instanceRole": "PRIMARY", 
                "mode": "R/W", 
                "status": "ONLINE"
            }, 
            "hb30-dbs-mysql-124-186:8403": {
                "address": "hb30-dbs-mysql-124-186:8403", 
                "instanceRole": "SECONDARY", 
                "mode": "R/O", 
                "replication": {
                    "applierStatus": "APPLIED_ALL", 
                    "applierThreadState": "Waiting for an event from Coordinator", 
                    "applierWorkerThreads": 4, 
                    "receiverStatus": "ON", 
                    "receiverThreadState": "Waiting for master to send event", 
                    "replicationLag": null
                }, 
                "status": "ONLINE"
            }
        }, 
        "type": "ASYNC"
    }
}

2、副本集 Primary 节点故障

当PRIMARY依旧出于可用的状态的时候，执行rs.forcePrimaryInstance是不允许的

 MySQL  127.0.0.1:8402 ssl  JS > rs.forcePrimaryInstance("127.0.0.1:8403")
* Connecting to replicaset instances
** Connecting to hb30-dbs-mysql-124-186:8401
** Connecting to hb30-dbs-mysql-124-186:8403

* Waiting for all received transactions to be applied
** Waiting for received transactions to be applied at hb30-dbs-mysql-124-186:8403
** Waiting for received transactions to be applied at hb30-dbs-mysql-124-186:8401
hb30-dbs-mysql-124-186:8403 will be promoted to PRIMARY of the replicaset and the former PRIMARY will be invalidated.

* Checking status of last known PRIMARY
PRIMARY hb30-dbs-mysql-124-186:8402 is still available.
Operation not allowed while there is still an available PRIMARY. Use setPrimaryInstance() to safely switch the PRIMARY.
ReplicaSet.forcePrimaryInstance: PRIMARY still available (MYSQLSH 51116)

当把8402 PRIMARY节点的mysqld进程杀死之后，出现如下的情况，复制断掉，但是mysqld_safe会重试重新把实例给启动，启动成功后，主从复制会自动重新修复；

杀掉相关的PRIMARY的进程

[root@hb30-dbs-mysql-124-186 mysql_8403]# ps -ef | grep 8402                              
[root@hb30-dbs-mysql-124-186 mysql_8403]# kill -9 89625  88089
[3]+  Killed                  /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql_8402/my.cnf

副本集 无法自动进行故障转移 ，需要人工介入修复

 MySQL  127.0.0.1:8403 ssl  JS > rs.status()
ERROR: Unable to connect to the PRIMARY of the replicaset innodb_replicaset_example: MySQL Error 2003: Could not open connection to 'hb30-dbs-mysql-124-186:8402': Can't connect to MySQL server on 'hb30-dbs-mysql-124-186' (111)
Cluster change operations will not be possible unless the PRIMARY can be reached.
If the PRIMARY is unavailable, you must either repair it or perform a forced failover.
See \help forcePrimaryInstance for more information.
WARNING: MYSQLSH 51118: PRIMARY instance is unavailable
{
    "replicaSet": {
        "name": "innodb_replicaset_example", 
        "primary": "hb30-dbs-mysql-124-186:8402", 
        "status": "UNAVAILABLE", 
        "statusText": "PRIMARY instance is not available, but there is at least one SECONDARY that could be force-promoted.", 
        "topology": {
            "hb30-dbs-mysql-124-186:8401": {
                "address": "hb30-dbs-mysql-124-186:8401", 
                "fenced": true, 
                "instanceErrors": [
                    "ERROR: Replication I/O thread (receiver) has stopped with an error."
                ], 
                "instanceRole": "SECONDARY", 
                "mode": "R/O", 
                "replication": {
                    "applierStatus": "APPLIED_ALL", 
                    "applierThreadState": "Waiting for an event from Coordinator", 
                    "applierWorkerThreads": 4, 
                    "expectedSource": "hb30-dbs-mysql-124-186:8402", 
                    "receiverLastError": "error reconnecting to master 'mysql_innodb_rs_310@hb30-dbs-mysql-124-186:8402' - retry-time: 60 retries: 1 message: Can't connect to MySQL server on 'hb30-dbs-mysql-124-186' (111)", 
                    "receiverLastErrorNumber": 2003, 
                    "receiverLastErrorTimestamp": "2021-02-21 15:23:33.794260", 
                    "receiverStatus": "ERROR", 
                    "receiverThreadState": "", 
                    "replicationLag": null, 
                    "source": "hb30-dbs-mysql-124-186:8402"
                }, 
                "status": "ERROR", 
                "transactionSetConsistencyStatus": null
            }, 
            "hb30-dbs-mysql-124-186:8402": {
                "address": "hb30-dbs-mysql-124-186:8402", 
                "connectError": "Could not open connection to 'hb30-dbs-mysql-124-186:8402': Can't connect to MySQL server on 'hb30-dbs-mysql-124-186' (111)", 
                "fenced": null, 
                "instanceRole": "PRIMARY", 
                "mode": null, 
                "status": "UNREACHABLE"
            }, 
            "hb30-dbs-mysql-124-186:8403": {
                "address": "hb30-dbs-mysql-124-186:8403", 
                "fenced": true, 
                "instanceErrors": [
                    "ERROR: Replication I/O thread (receiver) has stopped with an error."
                ], 
                "instanceRole": "SECONDARY", 
                "mode": "R/O", 
                "replication": {
                    "applierStatus": "APPLIED_ALL", 
                    "applierThreadState": "Waiting for an event from Coordinator", 
                    "applierWorkerThreads": 4, 
                    "expectedSource": "hb30-dbs-mysql-124-186:8402", 
                    "receiverLastError": "error reconnecting to master 'mysql_innodb_rs_312@hb30-dbs-mysql-124-186:8402' - retry-time: 60 retries: 1 message: Can't connect to MySQL server on 'hb30-dbs-mysql-124-186' (111)", 
                    "receiverLastErrorNumber": 2003, 
                    "receiverLastErrorTimestamp": "2021-02-21 15:23:33.793532", 
                    "receiverStatus": "ERROR", 
                    "receiverThreadState": "", 
                    "replicationLag": null, 
                    "source": "hb30-dbs-mysql-124-186:8402"
                }, 
                "status": "ERROR", 
                "transactionSetConsistencyStatus": null
            }
        }, 
        "type": "ASYNC"
    }
}

强制设置8403为主节点

MySQL  127.0.0.1:8403 ssl  JS >  rs.forcePrimaryInstance("127.0.0.1:8403")
* Connecting to replicaset instances
** Connecting to hb30-dbs-mysql-124-186:8401
** Connecting to hb30-dbs-mysql-124-186:8403

* Waiting for all received transactions to be applied
** Waiting for received transactions to be applied at hb30-dbs-mysql-124-186:8403
** Waiting for received transactions to be applied at hb30-dbs-mysql-124-186:8401
hb30-dbs-mysql-124-186:8403 will be promoted to PRIMARY of the replicaset and the former PRIMARY will be invalidated.

* Checking status of last known PRIMARY
NOTE: hb30-dbs-mysql-124-186:8402 is UNREACHABLE
* Checking status of promoted instance
NOTE: hb30-dbs-mysql-124-186:8403 has status ERROR
* Checking transaction set status
* Promoting hb30-dbs-mysql-124-186:8403 to a PRIMARY...

* Updating metadata...

hb30-dbs-mysql-124-186:8403 was force-promoted to PRIMARY.
NOTE: Former PRIMARY hb30-dbs-mysql-124-186:8402 is now invalidated and must be removed from the replicaset.
* Updating source of remaining SECONDARY instances
** Changing replication source of hb30-dbs-mysql-124-186:8401 to hb30-dbs-mysql-124-186:8403

Failover finished successfully.

副本集恢复后，因 8402 不可用副本集状态显示为 INVALIDATED

 MySQL  127.0.0.1:8403 ssl  JS > rs.status()
{
    "replicaSet": {
        "name": "innodb_replicaset_example", 
        "primary": "hb30-dbs-mysql-124-186:8403", 
        "status": "AVAILABLE_PARTIAL", 
        "statusText": "The PRIMARY instance is available, but one or more SECONDARY instances are not.", 
        "topology": {
            "hb30-dbs-mysql-124-186:8401": {
                "address": "hb30-dbs-mysql-124-186:8401", 
                "instanceRole": "SECONDARY", 
                "mode": "R/O", 
                "replication": {
                    "applierStatus": "APPLIED_ALL", 
                    "applierThreadState": "Waiting for an event from Coordinator", 
                    "applierWorkerThreads": 4, 
                    "receiverStatus": "ON", 
                    "receiverThreadState": "Waiting for master to send event", 
                    "replicationLag": null
                }, 
                "status": "ONLINE"
            }, 
            "hb30-dbs-mysql-124-186:8402": {
                "address": "hb30-dbs-mysql-124-186:8402", 
                "connectError": "Could not open connection to 'hb30-dbs-mysql-124-186:8402': Can't connect to MySQL server on 'hb30-dbs-mysql-124-186' (111)", 
                "fenced": null, 
                "instanceRole": null, 
                "mode": null, 
                "status": "INVALIDATED"
            }, 
            "hb30-dbs-mysql-124-186:8403": {
                "address": "hb30-dbs-mysql-124-186:8403", 
                "instanceRole": "PRIMARY", 
                "mode": "R/W", 
                "status": "ONLINE"
            }
        }, 
        "type": "ASYNC"
    }
}

如果此时强制移除8402节点的话，是无法成功的，如下所示：

 MySQL  127.0.0.1:8403 ssl  JS > rs.removeInstance('127.0.0.1:8402', {force:true})
ERROR: Instance 127.0.0.1:8402 is unreachable and was not found in the replicaset metadata. The exact address of the instance as recorded in the metadata must be used in cases where the target is unreachable.
ReplicaSet.removeInstance: 127.0.0.1:8402 does not belong to the replicaset (MYSQLSH 51310)

接下来，将8402节点修复完成，重新启动之后，集群节点状态如下，重点注意8402节点

MySQL  127.0.0.1:8402 ssl  JS > rs.status()
{
    "replicaSet": {
        "name": "innodb_replicaset_example", 
        "primary": "hb30-dbs-mysql-124-186:8403", 
        "status": "AVAILABLE_PARTIAL", 
        "statusText": "The PRIMARY instance is available, but one or more SECONDARY instances are not.", 
        "topology": {
            "hb30-dbs-mysql-124-186:8401": {
                "address": "hb30-dbs-mysql-124-186:8401", 
                "instanceRole": "SECONDARY", 
                "mode": "R/O", 
                "replication": {
                    "applierStatus": "APPLIED_ALL", 
                    "applierThreadState": "Waiting for an event from Coordinator", 
                    "applierWorkerThreads": 4, 
                    "receiverStatus": "ON", 
                    "receiverThreadState": "Waiting for master to send event", 
                    "replicationLag": null
                }, 
                "status": "ONLINE"
            }, 
            "hb30-dbs-mysql-124-186:8402": {
                "address": "hb30-dbs-mysql-124-186:8402", 
                "fenced": false, 
                "instanceErrors": [
                    "WARNING: Instance was INVALIDATED and must be removed from the replicaset.", 
                    "ERROR: Instance is NOT a PRIMARY but super_read_only option is OFF. Accidental updates to this instance are possible and will cause inconsistencies in the replicaset."
                ], 
                "instanceRole": null, 
                "mode": null, 
                "status": "INVALIDATED", 
                "transactionSetConsistencyStatus": "OK"
            }, 
            "hb30-dbs-mysql-124-186:8403": {
                "address": "hb30-dbs-mysql-124-186:8403", 
                "instanceRole": "PRIMARY", 
                "mode": "R/W", 
                "status": "ONLINE"
            }
        }, 
        "type": "ASYNC"
    }
}

处理掉8402节点的问题之后，然后重新再进行移除

 MySQL  127.0.0.1:8402 ssl  JS > rs.removeInstance('127.0.0.1:8402')
WARNING: Replication is not active in instance hb30-dbs-mysql-124-186:8402.
ERROR: Instance cannot be safely removed while in this state.
Use the 'force' option to remove this instance anyway. The instance may be left in an inconsistent state after removed.
ReplicaSet.removeInstance: Replication is not active in target instance (MYSQLSH 51132)

根据报错信息提示： 节点状态处于INVALIDATED状态的时候，是无法删除的，需要force进行删除

 MySQL  127.0.0.1:8402 ssl  JS > rs.removeInstance('127.0.0.1:8402', {force:true})
WARNING: Replication is not active in instance hb30-dbs-mysql-124-186:8402.
NOTE: hb30-dbs-mysql-124-186:8402 is invalidated, replication sync will be skipped.
The instance 'hb30-dbs-mysql-124-186:8402' was removed from the replicaset.

 MySQL  127.0.0.1:8402 ssl  JS > 
 MySQL  127.0.0.1:8402 ssl  JS > rs.status()
{
    "replicaSet": {
        "name": "innodb_replicaset_example", 
        "primary": "hb30-dbs-mysql-124-186:8403", 
        "status": "AVAILABLE", 
        "statusText": "All instances available.", 
        "topology": {
            "hb30-dbs-mysql-124-186:8401": {
                "address": "hb30-dbs-mysql-124-186:8401", 
                "instanceRole": "SECONDARY", 
                "mode": "R/O", 
                "replication": {
                    "applierStatus": "APPLIED_ALL", 
                    "applierThreadState": "Waiting for an event from Coordinator", 
                    "applierWorkerThreads": 4, 
                    "receiverStatus": "ON", 
                    "receiverThreadState": "Waiting for master to send event", 
                    "replicationLag": null
                }, 
                "status": "ONLINE"
            }, 
            "hb30-dbs-mysql-124-186:8403": {
                "address": "hb30-dbs-mysql-124-186:8403", 
                "instanceRole": "PRIMARY", 
                "mode": "R/W", 
                "status": "ONLINE"
            }
        }, 
        "type": "ASYNC"
    }
}

小结

1、InnoDB ReplicaSet 当前还不完善，可作为新特性在测试环境试用，但因为不支持自动故障转移，Primary 宕机整个副本集将不可用。

2、 InnoDB ReplicaSet 目前仅支持基于 GTID 的异步复制，哪怕支持自动切换，数据也有丢失风险，所以部署到生产环境还需要谨慎考虑。

3、InnoDB ReplicaSet 暂时还没有完善的投票选举机制，故障切换时也会存在脑裂风险。

总之，InnoDB 副本集虽存在诸多不足之处，但是其带来的效果也让众多 MySQL DBA 眼前一亮，既然有了副本集，相信 Sharding 也是未来可期，你觉得呢？

参考链接

https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-innodb-replicaset.html

https://my.oschina.net/actiontechoss/blog/3185337

MySQL 8.0新特性：InnoDB ReplicaSet

 

如果您觉得本站对你有帮助，那么可以收藏和推荐本站，帮助本站更好地发展，在此谢过各位网友的支持。
转载请注明原文链接：MySQL 8.0新特性：InnoDB ReplicaSet
喜欢 (5)
SEian.G
关于作者：
用心去记录工作，用心去感受生活，用心去学着成长；座右铭：苦练七十二变，笑对八十一难
作者主页
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi，您需要填写昵称和邮箱！

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址