InnoDB ReplicaSet

简介

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的快速简便方法，使其非常适合横向扩展读取。

与InnoDB Cluster相比，InnoDB ReplicaSet具有如下限制：

• 没有自动故障切换，如果主不可用，则需要手动触发故障转移
• 无法防止因意外或不可用导致的部分数据丢失(因为是异步复制)
• 无法防止崩溃或不可用后出现不一致情况。

部署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一起使用。

环境

规划

IP:PORT	主机名	server-id	系统版本	MySQL版本	MySQL Shell版本	MySQL Router版本
192.168.240.81:3310	innodb_cluster_01	81	CentOS7.6	8.0.21	mysql-shell-8.0.21	mysql-router-8.0.21
192.168.240.82:3310	innodb_cluster_02	82	CentOS7.6	8.0.21	-	-
192.168.240.83:3310	innodb_cluster_03	83	CentOS7.6	8.0.21	-	-

MySQL Shell ,Rpm包下载地址:https://dev.mysql.com/downloads/shell/

MySQL Router,Rpm包下载地址:https://dev.mysql.com/downloads/router/

MySQL ，二进制包下载地址：https://dev.mysql.com/downloads/mysql/

前期准备

1. 设置主机名

InnoDB ReplicaSet集群内部节点主机名必须唯一，可以通过hostname命令设置

按照先前规划，填写/etc/hosts文件

[root@localhost ~]# cat /etc/hosts
192.168.240.81 innodb_rs_01
192.168.240.82 innodb_rs_02
192.168.240.83 innodb_rs_03

修改本机主机名(每台机根据情况修改自己主机名)

hostnamectl  set-hostname innodb_rs_01

2. 关闭防火墙与selinux

systemctl  stop firewalld
setenforce  0

安装MySQL

把MySQL二进制安装包上传到/usr/local/src目录下

cd /usr/local/src
tar xf mysql-8.0.21-linux-glibc2.17-x86_64-minimal.tar.xz
mv mysql-8.0.21-linux-glibc2.17-x86_64-minimal /usr/local/mysql8.0
echo 'export  PATH=/usr/local/mysql8.0/bin:$PATH' >> /etc/profile
. /etc/profile

创建目录结构(所有节点)

useradd -M -s /sbin/nologin  mysql
mkdir -pv  /datadir/{temp,log,data}
chown -R mysql:mysql /datadir

初始化数据库(所有节点)

mysqld  --datadir=/datadir/data --user=mysql --initialize-insecure
chown -R mysql:mysql /datadir

修改配置文件(所有节点)

[mysqld]
basedir=/usr/local/mysql8.0
user=mysql
port=3310
datadir=/datadir/data
log-error=/datadir/log/err.log
pid-file=/datadir/temp/mysqld.pid
socket=/datadir/temp/mysqld.sock
symbolic-links=0
server_id=81
gtid-mode=on
enforce-gtid-consistency=true

log_bin=/datadir/log/binlog
binlog_format=ROW

[client]
socket=/datadir/temp/mysqld.sock

只需要修改server_id这一项即可(保证所有MySQL服务器都不同)

启动数据库(所有节点)

mysqld_safe --defaults-file=/etc/my.cnf --daemonize

进入mysql，创建ir用户(所有节点)

set sql_log_bin=OFF;
CREATE USER 'ir'@'%' IDENTIFIED BY '123456';
GRANT ALL ON *.* to 'ir'@'%' WITH GRANT OPTION;
set sql_log_bin=ON;

安装MySQL Shell

上传MySQL Shell包到innodb_rs_01的/usr/local/src目录下

cd /usr/local/src
yum install mysql-shell-8.0.21-1.el7.x86_64.rpm -y

配置InnoDB ReplicaSet

1. 进入MySQL Shell（innodb_rs_01节点执行）

mysqlsh

2. 创建rsadmin的集群管理员(此处以innodb_rs_01节点为例)

 MySQL  JS >  dba.configureReplicaSetInstance('ir@innodb_rs_01:3310', {clusterAdmin: "'rsadmin'@'%'"});
Please provide the password for 'ir@innodb_rs_01:3310': ******* # 此处填写ir用户的密码
Save password for 'ir@innodb_rs_01:3310'? [Y]es/[N]o/Ne[v]er (default No): y # 是否记住密码，如果选择y则下次连接无需重新输入密码
Configuring local MySQL instance listening at port 3310 for use in an InnoDB ReplicaSet...

This instance reports its own address as innodb_rs_01:3310
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: ******# 此处填写'rsadmin'@'%' 想要设置的密码
Confirm password: ****** # 确认'rsadmin'@'%'密码

The instance 'innodb_rs_01:3310' is valid to be used in an InnoDB ReplicaSet.
Cluster admin user 'rsadmin'@'%' created.
The instance 'innodb_rs_01:3310' is already ready to be used in an InnoDB ReplicaSet. # 表示已经就绪，可以创建innodb ReplicaSet

1. dba.configureReplicaSetInstance()函数可以创建管理员帐户。使用正确的权限来创建帐户来管理InnoDB集群和InnoDB副本集。
2. 在同一集群或副本集的所有实例中，管理员帐户必须具有相同的用户名和密码。

3. 创建Innodb ReplicaSet（innodb_rs_01节点执行）

mysql-js> \connect ir@innodb_rs_01:3310
MySQL  innodb_rs_01:3310 ssl  JS > var rs = dba.createReplicaSet("example") 
A new replicaset with instance 'innodb_rs_01:3310' will be created.

* Checking MySQL instance at innodb_rs_01:3310

This instance reports its own address as innodb_rs_01:3310
innodb_rs_01:3310: Instance configuration is suitable.

* Updating metadata...

ReplicaSet object successfully created for innodb_rs_01:3310.
Use rs.addInstance() to add more asynchronously replicated instances to this replicaset and rs.status() to check its status.

4. 查看ReplicaSet状态（innodb_rs_01节点执行）

 MySQL  innodb_rs_01:3310 ssl  JS > rs.status()
{
    "replicaSet": { 
        "name": "example", # 复制集名称
        "primary": "innodb_rs_01:3310", # 主节点
        "status": "AVAILABLE", # 状态：可用
        "statusText": "All instances available.", # 状态备注信息
        "topology": { # 当前拓扑
            "innodb_rs_01:3310": { 
                "address": "innodb_rs_01:3310", # 复制集通讯地址
                "instanceRole": "PRIMARY", # 角色，主/从
                "mode": "R/W", # 模式，主节点可读可写，从节点只读
                "status": "ONLINE" # 节点状态
            }
        },
        "type": "ASYNC" # 复制类型 异步
    }
}

5. 添加innodb_rs_02:3310实例进入集群（innodb_rs_01节点执行）

 MySQL  innodb_rs_01:3310 ssl  JS > rs.addInstance('innodb_rs_02:3310')
Adding instance to the replicaset...

* Performing validation checks

This instance reports its own address as innodb_rs_02:3310
innodb_rs_02:3310: Instance configuration is suitable.

* Checking async replication topology...

* Checking transaction state of the instance...

NOTE: The target instance 'innodb_rs_02:3310' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide whether replication can completely recover its state.
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'innodb_rs_02:3310' with a physical snapshot from an existing replicaset member. To use this method by default, set the 'recoveryMethod' option to 'clone'.

WARNING: It should be safe to rely on replication to incrementally recover the state of the new instance if you are sure all updates ever executed in the replicaset were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the replicaset or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.

# 输入I，选择增量恢复，需要确保所有实例GTID打开，以及没有被purged的事务，且gitd set必须是主节点的子集
Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): i  
* Updating topology
** Configuring innodb_rs_02:3310 to replicate from innodb_rs_01:3310
** Waiting for new instance to synchronize with PRIMARY...

The instance 'innodb_rs_02:3310' was added to the replicaset and is replicating from innodb_rs_01:3310.

6. 添加innodb_rs_03:3310实例进入集群（innodb_rs_01节点执行）

 MySQL  innodb_rs_01:3310 ssl  JS > rs.addInstance('innodb_rs_03:3310')
Adding instance to the replicaset...

* Performing validation checks

This instance reports its own address as innodb_rs_03:3310
innodb_rs_03:3310: Instance configuration is suitable.

* Checking async replication topology...

* Checking transaction state of the instance...

NOTE: The target instance 'innodb_rs_03:3310' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide whether replication can completely recover its state.
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'innodb_rs_03:3310' with a physical snapshot from an existing replicaset member. To use this method by default, set the 'recoveryMethod' option to 'clone'.

WARNING: It should be safe to rely on replication to incrementally recover the state of the new instance if you are sure all updates ever executed in the replicaset were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the replicaset or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.

# 输入I，选择增量恢复，需要确保所有实例GTID打开，以及没有被purged的事务，且gitd set必须是主节点的子集
Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): i
* Updating topology
** Configuring innodb_rs_03:3310 to replicate from innodb_rs_01:3310
** Waiting for new instance to synchronize with PRIMARY...

The instance 'innodb_rs_03:3310' was added to the replicaset and is replicating from innodb_rs_01:3310.

7. 查看集群状态

MySQL  innodb_rs_01:3310 ssl  JS > rs.status()
{
    "replicaSet": {
        "name": "example",# 复制集名称
        "primary": "innodb_rs_01:3310",# 主节点
        "status": "AVAILABLE",# 状态：可用
        "statusText": "All instances available.", # 状态备注信息
        "topology": { # 当前拓扑
            "innodb_rs_01:3310": {
                "address": "innodb_rs_01:3310", # 复制集通讯地址
                "instanceRole": "PRIMARY", # 角色，主/从
                "mode": "R/W", # 模式，主节点可读可写，从节点只读
                "status": "ONLINE" # 节点状态
            },
            "innodb_rs_02:3310": {
                "address": "innodb_rs_02:3310",
                "instanceRole": "SECONDARY",
                "mode": "R/O",
                "replication": {
                    "applierStatus": "APPLIED_ALL",
                    "applierThreadState": "Slave has read all relay log; waiting for more updates",
                    "receiverStatus": "ON",
                    "receiverThreadState": "Waiting for master to send event",
                    "replicationLag": null
                },
                "status": "ONLINE"
            },
            "innodb_rs_03:3310": {
                "address": "innodb_rs_03:3310",
                "instanceRole": "SECONDARY",
                "mode": "R/O",
                "replication": {
                    "applierStatus": "APPLIED_ALL",
                    "applierThreadState": "Slave has read all relay log; waiting for more updates",
                    "receiverStatus": "ON",
                    "receiverThreadState": "Waiting for master to send event",
                    "replicationLag": null
                },
                "status": "ONLINE"
            }
        },
        "type": "ASYNC" # 复制类型 异步
    }
}

配置MySQL Router

上传MySQL Router RPM包innodb_rs_03节点上的/usr/local/src目录下

cd /usr/local/src
yum install mysql-router-community-8.0.21-1.el7.x86_64.rpm  -y

MySQL Router对接InnoDB Cluster

mysqlrouter --bootstrap ir@innodb_rs_01:3310 --user=mysqlrouter

启动MySQL Router

systemctl start mysqlrouter

会启动4个端口

• 6446 - 对于传统MySQL协议读写会话，MySQL路由器将传入连接重定向到Primary服务器实例。
• 6447 - 对于传统MySQL协议只读会话，MySQL路由器将传入连接重定向到其中一个从服务器实例。
• 64460 - 对于X协议读写会话，MySQL路由器将传入连接重定向到Primary服务器实例。
• 64470 - 对于X协议只读会话，MySQL路由器将传入连接重定向到其中一个从服务器实例。

连接读写端口,进行测试

# mysql -uir -p -P6446 -h192.168.240.83
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 109
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|          81 |
+-------------+
1 row in set (0.00 sec)

InnoDB ReplicaSet日常管理

1. 连接InnoDB 复制集

   # 连接其中一个复制集节点
    MySQL  JS > \c ir@innodb_rs_02:3310
   # 获取复制集连接
    MySQL  innodb_rs_02:3310 ssl  JS > var rs = dba.getReplicaSet()
   # 后续就可以直接使用rs对象管理集群
   

2. 查看集群状态

    MySQL  innodb_rs_02:3310 ssl  JS > rs.status()
   {
       "replicaSet": {
           "name": "example",
           "primary": "innodb_rs_01:3310",
           "status": "AVAILABLE",
           "statusText": "All instances available.",
           "topology": {
               "innodb_rs_01:3310": {
                   "address": "innodb_rs_01:3310",
                   "instanceRole": "PRIMARY",
                   "mode": "R/W",
                   "status": "ONLINE"
               },
               "innodb_rs_02:3310": {
                   "address": "innodb_rs_02:3310",
                   "instanceRole": "SECONDARY",
                   "mode": "R/O",
                   "replication": {
                       "applierStatus": "APPLIED_ALL",
                       "applierThreadState": "Slave has read all relay log; waiting for more updates",
                       "receiverStatus": "ON",
                       "receiverThreadState": "Waiting for master to send event",
                       "replicationLag": null
                   },
                   "status": "ONLINE"
               },
               "innodb_rs_03:3310": {
                   "address": "innodb_rs_03:3310",
                   "instanceRole": "SECONDARY",
                   "mode": "R/O",
                   "replication": {
                       "applierStatus": "APPLIED_ALL",
                       "applierThreadState": "Slave has read all relay log; waiting for more updates",
                       "receiverStatus": "ON",
                       "receiverThreadState": "Waiting for master to send event",
                       "replicationLag": null
                   },
                   "status": "ONLINE"
               }
           },
           "type": "ASYNC"
       }
   }
   
   

3. 新增实例

   # 新增innodb_rs_03:3310节点
   MySQL  innodb_rs_02:3310 ssl  JS > rs.addInstance('innodb_rs_03:3310')
   

4. 强制切换主节点

   # 当主节点down了，就需要强制切换主
   rs.forcePrimaryInstance('innodb_rs_03:3310')
   

5. 设置主节点

    MySQL  innodb_rs_02:3310 ssl  JS > rs.setPrimaryInstance('innodb_rs_03:3310')
   innodb_rs_03:3310 will be promoted to PRIMARY of 'example'.
   The current PRIMARY is innodb_rs_01:3310.
   
   * Connecting to replicaset instancess
   ** Connecting to innodb_rs_01:3310
   ** Connecting to innodb_rs_02:3310
   ** Connecting to innodb_rs_03:3310
   ** Connecting to innodb_rs_01:3310
   ** Connecting to innodb_rs_02:3310
   ** Connecting to innodb_rs_03:3310
   
   * Performing validation checks
   ** Checking async replication topology...
   ** Checking transaction state of the instance...
   
   * Synchronizing transaction backlog at innodb_rs_03:3310
   
   * Updating metadata
   
   * Acquiring locks in replicaset instances
   ** Pre-synchronizing SECONDARIES
   ** Acquiring global lock at PRIMARY
   ** Acquiring global lock at SECONDARIES
   
   * Updating replication topology
   ** Configuring innodb_rs_01:3310 to replicate from innodb_rs_03:3310
   ** Changing replication source of innodb_rs_02:3310 to innodb_rs_03:3310
   
   innodb_rs_03:3310 was promoted to PRIMARY.
   
   

6. 移除实例

   # 从ReplicaSet中移除innodb_rs_03:3310节点
   MySQL  innodb_rs_02:3310 ssl  JS > rs.removeInstance('innodb_rs_03:3310')
   

7. 查看MySQL Router信息

    MySQL  innodb_rs_02:3310 ssl  JS > rs.listRouters()
   {
       "replicaSetName": "example",
       "routers": {
           "innodb_rs_03::system": {
               "hostname": "innodb_rs_03",
               "lastCheckIn": "2020-08-06 00:09:40",
               "roPort": 6447,
               "roXPort": 64470,
               "rwPort": 6446,
               "rwXPort": 64460,
               "version": "8.0.21"
           }
       }
   }
   

   参考文档

)

7. 查看MySQL Router信息

```js
 MySQL  innodb_rs_02:3310 ssl  JS > rs.listRouters()
{
    "replicaSetName": "example",
    "routers": {
        "innodb_rs_03::system": {
            "hostname": "innodb_rs_03",
            "lastCheckIn": "2020-08-06 00:09:40",
            "roPort": 6447,
            "roXPort": 64470,
            "rwPort": 6446,
            "rwXPort": 64460,
            "version": "8.0.21"
        }
    }
}

参考文档

• https://dev.mysql.com/doc/refman/8.0/en/mysql-innodb-replicasets.html