MySQL Innodb Cluster 配置

MySQL InnoDB Cluster 是 MySQL 官方提供的高可用性、容错性分布式数据库解决方案，基于 InnoDB 存储引擎和 MySQL Group Replication 技术构建。

MySQL InnoDB Cluster 主要由以下四个核心组件构成:

1. MySQL Server 实例：
   ○ 集群由多个 MySQL Server 组成，每个实例既可作为主节点也可作为从节点
   ○ 通过 Group Replication 进行数据同步
   ○ 支持单主(Single-Primary)和多主(Multi-Primary)两种模式
2. Group Replication：
   ○ 集群的核心组件，负责数据一致性
   ○ 使用 Paxos 协议协调节点间通信和数据复制
   ○ 提供自动故障转移功能，主节点宕机时自动选举新主节点
3. MySQL Router：
   ○ 集群的路由层，负责客户端请求的路由
   ○ 主要功能包括：
   ■ 负载均衡：将读请求分发到从节点，写请求分发到主节点
   ■ 故障转移：主节点故障时自动路由到新主节点
   ■ 透明性：客户端只需连接 Router，无需关心后端节点变化
4. MySQL Shell：
   ○ 集群管理工具，提供多种接口管理配置集群
   ○ 主要功能包括：
   ■ 集群创建、配置和监控
   ■ 查看节点状态、检查健康状态和复制延迟
   ■ 配置和故障排除命令

环境信息说明：

1 、安装 mysqlshell 和mysqlrouter

1.1 安装 mysql shell

cd /mysql/app/
tar -zxvf /soft/mysql-shell-8.0.38-linux-glibc2.17-x86-64bit.tar.gz -C /mysql/app/
ln -s mysql-shell-8.0.38-linux-glibc2.17-x86-64bit mysql-shell

环境变量

[mysql@mysqlshell-router bin]$ vim ~/.bash_profile

MYSQL_HOME=/mysql/app/mysql
PATH=$PATH:$HOME/.local/bin:$HOME/bin:$MYSQL_HOME/bin:/mysql/app/mysql-shell/bin

[mysql@mysqlshell-router bin]$ source ~/.bash_profile

1.2 安装 mysqlrouter

[mysql@mysqlshell-router app]$ tar -vxf  /soft/mysql-router-8.0.42-linux-glibc2.28-x86_64.tar.xz -C /mysql/app/
[mysql@mysqlshell-router app]$ ln -s mysql-router-8.0.42-linux-glibc2.28-x86_64 mysql-router

mkdir -p /mysql/log/mysqlrouter/
mkdir -p /mysql/app/mysqlrouter/etc/
环境变量
vim ~/.bash_profile
:/mysql/app/mysql-router/bin

source ~/.bash_profile
mysqlrouter --version
MySQL Router  Ver 8.0.39 for Linux on x86_64 (MySQL Community - GPL)

2 、利用 mysql Shell 构建MGR集群

利用 msyql Shell 构建MGR集群比较简单，主要有几个步骤：

1. 检查实例是否满足条件。
2. 创建并初始化一个集群。
3. 逐个添加实例。

三台 mysql 机器信息

首先，用管理员账号 data 连接到第一个节点：

mysqlsh --uri data@10.10.3.51:1622

执行 dba.configureInstance() 命令开始检查当前实例是否满足安装MGR集群的条件，如果不满足可以直接配置成为MGR集群的一个节点：

dba.checkInstanceConfiguration('data@10.10.3.51:1622')

创建集群

初始化新集群（在第一个节点上执行）

c = dba.createCluster('MGR1');

这就完成了MGR集群的初始化并加入第一个节点（引导节点）。

接下来，用同样方法先用 data 账号分别登入到另外两个节点

c.addInstance('data@10.10.3.52:1622');       --- 选择用Clone方式从第一个节点全量复制数据

 MySQL  10.10.3.52:1622 ssl  JS > c.addInstance('data@10.10.3.52:1622');

WARNING: A GTID set check of the MySQL instance at '10.10.3.52:1622' determined that it contains transactions that do not originate from the cluster, which must be discarded before it can join the cluster.

10.10.3.52:1622 has the following errant GTIDs that do not exist in the cluster:
943d6f4d-674b-11f0-ab31-00505628a153:1-3

WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of 10.10.3.52:1622 with a physical snapshot from an existing cluster 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
Validating instance configuration at 10.10.3.52:1622...

This instance reports its own address as 10.10.3.52:1622

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using '10.10.3.52:1622'. 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...

NOTE: User 'mysql_innodb_cluster_162252'@'%' already existed at instance '10.10.3.51:1622'. It will be deleted and created again with a new password.
Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
Clone based state recovery is now in progress.

NOTE: A server restart is expected to happen as part of the clone process. If the
server does not support the RESTART command or does not come back after a
while, you may need to manually start it back.

* Waiting for clone to finish...
NOTE: 10.10.3.52:1622 is being cloned from 10.10.3.51:1622
** Stage DROP DATA: Completed




** Clone Transfer      FILE COPY  ============================================================    0%  Not Started    PAGE COPY  ============================================================    0%  Not Started    REDO COPY  ============================================================    0%  Not Started** Clone Transfer      FILE COPY  ============================================================    0%  In Progress    PAGE COPY  ============================================================    0%  Not Started    REDO COPY  ============================================================    0%  Not Started** Clone Transfer      FILE COPY  ############################################################  100%  Completed    PAGE COPY  ############################################################  100%  Completed    REDO COPY  ############################################################  100%  Completed
NOTE: 10.10.3.52:1622 is shutting down...

* Waiting for server restart... ready
* 10.10.3.52:1622 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 76.93 MB transferred in about 1 second (~76.93 MB/s)

State recovery already finished for '10.10.3.52:1622'

The instance '10.10.3.52:1622' was successfully added to the cluster.

--- 新实例加入成功

用同样的方法，将第三个实例也加入到集群中。

c.addInstance('data@10.10.3.53:1622');

现在，一个有这三节点的MGR集群已经部署完毕，来确认下：

c.describe()

集群状态
c.status()

验证新集群状态
MySQL 10.10.3.51:1622 ssl SQL > SELECT * FROM performance_schema.replication_group_members;

3、配置mysqlrouter 构建读写分离集群

生成配置文件

mysqlrouter --bootstrap data@10.10.3.51:1622 --user=mysql

生成的配置文件

配置文件信息

[mysql@mysqlshell-router mysql-router]$ less mysqlrouter.conf
bind_port=6446
destinations=metadata-cache://MGR1/?role=PRIMARY
routing_strategy=first-available
protocol=classic

[routing:bootstrap_ro]
bind_address=0.0.0.0
bind_port=6447
destinations=metadata-cache://MGR1/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=classic

[routing:bootstrap_x_rw]
bind_address=0.0.0.0
bind_port=6448
destinations=metadata-cache://MGR1/?role=PRIMARY
routing_strategy=first-available
protocol=x

[routing:bootstrap_x_ro]
bind_address=0.0.0.0
bind_port=6449
destinations=metadata-cache://MGR1/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=x

[http_server]
port=8443
ssl=1
ssl_cert=/mysql/app/mysql-router-8.0.42-linux-glibc2.28-x86_64/var/lib/mysqlrouter/router-cert.pem
ssl_key=/mysql/app/mysql-router-8.0.42-linux-glibc2.28-x86_64/var/lib/mysqlrouter/router-key.pem

[http_auth_realm:default_auth_realm]
backend=default_auth_backend
method=basic
name=default_realm

[rest_router]
require_realm=default_auth_realm

[rest_api]

[http_auth_backend:default_auth_backend]
backend=metadata_cache

[rest_routing]
require_realm=default_auth_realm

[rest_metadata_cache]
require_realm=default_auth_realm

启动

/mysql/app/mysql-router/bin/mysqlrouter -c /mysql/app/mysql-router/mysqlrouter.conf &

ss -nltp |grep mysqlrouter 

mysqlrouter 起来之后 我们就连接

mysqlsh --uri data@10.10.3.54:6446

也可以查看集群状态

dba.getCluster("MGR1").status()

4、测试读写分离

查看几个节点信息

SELECT MEMBER_ID,MEMBER_ROLE FROM performance_schema.replication_group_members;

mysql -udata -p1ZvPJ5Ss11ZgmNFGt -h10.10.3.54 -P6446 -e "select @@hostname,@@server_uuid;"

可以看到 6446 端口指向的是 主库

mysql -udata -p1ZvPJ5Ss11ZgmNFGt -h10.10.3.54 -P6447 -e "select @@hostname,@@server_uuid;"

5、模拟故障

MySQL Router连接读写节点（Primary节点）默认的策略是 first-available，即只连接第一个可用的节点。Router连接只读节点（Secondary节点）默认的策略是 round-robin-with-fallback，会在各个只读节点间轮询。

目前 6446 端口只会连接第一个节点，现在让 node1 节点宕机看下

可以看到已经转移到node5节点
查看集群状态

dba.getCluster("MGR1").status()

重启 节点1，此时节点1 未加入 MGR中，我们来手动加入下

这里我们 使用 MySQL Shell 的 rejoinInstance() 方法加入
在主节点或任何在线节点上执行：

// 连接到集群
var cluster = dba.getCluster("MGR1");

// 重新加入离线节点
cluster.rejoinInstance('10.10.3.51:1622');

// 重新加入后，检查整个集群状态
dba.getCluster("MGR1").status();

sql界面 验证状态：