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的主要组件和特性：

MySQL Group Replication： MySQL Group Replication是MySQL 5.7版本引入的一个插件，它提供了基于多主复制的高可用性和灾难恢复方案。它允许多个MySQL实例协同工作，形成一个同步的集群。
MySQL Shell： MySQL Shell是用于管理和操作InnoDB Cluster的命令行工具。它提供了JavaScript、Python和SQL的接口，可以用于执行各种任务，如创建和管理集群、进行监控和诊断等。
MySQL Router： MySQL Router是InnoDB Cluster的负载均衡和路由组件。它负责将连接路由到正确的MySQL节点，提供了透明的故障切换和负载均衡。

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

Mysql Innodb Cluster	安装版本
Mysql Server	5.7.33
Mysql Shell	8.0.23
Mysql Router	8.0.23

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

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

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


# yum localinstall -y mysql-shell-8.0.23-1.el7.x86_64.rpm

# mysqlsh --version

mysqlsh   Ver 8.0.23 for Linux on x86_64 - for MySQL 8.0.23 (MySQL Community Server (GPL))

# 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


> select uuid();

+--------------------------------------+

| uuid() |

+--------------------------------------+

| 1a00d7eb-ec38-11ea-9d6d-fa163e28e526 |

+--------------------------------------+

1 row in set (0.00 sec)
- 三台机器除了group_replication_local_address不同，其他均一样。在my.cnf配置文件中增加：

########## 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;


- 登录每个节点创建innodb cluster管理员和复制账号（注意：set sql_log_bin=0）

set sql_log_bin=0 ;

grant all privileges on *.* to icadmin@'%' identified by 'xxx' with grant option;

flush privileges;
- 登录每个节点确认下plugins和group_members插件已安装

> show plugins ;

...

...

| group_replication                        | ACTIVE   | GROUP REPLICATION  | group_replication.so  | GPL     |

+------------------------------------------+----------+--------------------+-----------------------+---------+

48 rows in set (0.00 sec)

> 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


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 > \connect icadmin@db01:3306  				//连接到第一节点

MySQL db01:3306 ssl  JS > dba.checkInstanceConfiguration('icadmin@db01:3306')

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

MySQL db01:3306 ssl  JS > dba.checkInstanceConfiguration('icadmin@db03:3306')
- 打印出如下信息：

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

{ "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')


- 打印出如下信息：

...

Configuring MySQL instance at db03:3306 for use in an InnoDB cluster...
This instance reports its own address as db03:3306

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.

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'.


Validating instance configuration at db01:3306...
This instance reports its own address as db01:3306
Instance configuration is suitable.

NOTE: Group Replication will communicate with other members using 'db01:33061'. Use the localAddress option to override.
WARNING: Instance 'db01:3306' cannot persist Group Replication 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.

Creating InnoDB cluster 'Cluster' on 'db01:3306'...
Adding Seed Instance...

Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.

At least 3 instances are needed for the cluster to be able to withstand up to

one server failure.
- 查看集群状态，当前只有一个节点

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')


- 打印出如下信息：

......

Please select a recovery method [I]ncremental recovery/[A]bort (default Incremental recovery): = 8.0.11 required). Please use the dba.configureLocalInstance() command locally to persist the changes.

Adding instance to the cluster...
Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.

State recovery already finished for '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 'db02:3306' belongs to an InnoDB cluster.
Detecting the configuration file...

Found configuration file at standard location: /etc/my.cnf

Do you want to modify this file? [y/N]: y

Persisting the cluster settings...

The instance 'db02:3306' was configured for use in an InnoDB cluster.

The instance cluster settings were successfully persisted.

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

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

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


# # vi /etc/mysqlrouter/mysqlrouter.conf

.....

.....

[DEFAULT]

logging_folder = /data/mysqlrouter/log

runtime_folder = /data/mysqlrouter/run

config_folder = /etc/mysqlrouter
[logger]

level = INFO
...

...

[routing:Cluster_rw]

max_connections=5000                        // 增加max_connections值，默认为512
[routing:Cluster_ro]

max_connections=5000                        // 增加max_connections值

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'...


- 打印出如下信息：

......

# MySQL Router configured for the InnoDB Cluster 'Cluster'
After this MySQL Router has been started with the generated configuration
    $ mysqlrouter -c /data/mysqlrouter/myrouter/mysqlrouter.conf
the cluster 'Cluster' 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: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 ......


$ ps -ef|grep myroute

xxx       2285     1  2 14:09 pts/0    00:00:02 /usr/bin/mysqlrouter -c /data/mysqlrouter/myrouter/mysqlrouter.conf
[ops@db01 log]$ netstat -tunlp|grep 2285

(Not all processes could be identified, non-owned process info

 will not be shown, you would have to be root to see it all.)

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


- 修改/etc/keepalived/keepalived.conf文件。第一节点示例，其他节点除了unicast_src_ip和priority，其它均保持一致

# vi /etc/keepalived/keepalived.conf

! Configuration File for keepalived
global_defs {

   notification_email {

     mail@com.cn

   }

   notification_email_from mail@com.cn

   smtp_server smtp.com.cn

   smtp_connect_timeout 30

   router_id mysqlrouter

}
vrrp_script chk_mysqlrouter {

   script "/opt/scripts/checkMysqlrouter.sh"              // 检测脚本

   interval 2

   fall 2

   rise 1

}
vrrp_instance VI_1 {

   nopreempt                                             // MASTER要关掉抢占，高优先级的都要设置

   state BACKUP                                          // 所有节点都设置为BACKUP

   interface eth0

   virtual_router_id 51

   priority 100                                          // 优先级最高

   advert_int 1

   unicast_src_ip 物理IP1地址                   		 // 除了这里和优先级，其他节点都保持一致

   unicast_peer {

       物理IP2地址

       物理IP3地址

   }
   virtual_ipaddress {

       浮动IP地址

   }

track_script { chk_mysqlrouter } }

Step #2 编写mysql router check脚本

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


check_6446=`netstat -lnp  | grep 'mysqlrouter' | grep '6446' | grep -v grep | wc -l`

check_6447=`netstat -lnp  | grep 'mysqlrouter' | grep '6447' | grep -v grep | wc -l`

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

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


mysql> select @@port;

+--------+

| @@port |

+--------+

|   3306 |

+--------+

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)


MySQL db01:3306 ssl  JS > cluster.checkInstanceState("icadmin@db03:3306")   // 检查cluster里节点状态

Cluster.checkInstanceState: The instance 'db03:3306' already belongs to the cluster: 'Cluster'. (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

MySQL 5.7搭建Innodb Cluster高可用集群

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

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

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

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

五、配置Innodb Cluster集群

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

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

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

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

相关文章

发布评论取消回复

贤蛋大眼萌

Oracle如何使用授予和撤销权限的语法和示例

产品推荐：7月受欢迎AI容器镜像来了，有Qwen系列大模型镜像

i.MX8MP开发板AI项目实操

MySQL 5.7搭建Innodb Cluster高可用集群

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

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

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

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

五、配置Innodb Cluster集群

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

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

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

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

相关文章

发布评论 取消回复

贤蛋大眼萌

Oracle如何使用授予和撤销权限的语法和示例

产品推荐：7月受欢迎AI容器镜像来了，有Qwen系列大模型镜像

i.MX8MP开发板AI项目实操

发布评论取消回复