准备工作

MySQL InnoDB Cluster可直接部署，也可以在MGR基础上部署，本次尝试在已有的MGR基础上部署innodb cluster。

本次使用操作系统版本为ubuntu22.04。
首先提前部署MGR环境，参考MySQL高可用方案之MGRProxySQLKeepalived中相关章节即可，此处不再赘述。

下载MySQL Router和MySQL Shell软件包（选择glibc2.17版本的二进制包）并上传至服务器/app/database/下。
下载地址：

https://downloads.mysql.com/archives/router/
https://downloads.mysql.com/archives/shell/

本次使用的软件版本如下：
MySQL版本是8.0.36，
MySQL Router版本是8.0.36，
MySQL Shell版本是8.2.1。

安装MySQL Shell

解压安装：

cd /app/database
tar -xf mysql-shell-8.2.1-linux-glibc2.17-x86-64bit.tar.gz
mv mysql-shell-8.2.1-linux-glibc2.17-x86-64bit mysql-shell

配置环境变量：

vim /etc/profile

添加：

export PATH=$PATH:/app/database/mysql-shell/bin

生效：

source /etc/profile

检查版本：

mysqlsh --version

创建InnoDB Cluster

运行mysqlshell（三个MGR节点均执行）：

mysqlsh

进入后连接所有MGR节点并根据提示保存密码（三个MGR节点均执行）：

shell.connect('root@10.0.19.105:3306')
shell.connect('root@10.0.19.106:3306')
shell.connect('root@10.0.19.107:3306')

检查MGR所有节点状态（在一个节点执行即可）：

dba.checkInstanceConfiguration('root@10.0.19.105:3306')
dba.checkInstanceConfiguration('root@10.0.19.106:3306')
dba.checkInstanceConfiguration('root@10.0.19.107: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.

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

我们在mysql-shell中执行如下命令修复（在一个节点执行即可）：

dba.configureInstance('root@10.0.19.105:3306')
dba.configureInstance('root@10.0.19.106:3306')
dba.configureInstance('root@10.0.19.107:3306')

或者我们在mysql客户端修改相应变量（三个节点均执行）：

 set global binlog_transaction_dependency_tracking=WRITESET;

【注】my.cnf中也添加此项配置。

再次检查状态通过：

Instance configuration is compatible with InnoDB cluster

The instance 'vl7mysqlsit105:3306' is valid to be used in an InnoDB cluster.

{
    "status": "ok"
}

三个节点都通过后可进行下一步。

连接primary节点并创建一个集群：

shell.connect('root@10.0.19.105:3306')
var cluster = dba.createCluster('testCluster');

输出如下（两个secondary节点也已经加进来了）：

A new InnoDB Cluster will be created on instance 'vl7mysqlsit105:3306'.

You are connected to an instance that belongs to an unmanaged replication group.
Do you want to setup an InnoDB Cluster based on this replication group? [Y/n]: Y
Creating InnoDB Cluster 'testCluster' on 'vl7mysqlsit105:3306'...

Adding Seed Instance...
Adding Instance 'vl7mysqlsit105:3306'...
Adding Instance 'vl7mysqlsit106:3306'...
Adding Instance 'vl7mysqlsit107:3306'...
Resetting distributed recovery credentials across the cluster...
Cluster successfully created based on existing replication group.

检查集群状态：

cluster.status();

输出结果如下：

{
    "clusterName": "testCluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "vl7mysqlsit105:3306",
        "ssl": "DISABLED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "vl7mysqlsit105:3306": {
                "address": "vl7mysqlsit105:3306",
                "memberRole": "PRIMARY",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.36"
            },
            "vl7mysqlsit106:3306": {
                "address": "vl7mysqlsit106:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.36"
            },
            "vl7mysqlsit107:3306": {
                "address": "vl7mysqlsit107:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.36"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "vl7mysqlsit105:3306"
}

节点重启后如果未加入集群，参考如下方法加入：

https://www.cnblogs.com/williamzheng/p/11340604.html#%E4%B8%89%E5%B0%86mgr%E8%8A%82%E7%82%B9%E5%8A%A0%E5%85%A5mysql-cluster

#安装MySQL Router
解压安装：

cd /app/database
tar -xf mysql-router-8.0.36-linux-glibc2.17-x86_64.tar.xz
mv mysql-router-8.0.36-linux-glibc2.17-x86_64 mysql-router

配置环境变量：

vim /etc/profile

添加：

export PATH=$PATH:/app/database/mysql-router/bin

生效：

source /etc/profile

检查版本：

mysqlrouter --version

配置MySQL Router

MySQL Router有两种配置模式：简单模式和bootstrap模式。
其中简单模式不支持failover，bootstrap模式支持failover，但是必须结合InnoDB Cluster使用，本次我们采用bootstrap模式，在MGR第三个节点上启动MySQL Router。实际一般建议将MySQL Router部署在应用服务器上，连接效率更高。
bootstrap模式会自动配置参数，初始化时在–directory指定的路径下自动生成安装目录，配置文件里的端口为6446和6447。

我们先建立一个叫mysqlrouter的数据库账号，（如果初始化MySQL Router时，没有指定参数—account的话，初始化会在数据库创建一个账号给MySQL Router使用）
然后创建/data/mysqlrouter目录并授权，
然后进行初始化：

mysqlrouter --bootstrap root@10.0.19.107:3306 --directory /data/mysqlrouter --account mysqlrouter --user mysql --name 'test_router1'

其中–user 是运行mysqlrouter进程的系统用户，
–bootstrap 表示开始初始化，
–directory 是生成的配置目录，
–account 代表初始化后连接mysql账号，
如果指定–config或-c选项，则传入用户定义的配置文件，不会加载默认配置文件

输出结果为：

Please enter MySQL password for root:
# Bootstrapping MySQL Router 8.0.36 (MySQL Community - GPL) instance at '/data/mysqlrouter'...

Please enter MySQL password for mysqlrouter:
Fetching Cluster Members
trying to connect to mysql-server at vl7mysqlsit105:3306
- Creating account(s) (only those that are needed, if any)
Failed changing the authentication plugin for account 'mysqlrouter'@'%':  mysql_native_password which is deprecated is the default authentication plugin on this server.
- Verifying account (using it to run SQL queries that would be run by Router)
- Storing account in keyring
- Adjusting permissions of generated files
- Creating configuration /data/mysqlrouter/mysqlrouter.conf

# MySQL Router 'test_router1' configured for the InnoDB Cluster 'testCluster'

After this MySQL Router has been started with the generated configuration

    $ mysqlrouter -c /data/mysqlrouter/mysqlrouter.conf

InnoDB Cluster 'testCluster' can be reached by connecting to:

## MySQL Classic protocol

- Read/Write Connections: localhost:6446
- Read/Only Connections:  localhost:6447

## MySQL X protocol

- Read/Write Connections: localhost:6448
- Read/Only Connections:  localhost:6449

启停MySQL Router

用生成的shell脚本启动MySQL Router

cd /data/mysqlrouter
sh start.sh

停止：

sh stop.sh

mysqlrouter脚本启动方式：

mysqlrouter --config=/data/mysqlrouter/mysqlrouter.conf &

用kill命令停止：

ps -ef | grep mysqlrouter 
kill xxxx

启动后检查：

netstat -ntlp |grep mysqlrouter
ps -ef | grep mysqlrouter

使用MySQL客户端连接工具进行检查。