MySQL 5.7搭建Innodb Cluster高可用集群

一、MySQL Innodb Cluster介绍与安装版本

MySQL InnoDB Cluster是MySQL的一种高可用性和灾难恢复解决方案，为MySQL提供了自动化的高可用性解决方案。区别于MySQL原生的主从复制，InnoDB Cluster能够实现集群中的节点进行自动故障切换，提供了更高的高可用性。

InnoDB Cluster 支持两种不同的部署模式，即单主模式（Single Primary Mode）和多主模式（Multi-Primary Mode）。在单主模式下，集群中只有一个节点被指定为主节点（Primary），其余节点为从节点（Secondary）。在这种模式下所有写操作都必须通过主节点进行，从节点只用于读操作。当主节点不可用时，InnoDB Cluster 会自动选举新的主节点，确保集群的高可用性；在多主模式下，集群中的每个节点都可以同时作为主节点和从节点，即每个节点都可以执行读和写操作。在这种模式下写操作可以在任何节点上执行，而不仅仅是在主节点上，当节点不可用时，集群仍然可以继续进行写操作。

多主模式提供了更大的灵活性，允许应用在不同的节点上执行写操作，从而更好地利用系统资源。然而多主模式下，对于相同的数据在不同节点上的写操作可能会导致数据冲突，需要应用程序适当处理。建议在生产环境中，尽可能使用单主模式，本文主要是对Innodb Cluster的单主模式进行介绍。

InnoDB Cluster的主要组件和特性：

1. MySQL Group Replication： MySQL Group Replication是MySQL 5.7版本引入的一个插件，它提供了基于多主复制的高可用性和灾难恢复方案。它允许多个MySQL实例协同工作，形成一个同步的集群。

2. MySQL Shell： MySQL Shell是用于管理和操作InnoDB Cluster的命令行工具。它提供了JavaScript、Python和SQL的接口，可以用于执行各种任务，如创建和管理集群、进行监控和诊断等。

3. MySQL Router： MySQL Router是InnoDB Cluster的负载均衡和路由组件。它负责将连接路由到正确的MySQL节点，提供了透明的故障切换和负载均衡。

MySQL InnoDB Cluster 是建立在 MySQL Group Replication 基础之上的一种高可用性和灾难恢复解决方案。InnoDB Cluster 不仅使用了 Group Replication 插件，还包括了 MySQL Shell 和 MySQL Router 这两个组件，提供了更全面的功能，例如集群的创建、管理、监控以及负载均衡等。

二、三台服务器分别部署MySQL Server、MySQL Shell和MySQL Router

• 将官网对应的版本MySQL Shell和MySQL Router下载到本地

- 省略MySQL Server的具体部署步骤

# yum localinstall -y mysql-router-community-8.0.23-1.el7.x86_64.rpm
# mysqlrouter --version
MySQL Router Ver 8.0.23 for Linux on x86_64 (MySQL Community - GPL)


三、三台服务器分别修改MySQL配置文件

- 生成随机uuid

########## MGR #########
gtid_mode = on // 启用GTID模式，GTID 用于唯一标识每个事务
enforce_gtid_consistency = on
transaction_write_set_extraction = XXHASH64
slave_parallel_workers = 8 // 同逻辑CPU个数
slave_preserve_commit_order = 1 // 对于多线程slaves,来保障事务在slave上执行的顺序与relay log中的顺序严格一致
slave_parallel_type = LOGICAL_CLOCK
plugin_load_add = group_replication.so
group_replication_group_name = a95ff043-eda6-11ea-9015-fa163e28e526
group_replication_start_on_boot = ON // 服务器启动时自动启动 Group Replication
group_replication_local_address = "db-node01:33061"
group_replication_group_seeds = "db-node01:33061,db-node02:33061,db-node03:33061"
group_replication_single_primary_mode = ON //启用单主模式
group_replication_bootstrap_group = OFF // 配置是否在服务器启动时引导 Group Replication 组。在已经有成员的情况下设置为 OFF
disabled_storage_engines = MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY


四、三台服务器启动MySQL数据库

- 登录到每个节点，修改root密码，初始化系统用户（注意：set sql_log_bin=0）
set sql_log_bin=0 ; !### 否则会在mysql.gtid_executed表中产生数据
alter user 'root'@'localhost' identified by 'xxx' ;
flush privileges;

> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 07a06deb-ec32-11ea-8c44-fa163e28e526 | xxx | 3306 | OFFLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)


五、配置Innodb Cluster集群

Step #1: 在某一节点通过mysqlsh登录

# mysqlsh
MySQL Shell 8.0.23

{
"status": "ok"
}


Step #2: 配置实例

MySQL db01:3306 ssl JS > dba.configureInstance('icadmin@db01:3306')
MySQL db01:3306 ssl JS > dba.configureInstance('icadmin@db02:3306')
MySQL db01:3306 ssl JS > dba.configureInstance('icadmin@db03:3306')

The instance 'db03:3306' is valid to be used in an InnoDB cluster.
The instance 'db03:3306' is already ready to be used in an InnoDB cluster.


Step #3: 创建InnoDB Cluster集群

MySQL JS > \connect icadmin@db01:3306
MySQL db01:3306 ssl JS > dba.createCluster('Cluster') // 创建集群
A new InnoDB cluster will be created on instance 'db01:3306'.

MySQL db01:3306 ssl JS > var cluster = dba.getCluster()
MySQL db01:3306 ssl JS > cluster.status()
{
"clusterName": "Cluster",
"defaultReplicaSet": {
"name": "default",
"primary": "db01:3306",
"ssl": "REQUIRED",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures.",
"topology": {
"db01:3306": {
"address": "db01:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "5.7.33"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "db01:3306"
}


Step #4: 向集群中添加实例

MySQL JS > \connect icadmin@db01:3306
MySQL db01:3306 ssl JS > var cluster = dba.getCluster()
MySQL db01:3306 ssl JS > cluster.status()
MySQL db01:3306 ssl JS > cluster.addInstance('icadmin@db02:3306')
MySQL db01:3306 ssl JS > cluster.addInstance('icadmin@db03:3306')

WARNING: Instance 'db01:3306' cannot persist configuration since MySQL version 5.7.33 does not support the SET PERSIST command (MySQL version >= 8.0.11 required). Please use the dba.configureLocalInstance() command locally to persist the changes.
WARNING: Instance 'db02:3306' cannot persist configuration since MySQL version 5.7.33 does not support the SET PERSIST command (MySQL version >= 8.0.11 required). Please use the dba.configureLocalInstance() command locally to persist the changes.
The instance 'db03:3306' was successfully added to the cluster.


Step #5: 查看集群状态，三节点已全部添加完成

MySQL JS > \connect icadmin@db01:3306
MySQL db01:3306 ssl JS > var cluster = dba.getCluster()
MySQL db01:3306 ssl JS > cluster.status()
{
"clusterName": "Cluster",
"defaultReplicaSet": {
"name": "default",
"primary": "db01:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"db01:3306": {
"address": "db01:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "5.7.33"
},
"db02:3306": {
"address": "db02:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "5.7.33"
},
"db03:3306": {
"address": "db03:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "5.7.33"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "db01:3306"
}


Step #6: 因MySQL Server版本为5.7，需手工执行持久化，在每个节点单独执行（前面的操作在一个节点执行就可以）

MySQL db01:3306 ssl JS > dba.configureLocalInstance()
MySQL db02:3306 ssl JS > dba.configureLocalInstance()
MySQL db03:3306 ssl JS > dba.configureLocalInstance()

The instance cluster settings were successfully persisted.


五、三台服务器配置并启动Mysql Router

Step #1: 分别创建mysql router目录，并修改配置文件

# mkdir -p /data/mysqlrouter/
# chown -R 用户:组 /data/mysqlrouter

Step #2: 配置mysql router

$ cd /data/mysqlrouter
$ mysqlrouter --bootstrap icadmin@db01:3306 -d myrouter --user=启动用户
Please enter MySQL password for icadmin:
$ Bootstrapping MySQL Router instance at '/data/mysqlrouter/myrouter'...

- Read/Write Connections: localhost:64460
- Read/Only Connections: localhost:64470


Step #3: 启动mysql router

$ /data/mysqlrouter/myrouter/start.sh
$ PID 2285 written to '/data/mysqlrouter/myrouter/mysqlrouter.pid'
logging facility initialized, switching logging to loggers specified in configuration


Step #4: 查看mysql router日志与进程

$ tail -f mysqlrouter.log
xxxx-xx-xx 14:09:56 metadata_cache INFO [7f1ffc10c700] db01:3306 / 33060 - mode=RO
xxxx-xx-xx 14:09:56 metadata_cache INFO [7f1ffc10c700] db02:3306 / 33060 - mode=RW
xxxx-xx-xx 14:09:56 metadata_cache INFO [7f1ffc10c700] db03:3306 / 33060 - mode=RO
xxxx-xx-xx 14:09:56 routing INFO [7f1ffc10c700] Routing routing:Cluster_x_ro listening on 64470 got request to disconnect invalid connections: metadata change
xxxx-xx-xx 14:09:56 routing INFO [7f1ffc10c700] Routing routing:Cluster_x_rw listening on 64460 got request to disconnect invalid connections: metadata change
xxxx-xx-xx 14:09:56 routing INFO [7f1ffc10c700] Routing routing:Cluster_ro listening on 6447 got request to disconnect invalid connections: metadata change
xxxx-xx-xx 14:09:56 routing INFO [7f1ffc10c700] Routing routing:Cluster_rw listening on 6446 got request to disconnect invalid connections: metadata change
......

tcp 0 0 0.0.0.0:64460 0.0.0.0:* LISTEN 2285/mysqlrouter
tcp 0 0 0.0.0.0:6446 0.0.0.0:* LISTEN 2285/mysqlrouter
tcp 0 0 0.0.0.0:6447 0.0.0.0:* LISTEN 2285/mysqlrouter
tcp 0 0 0.0.0.0:64470 0.0.0.0:* LISTEN 2285/mysqlrouter


Step #5: 将mysql router加到Linux开机自启动

[root@db01 mysqlrouter]# vi /etc/rc.local
......
/bin/su - xxx -c "/data/mysqlrouter/myrouter/start.sh"


六、Mysql Router在Linux Keepalived中做高可用模式

Step #1 部署keepalived，两台机器分别修改keepalived.conf

# yum install keepalived
# systemctl start keepalived
# systemctl enable keepalived

track_script {
chk_mysqlrouter
}
}


Step #2 编写mysql router check脚本

# vi /opt/scripts/checkMysqlrouter.sh
#!/usr/bin/env bash

if [ $check_6446 -eq 2 -a $check_6447 -eq 2 ]; then
exit 0
else
exit 1
fi


六、以上已完成Innodb Cluster的部署，下面通过Mysql Router进行简单测试连接

# mysql -uuser -P6446 -h IP地址 -p // 读写端口
mysql> show variables like 'hostname' ; // db02为主节点
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| hostname | db02 |
+---------------+-----------+
1 row in set (0.01 sec)

# mysql -uuser -P6447 -h IP地址 -p //只读端口
msyql > show variables like 'hostname' ; // 随机分配到某一个从节点
+---------------+---------------+
| Variable_name | Value |
+---------------+---------------+
| hostname | db03 |
+---------------+---------------+
1 row in set (0.00 sec)


七、Mysqlsh常用监控和管理命令

# mysqlsh
MySQL JS > \connect icadmin@db01:3306 // 连接到对应的实例
MySQL db01:3306 ssl JS > \? // 查看帮助
MySQL db01:3306 ssl JS > \help cluster // 获取cluster的帮助
MySQL db01:3306 ssl JS > var cluster = dba.getCluster()
MySQL db01:3306 ssl JS > cluster.status() // 集群状态
MySQL db01:3306 ssl JS > cluster.describe() // 集群描述
MySQL db01:3306 ssl JS > cluster.listRouters() // 查看当前的mysql router
MySQL db01:3306 ssl JS > cluster.setPrimaryInstance("db02:3306") //提升从-->主（MySQL版本需为8.0以上）
Cluster.setPrimaryInstance: Operation not supported on target server version: '5.7.35' (RuntimeError)

- 集群节点状态：
- ONLINE: The instance is online and participating in the cluster.
- OFFLINE: The instance has lost connection to the other instances.
- RECOVERING: The instance is attempting to synchronize with the cluster by retrieving transactions it needs before it can become an ONLINE member.
- UNREACHABLE: The instance has lost communication with the cluster.
- ERROR: The instance has encountered an error during the recovery phase or while applying a transaction