目录

MySQL MGR容器化部署实战(Openshift4平台)实战

容器化+MySQL MGR+Openshift4.5+MySql Router实战。

MySQL MGR容器化部署实战(Openshift4平台)


1.前言

虽然目前云原生架构很火,很多开发者恨不得把所有软件都部署在容器内,但总得来说关键性应用的数据库不适合部署在容器中。

甚至从某种程度来说,特别关键的数据库甚至不适合部署在和其他业务存算一体资源类架构内,例如多个业务共享的超融合架构。

原因之一是多个共享资源的应用可能在I/O响应上,最终拖垮数据库的资源请求。

是不是说数据库肯定不能部署在容器内?显然不一定。k8s平台最大的特点是弹性,可自动伸缩、故障转移等。

我们可以将那些边缘业务的数据库(对性能不敏感)容器化部署,利用数据库分片来增加实例数,从而增加吞吐量。

容器化特别适合跑轻量级或分布式数据库,当POD挂掉后,会自动启动新POD,只要解决好客户端的故障转移以及存储持久化等问题,就能最大程度发挥容器化部署的特性,这也是本文探讨的问题。

最终的业务高可用利用单独的MySQL MGR技术并不能完美解决,需要利用InnoDB Cluster其中一些组件来搭建HA环境。

本文讨论利用Openshift4部署MySQL MGR集群,来实现在特定场景应用下的高可用环境。

明年还会增加 ShardingSphere的一些知识,利用ShardingSphere来替代MySQL Router。

因为能力问题,还有些遗憾,单独部署的MySQL Router无法通过sysbench测试,自编的golang程序调用Perfomance Schema表无法正常在OCP Grafana显示。自high的路上渐行渐远。。。。

1.1 MySQL传统高可用

MySQL 有一些传统高可用技术,广为使用的模式主要有三种:

  • 主备复制:在这种模式中主备实例的复制是异步复制,主实例可读写,备实例可读。从库起 IO 线程连接主库,获取主库二进制日志写到本地中继日志,并更新 master-info 文件(存放主库相关信息),从库再利用 SQL 线程执行中继日志。
  • 半同步复制在第一种主从复制的基础上、利用插件完成半同步复制。传统的主从复制,不管从库是否正确获取到二进制日志,主库不断更新。而半同步复制则当确认了从库把二进制日志写入中继日志才会允许提交,如果从库迟迟不返回 ack,主库会自动将半同步复制状态取消,进入最基本的主从复制模式。
  • 组复制 (MySQL Group Replication,简称 MGR)。MGR 是 MySQL 官方于 2016 年 12 月推出的一个全新的高可用与高扩展的解决方案。MGR 是 MySQL 官方在 5.7.17 版本引进的一个数据库高可用与高扩展的解决方案。MGR 在原生复制技术之上引入分布式强一致性协议 Paxos,以插件的方式提供。官方还基于 MGR 推出了 MySQL InnoDB Cluster 为 MySQL 提供了完整的高可用性解决方案。

传统的主从复制模式的发展历程

MySQL5.1.30支持Row-base异步复制。

MySQL5.5.8开始支持半同步复制。

MySQL5.6.10支持GTID和多线程回放。

MySQL5.7.9开始支持增强半同步复制,支持XA事务,支持多源复制。

MySQL5.7.17基于MGR单主/多主组同步复制。

https://typorabyethancheung911.oss-cn-shanghai.aliyuncs.com/typora/image-20201122200357858.png

**传统主从复制的最大问题在于同步的不是基于同步的

异步模式存在故障发生时刻,数据非一致性问题。MySQL的异步复制可以提供最佳的性能, 主库把 binlog 日志发送给从库,这一动作就结束了,并不会验证从库是否接收完毕。也就意味着有可能出现当主服务器或从服务器端发生故障的时候,有可能从服务器没有接收到主服务器发送过来的 binlog 日志,会造成主服务器和从服务器的数据不一致,甚至在恢复时造成数据的丢失。

在MySQL5.5.8开始支持半同步复制。该模式可以确保从服务器接收完主服务器发送的 binlog 日志文件并写入到自己的中继日志(relay log),然后会给主服务器一个反馈,告诉主服务器已经接收完毕,这时主服务线程才返回给当前 session 告知操作完成。

https://typorabyethancheung911.oss-cn-shanghai.aliyuncs.com/typora/image-20201122204721448.png

这种半同步存在的问题:

1、集群无法自动进行故障诊断并自动切换,运维复杂度高。

2、读库的压力不断增加后,会导致复制延迟增加。

MGR复制模式能够很好解决传统主从模式的问题。

PS:广大朋友,切勿在关键业务领域用keepalived加主从同步来做MySQL的集群部署。

1.2 MGR

MGR全称为MySQL Group Replication组复制,采用ROW格式日志文件+GTID的方式工作。

1、高一致性

基于原生复制及paxos协议的组复制手艺,并以插件的方式提供,提供一致数据平安保证;

2、高容错性

只要不是大多数节点坏掉就可以继续事情,有自动检测机制,当差别节点发生资源争用冲突时,不会泛起错误,根据先到者优先原则举行处置,而且内置了自动化脑裂防护机制;

3、高扩展性

节点的新增和移除都是自动的,新节点加入后,会自动从其他节点上同步状态,直到新节点和其他节点保持一致,若是某节点被移除了,其他节点自动更新组信息,自动维护新的组信息;

4、高灵活性

有单主模式和多主模式,单主模式下,会自动选主,所有更新操作都在主上举行;多主模式下,所有server都可以同时处置更新操作。

MGR是一种分布式集群,基于paxos协议,数据具备准数据同步性,而且满足share nothing特点。具备很好的弹性可扩展特性,最高支持9个节点,具备故障检测和自动切换功能。具备冲突检测认证机制,具备单主或者多主模式。

特别注意:MGR本身无法处理客户端访问的故障转移,需要通过其他中间件处理。后续将通过官方原生的MySQL Router来解决故障迁移问题

以下是MGR的基本工作流程

https://typorabyethancheung911.oss-cn-shanghai.aliyuncs.com/typora/image-20201122211501736.png

关键业务的数据库建议还是采用数据库一体机、单独的超融合集群、实体机单独部署等方式。

本文只是对在容器化部署条件下试验。

1、首先需要解决存储持久化问题,pod重启后,数据不能发生丢失。

2、因为pod ip地址无法固定,需要解决MGR集群内各节点互相通讯问题,需要通过固定的域名实现互相通信。

3、MGR各节点的配置需要实现提前配置,避免需要进入pod内进行配置情况。

遇到的大坑的解决办法

1、白名单:因为在k8s、openshift平台下,容器化部署的mysql数据库存在跨网段访问,需要通过mysql mgr的白名单来解决。

2、mysql8.0之后加密规则变成 caching_sha2_password了,造成复制通道无法正常通讯,需要开启公钥访问功能。

2.前期准备

2.1 基本环境

基本环境

系统 版本号
底层超融合架构Vmware vSphere+Vsan 7.0.0 内部版本号:16749670
全栈PasS平台:Openshift 4.5.14
数据库平台:MySQL 8.0.22

容器部署相关环境

pod节点 角色 域名 控制器类型 服务类型
Pod1 Primary mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local /var/lib/mysql/node1 Headless
Pod2 Secondaries mysql-mgr-node2-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local /var/lib/mysql/node2 Headless
Pod3 Secondaries mysql-mgr-node3-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local /var/lib/mysql/node3 Headless

2.2 准备镜像

下载官方mysql镜像,这里采用版本号8.0.22

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
[root@support mysql-mgr]$docker pull mysql:8.0.22
Trying to pull repository docker.io/library/mysql ...
8.0.22: Pulling from docker.io/library/mysql
bb79b6b2107f: Pull complete
49e22f6fb9f7: Pull complete
842b1255668c: Pull complete
9f48d1f43000: Pull complete
c693f0615bce: Pull complete
8a621b9dbed2: Pull complete
0807d32aef13: Pull complete
a56aca0feb17: Pull complete
de9d45fd0f07: Pull complete
1d68a49161cc: Pull complete
d16d318b774e: Pull complete
49e112c55976: Pull complete
Digest: sha256:8c17271df53ee3b843d6e16d46cff13f22c9c04d6982eb15a9a47bd5c9ac7e2d
Status: Downloaded newer image for docker.io/mysql:8.0.22

推送到企业内部镜像仓库。

PS:后续将有专门文章讲解高可用内部镜像仓库quay的部署实战。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
[root@support mysqlm-mgr-pri]$docker image ls | grep mysql
docker.io/mysql             8.0.22        db2b37ec6181        3 weeks ago         545 MB

# db2b37ec6181是镜像ID,推送前先打上标记
[root@support mysqlm-mgr-pri]$docker tag db2b37ec6181 registry.cj.io:5000/mysql:8.0.22

# 向内部私有镜像仓库推送
[root@support mysqlm-mgr-pri]$docker push registry.cj.io:5000/mysql:8.0.22
The push refers to a repository [registry.cj.io:5000/mysql]
9b0377a95c0e: Pushed
af6e790b8237: Pushed
060fef62a228: Pushed
7f893b7c04ac: Pushed
7832ac00d41e: Pushed
15b463db445c: Pushed
c21e35e55228: Pushed
36b89ee4c647: Pushed
9dae2565e824: Pushed
ec8c80284c72: Pushed
329fe06a30f0: Pushed
d0fe97fa8b8c: Pushed
8.0.22: digest: sha256:c7788fdc4c04a64bf02de3541656669b05884146cb3995aa64fa4111932bec0f size: 2828

3.持久化存储

数据库容器化部署,优先解决持久化存储问题。

这里采用最简单的NFS存储解决方案,当然也可以采用Redhat OCS或者Portworx等云原生存储解决方案。

PS:后续将有专门文章讲解Ceph的部署实战。

3.1 配置NFS

1
echo /data/nfs/mysqlm-mgr-pri/ *'(rw,sync,no_wdelay,root_squash,insecure)' > /etc/exports.d/mysql-mgr.exports

【PS:谨慎使用fsid=0参数】

设置权限

1
2
chown -R nfsnobody.nfsnobody /data/nfs/mysqlm-mgr-pri/
chmod 777 -R /data/nfs/mysqlm-mgr-pri/

设置生效

1
2
[root@support mysql-mgr]$exportfs -rav
exporting *:/data/nfs/mysqlm-mgr-pri/

3.2 创建 PV

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
apiVersion: v1
kind: PersistentVolume
metadata:
  name: pv-mysqlmgr
spec:
  capacity:
    storage: 100Gi
  accessModes:
    - ReadWriteMany
  persistentVolumeReclaimPolicy: Retain
  nfs:
    path: /data/nfs/mysqlm-mgr-pri/
    server: nfs.cj.io
    readOnly: false

创建pv

1
2
[root@support mysql-mgr]$oc create -f pv.yaml
persistentvolume/pv-mysqlmgr created

【验证】

1
2
3
4
# 状态已经是Available
[root@support mysql-mgr]$oc get pv
NAME        CAPACITY   ACCESS MODES   RECLAIM POLICY   STATUS       CLAIM          STORAGECLASS    REASON   AGE
pv-mysqlmgr  100Gi      RWX            Retain           Available                                             2s

3.3 创建pvc

pvc需要创建在制定namespace下

【创建pvc,务必要注意namespace,跨namespace是无法绑定的】

1
2
# 务必到mysql-mgr这个project下,名字可自定义
oc project mysql-mgr

设置pvc

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: pvc-mysqlmgr
spec:
  accessModes:
  - ReadWriteMany
  resources:
    requests:
      storage: 100Gi

创建pvc

1
2
[root@support mysql-mgr]$oc create -f pvc.yaml 
persistentvolumeclaim/pvc-mysqlmgr created

【验证】

1
2
3
4
5
6
7
8
# pvc
[root@support mysql-mgr]$oc get pvc
NAME           STATUS   VOLUME        CAPACITY   ACCESS MODES   STORAGECLASS   AGE
pvc-mysqlmgr   Bound    pv-mysqlmgr   100Gi      RWX                           4s

# pv,状态从Available变为Bound
[root@support mysql-mgr]$oc get pv | grep pv-mysqlmgr
pv-mysqlmgr                                100Gi      RWX            Retain           Bound    mysql-mgr/pvc-mysqlmgr

4.创建configmap

创建configmap用于mysql mgr集群内mysql的配置文件。

编辑mysqlconfigmap.yaml

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
apiVersion: v1
data:
  node1.cnf: |
    [mysqld]

    #Disabling symbolic-links is recommended to prevent assorted security risks

    symbolic-links = 0
    skip_name_resolve


    datadir=/var/lib/mysql/node1
    log-error=/var/lib/mysql/node1/logerror
    secure_file_priv=/var/lib/mysql

    server_id=20201101
    gtid_mode=ON
    enforce_gtid_consistency=ON
    binlog_checksum=NONE
    #log_bin=binlog
    #log_slave_updates=ON
    #binlog_format=ROW
    #master_info_repository=TABLE
    #relay_log_info_repository=TABLE
    relay-log=mysql-mgr-node1-0-relay-bin

    transaction_write_set_extraction=XXHASH64
    
    
    loose-group_replication_recovery_get_public_key=ON
    #loose-group_replication_recovery_use_ssl=ON
    loose-group_replication_group_name="deabf3a9-66d3-4cc3-acb2-66ebd7800a7c"
    loose-group_replication_start_on_boot=OFF
    loose-group_replication_local_address="mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:33061"
    loose-group_replication_group_seeds="mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:33061,mysql-mgr-node2-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:33061,mysql-mgr-node3-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:33061"
    loose-group_replication_bootstrap_group=OFF
    loose-group_replication_ip_whitelist="10.128.0.0/14"
    report_host=mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local
    report_port=3306

    #!includedir /etc/my.cnf.d    



  node2.cnf: |
    [mysqld]

    #Disabling symbolic-links is recommended to prevent assorted security risks

    symbolic-links = 0
    skip_name_resolve


    datadir=/var/lib/mysql/node2
    log-error=/var/lib/mysql/node2/logerror
    secure_file_priv=/var/lib/mysql

    server_id=20201102
    gtid_mode=ON
    enforce_gtid_consistency=ON
    binlog_checksum=NONE
    #log_bin=binlog
    #log_slave_updates=ON
    #binlog_format=ROW
    #master_info_repository=TABLE
    #relay_log_info_repository=TABLE
    relay-log=mysql-mgr-node2-0-relay-bin
    
    transaction_write_set_extraction=XXHASH64
    
    loose-group_replication_recovery_get_public_key=ON
    #loose-group_replication_recovery_use_ssl=ON
    loose-group_replication_group_name="deabf3a9-66d3-4cc3-acb2-66ebd7800a7c"
    loose-group_replication_start_on_boot=OFF
    loose-group_replication_local_address="mysql-mgr-node2-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:33061"
    loose-group_replication_group_seeds="mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:33061,mysql-mgr-node2-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:33061,mysql-mgr-node3-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:33061"
    loose-group_replication_ip_whitelist="10.128.0.0/14"
    loose-group_replication_bootstrap_group=OFF
    report_host=mysql-mgr-node2-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local
    report_port=3306


    #!includedir /etc/my.cnf.d    


  node3.cnf: |
    [mysqld]

    #Disabling symbolic-links is recommended to prevent assorted security risks

    symbolic-links = 0
    skip_name_resolve


    datadir=/var/lib/mysql/node3
    log-error=/var/lib/mysql/node3/logerror
    secure_file_priv=/var/lib/mysql

    server_id=20201103
    gtid_mode=ON
    enforce_gtid_consistency=ON
    binlog_checksum=NONE
    #log_bin=binlog
    #log_slave_updates=ON
    #binlog_format=ROW
    #master_info_repository=TABLE
    #relay_log_info_repository=TABLE
    relay-log=mysql-mgr-node3-0-relay-bi
    
    transaction_write_set_extraction=XXHASH64
    
    loose-group_replication_recovery_get_public_key=ON
    #loose-group_replication_recovery_use_ssl=ON
    loose-group_replication_group_name="deabf3a9-66d3-4cc3-acb2-66ebd7800a7c"
    loose-group_replication_start_on_boot=OFF
    loose-group_replication_local_address="mysql-mgr-node3-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:33061"
    loose-group_replication_group_seeds="mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:33061,mysql-mgr-node2-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:33061,mysql-mgr-node3-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:33061"
    loose-group_replication_ip_whitelist="10.128.0.0/14"
    loose-group_replication_bootstrap_group=OFF
    report_host=mysql-mgr-node3-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local
    report_port=3306


    #!includedir /etc/my.cnf.d    


kind: ConfigMap
metadata:
  labels:
    app: mysqlconfigmap
  name: mysqlconfigmap

创建configmap

1
[root@support mysql-mgr]$oc create -f mysqlconfigmap.yaml

4.1 MGR基本结构要求和使用限制

1、引擎必须为innodb,由于需事务支持在commit时对各节点举行冲突检查

2、每个表必须有主键,在举行事务冲突检测时需要行使主键值对比

3、必须开启binlog且为row方式

4、必须打开GTID特征,且主从状态信息存于表中 (–master-info-repository=TABLE 、–relay-log-info-repository=TABLE)

5、–log-slave-updates打开

6、一致性检测设置–transaction-write-set-extraction=XXHASH64

7、RP和通俗复制binlog校验不能共存,需设置–binlog-checksum=none

8、不支持gap lock(间隙锁),隔离级别需设置为read_committed

9、不支持对表举行锁操作(lock /unlock table),不会发送到其他节点执行 ,影响需要对表举行加锁操作的情形,列入mysqldump全表备份恢复操作

10、不支持serializable(序列化)隔离级别

11、DDL语句不支持原子性,不能检测冲突,执行后需自行校验是否一致

12、不支持外键:多主不支持,单主模式不存在此问题

13、最多支持9个节点:跨越9台server无法加入组

4.2 MySQL MGR相关配置解释

1、data下的“node1.cnf”、“node2.cnf”、“node3.cnf”通过configmap特性,在每个StatefulSet映射到每个pod内容器的/et/mysql/my.cnf文件。

2、datadir是mysql的数据目录,在每个StatefulSet的yaml文件内,映射到nfs的不同的数据目录下,完成数据持久化。

3、log-erro是明确log日志位置,对于安装部署以及后期运维非常有帮助

4、gtid_mode=ON##开启全局事务,必须设置

5、enforce_gtid_consistency=ON##强制GTID的一致性,必须设置

6、binlog_checksum=NONE##禁用二进制日志事宜校验,8.0.20版本需要设置该参数

7、log_bin=binlog ##开启二进制日志纪录,8.0.3之前版本需要设置该参数

8、log_slave_updates=ON ##级联复制,组中的每个节点都保留了完整的数据副本,它是share-nothing的模式。所以所有节点上都必须开启log_slave_updates,这样新节点随便选哪个作为donor都可以进行异步复制。8.0.3之前的版本需要设置该参数

9、binlog_format=ROW ##以行的花样纪录,8.0.3之前的版本需要设置该参数

10、master_info_repository=TABLE ##将master.info元数据保存在系统表中8.0.3之前的版本需要设置该参数

11、relay_log_info_repository=TABLE ##将master.info元数据保存在系统表中8.0.3之前的版本需要设置该参数

8.0.3的MySQL版本,其中默认值已针对复制进行了改进,如果是8.0.3之前的MySQL版本则需要将这些行添加到成员的配置文件中

log_bin=binlog

log_slave_updates=ON

binlog_format=ROW

master_info_repository=TABLE

relay_log_info_repository=TABLE

12、group_replication_bootstrap_group=off ##此参数不能在参数文件里设置成on,此设置表示自动引导组,此设置不要与loose-group_replication_start_on_boot混淆。集群内其他实例都不需要开启这个参数,默认为off,只有第一次初始化集群或者整个集群重启时需要再set global来开启。 如果将其保存在配置文件中,则在重新启动时,server会自动引导具有相同名称的第二个组。这将导致两个不同的组具有相同的名称。

通常是首次引导组时(或在整个组被崩溃然后恢复的情况下)。如果您多次引导组,例如,当多个server实例设置了此选项,则它们可能会人为地造成脑裂的情况,其中存在两个具有相同名称的不同组。在第一个server实例加入组后禁用此选项。

13、group_replication_start_on_boot=off ##Mysql服务起来以后,引擎不会自动起来。如果所有group成员配置完成,可以设置成on

14、transaction_write_set_extraction## 定义用于事务期间哈希写入提取的算法,组复制模式下必须设置为 XXHASH64。所谓写集,是对事务中所修改的行进行的唯一标识,在后续检测并发事务之间是否修改同一行冲突时使用。它基于主键生成,所以使用组复制,表中必须要有主键,从MySQL 8.0.2开始,此设置是默认设置。

15、loose 前缀的意义在于第一次启动时还没加载组复制的plugin,可以让mysql server忽略该参数,继续启动。 [注意这里loose后是短折号]

16、group_replication_group_name ##表示MGR复制组的名字。可以手工指定,但是必须符合uuid的格式。在Linux下,可以使用uuidgen工具来生成UUID值

17、loose-group_replication_local_address [host|ip]:port##其中port是本节点MGR的通讯端口,不是数据库的端口。

18、loose-group_replication_group_seeds ##需要接受本MGR实例控制的实例 [host|ip]:port,其中port是MGR的端口,不是数据库的端口 。建立连接后,组成员身份信息在 performance_schema.replication_group_members表中

19、loose-group_replication_recovery_use_ssl=ON##确定组复制恢复时是否应该应用 SSL,通常设置为“开”,但默认设置为“关”。

20、loose-group_replication_recovery_get_public_key=ON:这个很重要,mysql8.0之后加密规则变成 caching_sha2_password了,造成复制通道无法正常通讯,需要开启此功能。

21、report_host#根据主机名进行MGR通讯,否则报DNS错误。【如果是多主,相较单主多出两个设置】

22、group_replication_single_primary_mode 决定是否为单主模式(ON)还是多主模式(OFF) ,单主模式要求过半数节点写入成功之后,主库才返回成功。多主模式可以提高性能,但是事务冲突的概率也更高。

23、loose-group_replication_enforce_update_everywhere_checks多主模式下,强制检查每一个实例是否允许写操作,如果是单主模式,可以设置为off,多主为ON。

5.创建Headless Service

创建Headless Service,用于mysql mgr集群内各节点互相通过固定的域名互相通信。

【核心点:是将MGR的节点的网络拓扑状态固定】

编辑mysqlsvc.yaml

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
kind: Service
apiVersion: v1
metadata:
  name: mysqlmgr-svc
  namespace: mysql-mgr
  labels:
    app: mysql-mgr
spec:
  ports:
    - name: mysql
      protocol: TCP
      port: 3306
      targetPort: 3306
    - name: mysql-mgr
      protocol: TCP
      port: 33061
      targetPort: 33061
  selector:
    app: mysql8
  type: ClusterIP
  clusterIP: None

【注意上述YAML文件中 clusterIP: None】

创建Headless Service

1
oc create -f mysqlsvc.yaml

6.创建StatefulSet

通过StatefulSet配合Headless Service,用于mysql mgr集群内各节点互相通过固定的域名互相通信。

【初始密码是name: MYSQL_ROOT_PASSWORD value: Pass】,这个可以根据要求自定义。

【编辑mysqlnode1statefulset.yaml】

【注意,这里app: mysql8 是Headless Service与pod匹配的关键字】

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mysql-mgr-node1
  namespace: mysql-mgr
spec:
  serviceName: mysqlmgr-svc
  replicas: 1
  selector:
    matchLabels:
      app: mysql8
  template:
    metadata:
      labels:
        app: mysql8
    spec:
      hostname: mysql-mgr1
      subdomain: default-subdomain
      containers:
        - name: mysql8
          image: 'registry.cj.io:5000/mysql:8.0.22'
          ports:
          - containerPort: 3306
            name: mysql-port
          env:
          - name: MYSQL_ROOT_PASSWORD
            value: Pass
          volumeMounts:
          - name: mysqlmgrstorage
            mountPath: "/var/lib/mysql"
          - name: mysqlconfigmap
            mountPath: "/etc/mysql/my.cnf"
            subPath: node1.cnf
      volumes:
      - name: mysqlmgrstorage
        persistentVolumeClaim:
          claimName: pvc-mysqlmgr
      - name: mysqlconfigmap
        configMap:
          name: mysqlconfigmap

【编辑mysqlnode2statefulset.yaml】

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mysql-mgr-node2
  namespace: mysql-mgr
spec:
  serviceName: mysqlmgr-svc
  replicas: 1
  selector:
    matchLabels:
      app: mysql8
  template:
    metadata:
      labels:
        app: mysql8
    spec:
      hostname: mysql-mgr1
      subdomain: default-subdomain
      containers:
        - name: mysql8
          image: 'registry.cj.io:5000/mysql:8.0.22'
          ports:
          - containerPort: 3306
            name: mysql-port
          env:
          - name: MYSQL_ROOT_PASSWORD
            value: Pass
          volumeMounts:
          - name: mysqlmgrstorage
            mountPath: "/var/lib/mysql"
          - name: mysqlconfigmap
            mountPath: "/etc/mysql/my.cnf"
            subPath: node2.cnf
      volumes:
      - name: mysqlmgrstorage
        persistentVolumeClaim:
          claimName: pvc-mysqlmgr
      - name: mysqlconfigmap
        configMap:
          name: mysqlconfigmap

【编辑mysqlnode3statefulset.yaml】

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mysql-mgr-node3
  namespace: mysql-mgr
spec:
  serviceName: mysqlmgr-svc
  replicas: 1
  selector:
    matchLabels:
      app: mysql8
  template:
    metadata:
      labels:
        app: mysql8
    spec:
      hostname: mysql-mgr1
      subdomain: default-subdomain
      containers:
        - name: mysql8
          image: 'registry.cj.io:5000/mysql:8.0.22'
          ports:
          - containerPort: 3306
            name: mysql-port
          env:
          - name: MYSQL_ROOT_PASSWORD
            value: Pass
          volumeMounts:
          - name: mysqlmgrstorage
            mountPath: "/var/lib/mysql"
          - name: mysqlconfigmap
            mountPath: "/etc/mysql/my.cnf"
            subPath: node3.cnf
      volumes:
      - name: mysqlmgrstorage
        persistentVolumeClaim:
          claimName: pvc-mysqlmgr
      - name: mysqlconfigmap
        configMap:
          name: mysqlconfigmap

创建StatefulSet

1
2
3
4
5
6
[root@support mysql-mgr]$oc create -f mysqlnode1statefulset.yaml 
statefulset.apps/mysql-mgr-node1 created
[root@support mysql-mgr]$oc create -f mysqlnode2statefulset.yaml 
statefulset.apps/mysql-mgr-node2 created
[root@support mysql-mgr]$oc create -f mysqlnode3statefulset.yaml 
statefulset.apps/mysql-mgr-node3 created

【注意:这里subPath的首字母大小写。】

【如果发生如下错误】

1
2
3
4
5
6
[root@support mysql-mgr]$oc logs mysql-mgr-pri-0
2020-11-15 12:34:18+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.22-1debian10 started.
mysqld: Error on realpath() on '/var/lib/mysql-files' (Error 2 - No such file or directory)
2020-11-15T12:34:18.536067Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and willbe removed in a future release.
2020-11-15T12:34:18.536197Z 0 [ERROR] [MY-010095] [Server] Failed to access directory for --secure-file-priv. Please make sure that directory exists and is accessible by MySQL Server. Supplied value : /var/lib/mysql-files
2020-11-15T12:34:18.536250Z 0 [ERROR] [MY-010119] [Server] Aborting

那是因为MYSQL新特性secure_file_priv对读写文件的影响。

【解决】

/etc/mysql/my.cnf配置文件添加

secure_file_priv=/var/lib/mysql

上述操作已添加到configmap文件内

7.网络互通验证

验证各node节点互相通讯,并且mysql服务能够正常启动。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
[root@support mysql-mgr]$oc get pod
NAME                READY   STATUS    RESTARTS   AGE
busybox-0           1/1     Running   9          9h
mysql-mgr-node1-0   1/1     Running   0          4m7s
mysql-mgr-node2-0   1/1     Running   0          3m59s
mysql-mgr-node3-0   1/1     Running   0          3m53s

# 远程登录node1,访问node2和node3
[root@support mysql-mgr]$oc rsh mysql-mgr-node1-0

# mysql可以远程登录,初始密码在configmap中
$ mysql -h mysql-mgr-node2-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.22 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> 

继续登录其他节点,访问其他节点mysql验证。此处略去。

8.MGR详细配置

MGR的基本配置流程包括

第一步:安装组复制插件

第二步:创建复制用户(Replication user)的账户

第三步:启动组复制功能

8.1 Node1节点

1
2
[root@support mysql-mgr]$oc rsh mysql-mgr-node1-0
$ mysql -uroot -p

【检查插件目录】

1
2
3
4
5
6
7
mysql> SHOW VARIABLES LIKE 'plugin_dir';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| plugin_dir    | /usr/lib/mysql/plugin/ |
+---------------+------------------------+
1 row in set (0.01 sec)

【安装插件】

1
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';

【验证开启插件】

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
mysql> SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME = 'group_replication' \G
*************************** 1. row ***************************
           PLUGIN_NAME: group_replication
        PLUGIN_VERSION: 1.1
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: GROUP REPLICATION
   PLUGIN_TYPE_VERSION: 1.4
        PLUGIN_LIBRARY: group_replication.so
PLUGIN_LIBRARY_VERSION: 1.10
         PLUGIN_AUTHOR: Oracle Corporation
    PLUGIN_DESCRIPTION: Group Replication (1.1.0)
        PLUGIN_LICENSE: GPL
           LOAD_OPTION: ON
1 row in set (0.00 sec)

也可以通过mysql> show plugins;去查看所有插件。

创建账户,我们告诉服务器连接到名为 group_replication_recovery 的特殊复制通道。

1
2
3
4
5
6
7
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'rpl_pass';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER="rpl_user", MASTER_PASSWORD="rpl_pass" FOR CHANNEL 'group_replication_recovery';

SET SQL_LOG_BIN=0;SET SQL_LOG_BIN=1;表示暂停和开始binlog日志记录。此操作不应记录到二进制日志中,以避免将更改传递到其他server实例。禁用了二进制日志记录,则在创建用户后再次启用二进制日志。

执行CHANGE MASTER TO命令,都会返回如下信息

1
[System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.

【验证】

注意在主机名中使用了 %。这样做是为了确保复制用户可以从任何服务器进行连接。对于生产环境,通常不会这样做,这里只是为了方便。

注意这里的caching_sha2_password的问题。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
mysql> SELECT Host, User, plugin from mysql.user;
+-----------+------------------+-----------------------+
| Host      | User             | plugin                |
+-----------+------------------+-----------------------+
| %         | root             | caching_sha2_password |
| %         | rpl_user         | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
| localhost | root             | caching_sha2_password |
+-----------+------------------+-----------------------+
6 rows in set (0.00 sec)

启动组复制,设置group_replication_bootstrap_group为ON是为了标示以后加入集群的服务器以这台服务器为基准,以后加入的就不需要设置作为首个节点启动MGR集群

1
2
3
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION USER='rpl_user', PASSWORD='rpl_pass';
SET GLOBAL group_replication_bootstrap_group=OFF;

注意这里选择了使用START GROUP_REPLICATION USER='rpl_user', PASSWORD='rpl_pass';启动组复制。

MySQL 8.0.21以后版本 START GROUP_REPLICATION改为使用START GROUP_REPLICATION USER='rpl_user', PASSWORD='rpl_pass';

1
2
3
4
# MySQL 8.0.21以前版本
START GROUP_REPLICATION;
# MySQL 8.0.21以后版本
START GROUP_REPLICATION USER='rpl_user', PASSWORD='rpl_pass';

如果选择START GROUP_REPLICATION启动,会提示

1
2020-11-22T02:31:03.734451Z 18 [Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.

记录返回,显示作为primary节点node1的相关信息

视图发生变化。

1
2
3
4
5
6
[System] [MY-013471] [Repl] Plugin group_replication reported: 'Distributed recovery will transfer data using: Incremental recovery from a group donor'
[System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306 on view 16060109028252781:1.'
[System] [MY-011490] [Repl] Plugin group_replication reported: 'This server was declared online within the replication group.'
[System] [MY-011507] [Repl] Plugin group_replication reported: 'A new primary with address mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306 was elected. The new primary will execute all previous group transactions before allowing writes.'
[System] [MY-011566] [Repl] Plugin group_replication reported: 'Setting super_read_only=OFF.'
[System] [MY-011510] [Repl] Plugin group_replication reported: 'This server is working as primary member.'

【验证】

1
2
3
4
5
6
7
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST       | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+----------------------+-------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 967d909e-2b31-11eb-824d-0a580a8300dd | mysql-mgr-node1-0 |        3306 | ONLINE       | PRIMARY     | 8.0.22         |
+---------------------------+---------- -----------+-------------------+-------------+--------------+-------------+----------------+
1 row in set (0.01 sec)

8.2 Node2节点

登录Node2节点,执行命令。

注意:首次启动组复制,在START GROUP_REPLICATION命令之前启用reset master;

MySQL是新装的没问题,但是每次新装MySQL都要修改密码,本文的容器在初始化中,根据StatefulSet的yaml文件中env环境变量的设置,密码已经修改了。那么修改密码的记录是存在在binlog日志中的。详见《TroubleShooting》章节。

reset master功能说明:删除所有的binglog日志文件,并将日志索引文件清空,重新开始所有新的日志文件。用于第一次进行搭建主从库时,进行主库binlog初始化工作;reset master 不能用于有任何slave 正在运行的主从关系的主库。但是如果当前是主数据库,且主从数据库正常的时候,千万不能用这种方式删除。

reset slave功能说明:用于删除SLAVE数据库的relaylog日志文件,并重新启用新的relaylog文件;

reset slave 将使slave 忘记主从复制关系的位置信息。该语句将被用于干净的启动, 它删除master.info文件和relay-log.info 文件以及所有的relay log 文件并重新启用一个新的relaylog文件。

使用reset slave之前必须使用stop slave 命令将复制进程停止。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
[root@support mysql-mgr]$oc rsh mysql-mgr-node2-0
$ mysql -uroot -p

# 安装插件
$ mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';

# 安装复制组账户
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'rpl_pass';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER="rpl_user", MASTER_PASSWORD="rpl_pass" FOR CHANNEL 'group_replication_recovery';


# 启动组复制
reset master;
START GROUP_REPLICATION USER='rpl_user', PASSWORD='rpl_pass';

这里的步骤与node1不同,就是不执行SET GLOBAL group_replication_bootstrap_group = ON的操作; 在启动组复制之前,因为该组已由node1创建和引导。此时,node2和后续的node3只需要添加到已经存在的组中。

这里启动node1和node2节点后,由于白名单正常工作。

Node1返回,视图进行了切换。相关视图详见8.4章节。

1
[System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306, mysql-mgr-node2-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306 on view 16060109028252781:2.'

Node2返回,此项错误信息是mysql8.0之后加密规则变成 caching_sha2_password了,造成复制通道无法正常通讯。

1
2
3
4
[Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
[ERROR] [MY-010584] [Repl] Slave I/O for channel 'group_replication_recovery': error connecting to master 'rpl_user@mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061
[ERROR] [MY-011582] [Repl] Plugin group_replication reported: 'There was an error when connecting to the donor server. Please check that group_replication_recovery channel credentials and all MEMBER_HOST column values of performance_schema.replication_group_members table are correct and DNS resolvable.'
[ERROR] [MY-011583] [Repl] Plugin group_replication reported: 'For details please check performance_schema.replication_connection_status table and error log messages of Slave I/O for channel group_replication_recovery.'

【解决办法】

1
loose-group_replication_recovery_get_public_key=ON

问题解决后

Node2返回信息正常,总归四条记录。

其中“connected to master ‘rpl_user@mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306’,replication started in log ‘FIRST’ at position 4”显示已成功连接。

其中 “This server was declared online within the replication group.”已成功加入集群。

1
2
3
4
[Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
[System] [MY-010562] [Repl] Slave I/O thread for channel 'group_replication_recovery': connected to master 'rpl_user@mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306',replication started in log 'FIRST' at position 4
[System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.
[System] [MY-011490] [Repl] Plugin group_replication reported: 'This server was declared online within the replication group.'

Node1返回信息正常,较之前多了第二行

1
2
[System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to mysql-mgr-node2-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306, mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306 on view 16060121683078752:2.'
[System] [MY-011492] [Repl] Plugin group_replication reported: 'The member with address mysql-mgr-node2-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306 was declared online within the replication group.'

【验证】

无论登录哪个节点

1
2
3
4
5
6
7
mysql> select * from performance_schema.replication_group_members;
+----------------+--------------------+-------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME       | MEMBER_ID  | MEMBER_HOST      | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+----------------+--------------------+-------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 5aec08fe-2c6a-11eb-801c-0a580a80037b | mysql-mgr-node2-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local |        3306 | ONLINE       | SECONDARY   | 8.0.22         |
| group_replication_applier | 5bc8f7c3-2c6a-11eb-9061-0a580a8300fd | mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local |        3306 | ONLINE       | PRIMARY     | 8.0.22         |
+----------------+--------------------+-------------------+-------------+--------------+-------------+----------------+

换种方式显示

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
mysql> SELECT * FROM performance_schema.replication_group_members \G
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: 5aec08fe-2c6a-11eb-801c-0a580a80037b
   MEMBER_HOST: mysql-mgr-node2-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.22
*************************** 2. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: 5bc8f7c3-2c6a-11eb-9061-0a580a8300fd
   MEMBER_HOST: mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.22
2 rows in set (0.00 sec)

8.3 Node3节点

登录node3节点,执行命令

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
[root@support mysql-mgr]$oc rsh mysql-mgr-node3-0
$ mysql -uroot -p

# 安装插件
$ mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';

# 安装复制组账户
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'rpl_pass';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER="rpl_user", MASTER_PASSWORD="rpl_pass" FOR CHANNEL 'group_replication_recovery';



# 启动组复制
reset master;
START GROUP_REPLICATION USER='rpl_user', PASSWORD='rpl_pass';

这里启动node1和node2节点后,由于白名单正常工作。

Node1返回,视图进行了切换

1
2
[System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to mysql-mgr-node2-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306, mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306, mysql-mgr-node3-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306 on view 16060121683078752:3.'
[System] [MY-011492] [Repl] Plugin group_replication reported: 'The member with address mysql-mgr-node3-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306 was declared online within the replication group.'

Node3返回相关信息,总归四条记录。

其中“connected to master ‘rpl_user@mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306’,replication started in log ‘FIRST’ at position 4”显示已成功连接。

“This server was declared online within the replication group.”已成功加入集群。

1
2
3
4
5
6
[Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
[System] [MY-010562] [Repl] Slave I/O thread for channel 'group_replication_recovery': connected to master 'rpl_user@mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306',replication started in log 'FIRST' at position 4
 [System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.
 [System] [MY-011490] [Repl] Plugin group_replication reported: 'This server was declared online within the replication group.'
 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to mysql-mgr-node2-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306, mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306, mysql-mgr-node3-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306 on view 16060121683078752:3.'
 [System] [MY-011492] [Repl] Plugin group_replication reported: 'The member with address mysql-mgr-node3-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306 was declared online within the replication group.'

【验证】

1
2
oc rsh mysql-mgr-node1-0
mysql -uroot -p

Node1是PRIMARY节点,Node2和Node是SECONDARY节点

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
mysql> SELECT * FROM performance_schema.replication_group_members \G
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: 5aec08fe-2c6a-11eb-801c-0a580a80037b
   MEMBER_HOST: mysql-mgr-node2-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.22
*************************** 2. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: 5bc8f7c3-2c6a-11eb-9061-0a580a8300fd
   MEMBER_HOST: mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.22
*************************** 3. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: 5bd16cfb-2c6a-11eb-8f59-0a580a8300fc
   MEMBER_HOST: mysql-mgr-node3-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.22
3 rows in set (0.01 sec)
SELECT * FROM performance_schema.replication_group_members \G

8.4 组视图概念

MGR以组视图(Group View,简称视图)为基础来进行成员管理。视图指Group在一段时间内的成员状态,如果在这段时间内没有成员变化,也就是说没有成员加入或退出,则这段连续的时间为一个视图,如果发生了成员加入或退出变化,则视图也就发生了变化。

MGR使用视图ID(View ID)来跟踪视图的变化并区分视图的先后时间

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
mysql> select * from performance_schema.replication_group_member_stats\G ;
*************************** 1. row ***************************
                              CHANNEL_NAME: group_replication_applier
                                   VIEW_ID: 16060121683078752:3
                                 MEMBER_ID: 5aec08fe-2c6a-11eb-801c-0a580a80037b
               COUNT_TRANSACTIONS_IN_QUEUE: 0
                COUNT_TRANSACTIONS_CHECKED: 3
                  COUNT_CONFLICTS_DETECTED: 0
        COUNT_TRANSACTIONS_ROWS_VALIDATING: 1
        TRANSACTIONS_COMMITTED_ALL_MEMBERS: 5bc8f7c3-2c6a-11eb-9061-0a580a8300fd:1-5,
deabf3a9-66d3-4cc3-acb2-66ebd7800a7c:1-6
            LAST_CONFLICT_FREE_TRANSACTION: deabf3a9-66d3-4cc3-acb2-66ebd7800a7c:6
COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE: 0
         COUNT_TRANSACTIONS_REMOTE_APPLIED: 4
         COUNT_TRANSACTIONS_LOCAL_PROPOSED: 0
         COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 0
*************************** 2. row ***************************
                              CHANNEL_NAME: group_replication_applier
                                   VIEW_ID: 16060121683078752:3
                                 MEMBER_ID: 5bc8f7c3-2c6a-11eb-9061-0a580a8300fd
               COUNT_TRANSACTIONS_IN_QUEUE: 0
                COUNT_TRANSACTIONS_CHECKED: 3
                  COUNT_CONFLICTS_DETECTED: 0
        COUNT_TRANSACTIONS_ROWS_VALIDATING: 1
        TRANSACTIONS_COMMITTED_ALL_MEMBERS: 5bc8f7c3-2c6a-11eb-9061-0a580a8300fd:1-5,
deabf3a9-66d3-4cc3-acb2-66ebd7800a7c:1-6
            LAST_CONFLICT_FREE_TRANSACTION: deabf3a9-66d3-4cc3-acb2-66ebd7800a7c:6
COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE: 0
         COUNT_TRANSACTIONS_REMOTE_APPLIED: 2
         COUNT_TRANSACTIONS_LOCAL_PROPOSED: 3
         COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 0
*************************** 3. row ***************************
                              CHANNEL_NAME: group_replication_applier
                                   VIEW_ID: 16060121683078752:3
                                 MEMBER_ID: 5bd16cfb-2c6a-11eb-8f59-0a580a8300fc
               COUNT_TRANSACTIONS_IN_QUEUE: 0
                COUNT_TRANSACTIONS_CHECKED: 3
                  COUNT_CONFLICTS_DETECTED: 0
        COUNT_TRANSACTIONS_ROWS_VALIDATING: 1
        TRANSACTIONS_COMMITTED_ALL_MEMBERS: 5bc8f7c3-2c6a-11eb-9061-0a580a8300fd:1-5,
deabf3a9-66d3-4cc3-acb2-66ebd7800a7c:1-6
            LAST_CONFLICT_FREE_TRANSACTION: deabf3a9-66d3-4cc3-acb2-66ebd7800a7c:6
COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE: 0
         COUNT_TRANSACTIONS_REMOTE_APPLIED: 3
         COUNT_TRANSACTIONS_LOCAL_PROPOSED: 0
         COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 0
3 rows in set (0.00 sec)

这里的视图ID为,根据下面语句显示是2020年11月22日 02:11:28 创建的(UTC时间)

1
2
3
4
5
6
7
mysql> select from_unixtime(FLOOR(16060121683078752/10000000),'%Y-%m-%d %h:%m:%s');
+----------------------------------------------------------------------+
| from_unixtime(FLOOR(16060121683078752/10000000),'%Y-%m-%d %h:%m:%s') |
+----------------------------------------------------------------------+
| 2020-11-22 02:11:28                                                  |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)

Group视图ViewID由前缀时间戳+序号部分组成。

前缀部分:是在这个Group初始化时产生,为当时的时间戳,Group存活期间该值不变。

序号部分:Group初始化时,第一个视图序号为1,以后任何成员加入或退出序号都增一。

8.5 节点加入过程

https://typorabyethancheung911.oss-cn-shanghai.aliyuncs.com/typora/16f21fe1f2ed245e.png

一个节点请求加入Group时,其首先会根据配置的group_replication_group_seeds参数跟Group的种子成员建立TCP连接 (Gcs_xcom_control::do_join())。该种子成员会根据自己的group_replication_ip_whitelist(ip白名单)检查是否允许新节点加入,MGR默认不限制新节点的ip。连接建立后,新节点发送请求申请加入组。 收到请求后,种子成员广播视图变化的消息给Group中的所有节点,包括申请加入的节点,如右上所示;各节点收到消息后开始做视图切换。每个节点都会广播一个状态交换消息,每个交换消息包含了节点的当前状态和信息,如图左下所示。发送了交换消息后,各个节点开始接收其他节点广播的消息,将其中的节点信息更新到本节点所维护的成员列表中。

完成视图切换只是成员加入Group要做的第一步,只是说明该成员可以接收到Group中通过Paxos协议达成共识的消息,并不意味着可以将成员设置为ONLINE(上线)对外提供服务。原因是新成员还需要进行数据同步,建立起正确的数据版本(recovery_module->start_recovery)。之后才能执行Paxos协议消息,进而上线提供正常的用户访问服务。

9.MGR测试

完整的集群建立后,各节点的文件详见如下

node1上,data目录其实在nfs服务器上

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
[root@support node1]$ll /data/nfs/mysqlm-mgr-pri/node1/
total 187784
-rw-r-----. 1 1000610000 nfsnobody       56 Nov 23 10:58 auto.cnf
-rw-r-----. 1 1000610000 nfsnobody  3046539 Nov 23 10:58 binlog.000001
-rw-r-----. 1 1000610000 nfsnobody     1716 Nov 23 13:19 binlog.000002
-rw-r-----. 1 1000610000 nfsnobody       32 Nov 23 10:58 binlog.index
-rw-------. 1 1000610000 nfsnobody     1680 Nov 23 10:58 ca-key.pem
-rw-r--r--. 1 1000610000 nfsnobody     1112 Nov 23 10:58 ca.pem
-rw-r--r--. 1 1000610000 nfsnobody     1112 Nov 23 10:58 client-cert.pem
-rw-------. 1 1000610000 nfsnobody     1676 Nov 23 10:58 client-key.pem
-rw-r-----. 1 1000610000 nfsnobody        0 Nov 23 10:59 GCS_DEBUG_TRACE
-rw-r-----. 1 1000610000 nfsnobody   196608 Nov 23 11:00 #ib_16384_0.dblwr
-rw-r-----. 1 1000610000 nfsnobody  8585216 Nov 23 10:58 #ib_16384_1.dblwr
-rw-r-----. 1 1000610000 nfsnobody     5429 Nov 23 10:58 ib_buffer_pool
-rw-r-----. 1 1000610000 nfsnobody 12582912 Nov 23 10:59 ibdata1
-rw-r-----. 1 1000610000 nfsnobody 50331648 Nov 23 11:00 ib_logfile0
-rw-r-----. 1 1000610000 nfsnobody 50331648 Nov 23 10:58 ib_logfile1
-rw-r-----. 1 1000610000 nfsnobody 12582912 Nov 23 10:59 ibtmp1
drwxr-x---. 2 1000610000 nfsnobody      187 Nov 23 10:58 #innodb_temp
-rw-r-----. 1 1000610000 nfsnobody    12354 Nov 23 13:19 logerror.err
drwxr-x---. 2 1000610000 nfsnobody      143 Nov 23 10:58 mysql
-rw-r-----. 1 1000610000 nfsnobody 30408704 Nov 23 10:59 mysql.ibd
-rw-r-----. 1 1000610000 nfsnobody        2 Nov 23 10:58 mysql-mgr-node1-0.pid
-rw-r-----. 1 1000610000 nfsnobody      239 Nov 23 10:59 mysql-mgr-node1-0-relay-bin-group_replication_applier.000001
-rw-r-----. 1 1000610000 nfsnobody     1781 Nov 23 13:19 mysql-mgr-node1-0-relay-bin-group_replication_applier.000002
-rw-r-----. 1 1000610000 nfsnobody      126 Nov 23 10:59 mysql-mgr-node1-0-relay-bin-group_replication_applier.index
-rw-r-----. 1 1000610000 nfsnobody      152 Nov 23 10:59 mysql-mgr-node1-0-relay-bin-group_replication_recovery.000001
-rw-r-----. 1 1000610000 nfsnobody       64 Nov 23 10:59 mysql-mgr-node1-0-relay-bin-group_replication_recovery.index
drwxr-x---. 2 1000610000 nfsnobody     8192 Nov 23 10:58 performance_schema
-rw-------. 1 1000610000 nfsnobody     1680 Nov 23 10:58 private_key.pem
-rw-r--r--. 1 1000610000 nfsnobody      452 Nov 23 10:58 public_key.pem
-rw-r--r--. 1 1000610000 nfsnobody     1112 Nov 23 10:58 server-cert.pem
-rw-------. 1 1000610000 nfsnobody     1676 Nov 23 10:58 server-key.pem
drwxr-x---. 2 1000610000 nfsnobody       28 Nov 23 10:58 sys
-rw-r-----. 1 1000610000 nfsnobody 13631488 Nov 23 11:00 undo_001
-rw-r-----. 1 1000610000 nfsnobody 10485760 Nov 23 11:00 undo_002

node2上

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
[root@support node1]$ll /data/nfs/mysqlm-mgr-pri/node2/
total 191884
-rw-r-----. 1 1000610000 nfsnobody       56 Nov 23 10:58 auto.cnf
-rw-r-----. 1 1000610000 nfsnobody  3048074 Nov 23 13:19 binlog.000001
-rw-r-----. 1 1000610000 nfsnobody       16 Nov 23 11:01 binlog.index
-rw-------. 1 1000610000 nfsnobody     1680 Nov 23 10:58 ca-key.pem
-rw-r--r--. 1 1000610000 nfsnobody     1112 Nov 23 10:58 ca.pem
-rw-r--r--. 1 1000610000 nfsnobody     1112 Nov 23 10:58 client-cert.pem
-rw-------. 1 1000610000 nfsnobody     1676 Nov 23 10:58 client-key.pem
-rw-r-----. 1 1000610000 nfsnobody        0 Nov 23 11:01 GCS_DEBUG_TRACE
-rw-r-----. 1 1000610000 nfsnobody   196608 Nov 23 11:02 #ib_16384_0.dblwr
-rw-r-----. 1 1000610000 nfsnobody  8585216 Nov 23 10:58 #ib_16384_1.dblwr
-rw-r-----. 1 1000610000 nfsnobody     5446 Nov 23 10:58 ib_buffer_pool
-rw-r-----. 1 1000610000 nfsnobody 12582912 Nov 23 11:01 ibdata1
-rw-r-----. 1 1000610000 nfsnobody 50331648 Nov 23 11:02 ib_logfile0
-rw-r-----. 1 1000610000 nfsnobody 50331648 Nov 23 10:58 ib_logfile1
-rw-r-----. 1 1000610000 nfsnobody 12582912 Nov 23 10:58 ibtmp1
drwxr-x---. 2 1000610000 nfsnobody      187 Nov 23 10:58 #innodb_temp
-rw-r-----. 1 1000610000 nfsnobody    13642 Nov 23 13:19 logerror.err
drwxr-x---. 2 1000610000 nfsnobody      143 Nov 23 10:58 mysql
-rw-r-----. 1 1000610000 nfsnobody 30408704 Nov 23 11:01 mysql.ibd
-rw-r-----. 1 1000610000 nfsnobody        2 Nov 23 10:58 mysql-mgr-node2-0.pid
-rw-r-----. 1 1000610000 nfsnobody      239 Nov 23 11:01 mysql-mgr-node2-0-relay-bin-group_replication_applier.000001
-rw-r-----. 1 1000610000 nfsnobody     1434 Nov 23 13:19 mysql-mgr-node2-0-relay-bin-group_replication_applier.000002
-rw-r-----. 1 1000610000 nfsnobody      126 Nov 23 11:01 mysql-mgr-node2-0-relay-bin-group_replication_applier.index
-rw-r-----. 1 1000610000 nfsnobody      240 Nov 23 11:01 mysql-mgr-node2-0-relay-bin-group_replication_recovery.000001
-rw-r-----. 1 1000610000 nfsnobody      273 Nov 23 11:01 mysql-mgr-node2-0-relay-bin-group_replication_recovery.000002
-rw-r-----. 1 1000610000 nfsnobody      128 Nov 23 11:01 mysql-mgr-node2-0-relay-bin-group_replication_recovery.index
drwxr-x---. 2 1000610000 nfsnobody     8192 Nov 23 10:58 performance_schema
-rw-------. 1 1000610000 nfsnobody     1680 Nov 23 10:58 private_key.pem
-rw-r--r--. 1 1000610000 nfsnobody      452 Nov 23 10:58 public_key.pem
-rw-r--r--. 1 1000610000 nfsnobody     1112 Nov 23 10:58 server-cert.pem
-rw-------. 1 1000610000 nfsnobody     1680 Nov 23 10:58 server-key.pem
drwxr-x---. 2 1000610000 nfsnobody       28 Nov 23 10:58 sys
-rw-r-----. 1 1000610000 nfsnobody 13631488 Nov 23 11:02 undo_001
-rw-r-----. 1 1000610000 nfsnobody 14680064 Nov 23 11:02 undo_002

node3上

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
[root@support node1]$ll /data/nfs/mysqlm-mgr-pri/node3/
total 188812
-rw-r-----. 1 1000610000 nfsnobody       56 Nov 23 10:58 auto.cnf
-rw-r-----. 1 1000610000 nfsnobody  3048074 Nov 23 13:19 binlog.000001
-rw-r-----. 1 1000610000 nfsnobody       16 Nov 23 13:18 binlog.index
-rw-------. 1 1000610000 nfsnobody     1680 Nov 23 10:58 ca-key.pem
-rw-r--r--. 1 1000610000 nfsnobody     1112 Nov 23 10:58 ca.pem
-rw-r--r--. 1 1000610000 nfsnobody     1112 Nov 23 10:58 client-cert.pem
-rw-------. 1 1000610000 nfsnobody     1676 Nov 23 10:58 client-key.pem
-rw-r-----. 1 1000610000 nfsnobody        0 Nov 23 13:17 GCS_DEBUG_TRACE
-rw-r-----. 1 1000610000 nfsnobody   196608 Nov 23 13:20 #ib_16384_0.dblwr
-rw-r-----. 1 1000610000 nfsnobody  8585216 Nov 23 10:58 #ib_16384_1.dblwr
-rw-r-----. 1 1000610000 nfsnobody     5446 Nov 23 10:58 ib_buffer_pool
-rw-r-----. 1 1000610000 nfsnobody 12582912 Nov 23 13:19 ibdata1
-rw-r-----. 1 1000610000 nfsnobody 50331648 Nov 23 13:20 ib_logfile0
-rw-r-----. 1 1000610000 nfsnobody 50331648 Nov 23 10:58 ib_logfile1
-rw-r-----. 1 1000610000 nfsnobody 12582912 Nov 23 10:59 ibtmp1
drwxr-x---. 2 1000610000 nfsnobody      187 Nov 23 10:58 #innodb_temp
-rw-r-----. 1 1000610000 nfsnobody    13447 Nov 23 13:19 logerror.err
drwxr-x---. 2 1000610000 nfsnobody      143 Nov 23 10:58 mysql
-rw-r-----. 1 1000610000 nfsnobody 30408704 Nov 23 13:19 mysql.ibd
-rw-r-----. 1 1000610000 nfsnobody        2 Nov 23 10:58 mysql-mgr-node3-0.pid
-rw-r-----. 1 1000610000 nfsnobody      238 Nov 23 13:18 mysql-mgr-node3-0-relay-bi-group_replication_applier.000001
-rw-r-----. 1 1000610000 nfsnobody      660 Nov 23 13:19 mysql-mgr-node3-0-relay-bi-group_replication_applier.000002
-rw-r-----. 1 1000610000 nfsnobody      124 Nov 23 13:18 mysql-mgr-node3-0-relay-bi-group_replication_applier.index
-rw-r-----. 1 1000610000 nfsnobody      239 Nov 23 13:19 mysql-mgr-node3-0-relay-bi-group_replication_recovery.000001
-rw-r-----. 1 1000610000 nfsnobody      273 Nov 23 13:19 mysql-mgr-node3-0-relay-bi-group_replication_recovery.000002
-rw-r-----. 1 1000610000 nfsnobody      126 Nov 23 13:19 mysql-mgr-node3-0-relay-bi-group_replication_recovery.index
drwxr-x---. 2 1000610000 nfsnobody     8192 Nov 23 10:58 performance_schema
-rw-------. 1 1000610000 nfsnobody     1676 Nov 23 10:58 private_key.pem
-rw-r--r--. 1 1000610000 nfsnobody      452 Nov 23 10:58 public_key.pem
-rw-r--r--. 1 1000610000 nfsnobody     1112 Nov 23 10:58 server-cert.pem
-rw-------. 1 1000610000 nfsnobody     1680 Nov 23 10:58 server-key.pem
drwxr-x---. 2 1000610000 nfsnobody       28 Nov 23 10:58 sys
-rw-r-----. 1 1000610000 nfsnobody 14680064 Nov 23 13:20 undo_001
-rw-r-----. 1 1000610000 nfsnobody 10485760 Nov 23 13:20 undo_002

完整的集群信息如下:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
mysql> SELECT * FROM performance_schema.replication_group_members \G
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: b97d71fd-2d37-11eb-8f43-0a580a800396
   MEMBER_HOST: mysql-mgr-node2-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.22
*************************** 2. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: b99e0593-2d37-11eb-931f-0a580a830103
   MEMBER_HOST: mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.22
*************************** 3. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: b9a35b98-2d37-11eb-bd5d-0a580a830102
   MEMBER_HOST: mysql-mgr-node3-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.22
3 rows in set (0.00 sec)

9.1 数据同步测试

测试除主节点外节点无法写入

1
2
oc rsh mysql-mgr-node2-0
mysql -uroot -p

新建一个数据库

1
2
mysql> CREATE DATABASE test;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

super-read-only=OFF 禁止任何人写入副本。在单主模式下避免发送写操作的导致读写不一致的问题。

登录主节点

1
2
oc rsh mysql-mgr-node1-0
mysql -uroot -p

写入数据测试

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.02 sec)

mysql> CREATE TABLE test.table1 (id INT PRIMARY KEY, content TEXT NOT NULL);
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO test.table1 VALUES (1, 'CJCC');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM test.table1;
+----+---------+
| id | content |
+----+---------+
|  1 | CJCC    |
+----+---------+
1 row in set (0.00 sec)

其他节点查看同步情况

1
2
oc rsh mysql-mgr-node2-0
mysql -uroot -p

查看数据,已正常同步

1
2
3
4
5
6
7
mysql> SELECT * FROM test.table1;
+----+---------+
| id | content |
+----+---------+
|  1 | CJCC    |
+----+---------+
1 row in set (0.00 sec)

9.2 故障测试

模拟node1节点故障,手动在Openshift webconsole上关闭node1 pod。因为statefulset的特性,pod自动拉起。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
[root@support ~]$oc get pod
NAME                READY   STATUS        RESTARTS   AGE
busybox-0           1/1     Running       80         3d8h
mysql-mgr-node1-0   0/1     Terminating   0          9h
mysql-mgr-node2-0   1/1     Running       0          9h
mysql-mgr-node3-0   1/1     Running       0          9h
[root@support ~]$oc get pod -w
NAME                READY   STATUS              RESTARTS   AGE
busybox-0           1/1     Running             80         3d8h
mysql-mgr-node1-0   0/1     ContainerCreating   0          3s
mysql-mgr-node2-0   1/1     Running             0          9h
mysql-mgr-node3-0   1/1     Running             0          9h
mysql-mgr-node1-0   1/1     Running             0          4s

重新进入后发现,由于loose-group_replication_start_on_boot=OFF,node1无法自动启动群复制,node1已经从集群中离线

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
mysql> SELECT * FROM performance_schema.replication_group_members \G
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: 
   MEMBER_HOST: 
   MEMBER_PORT: NULL
  MEMBER_STATE: OFFLINE
   MEMBER_ROLE: 
MEMBER_VERSION: 
1 row in set (0.00 sec)

进入node3进行查看

1
2
[root@support mysql-mgr]$oc rsh mysql-mgr-node3-0
$ mysql -uroot -p

查看集群情况,node2被选举为新的PRIMARY。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
mysql> SELECT * FROM performance_schema.replication_group_members \G
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: b97d71fd-2d37-11eb-8f43-0a580a800396
   MEMBER_HOST: mysql-mgr-node2-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.22
*************************** 2. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: b9a35b98-2d37-11eb-bd5d-0a580a830102
   MEMBER_HOST: mysql-mgr-node3-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.22
2 rows in set (0.00 sec)

在node2节点上写入一条新记录(单主模式模式下只有角色为PRIMARY的node2节点才能写入数据)

将node1节点恢复至集群内。

1
2
3
[root@support mysql-mgr]$oc rsh mysql-mgr-node1-0
$ mysql -uroot -p
$ mysql> START GROUP_REPLICATION USER='rpl_user', PASSWORD='rpl_pass';

此时node1为SECONDARY节点。node2继续为PRIMARY节点。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
mysql> SELECT * FROM performance_schema.replication_group_members \G
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: b97d71fd-2d37-11eb-8f43-0a580a800396
   MEMBER_HOST: mysql-mgr-node2-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.22
*************************** 2. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: b99e0593-2d37-11eb-931f-0a580a830103
   MEMBER_HOST: mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.22
*************************** 3. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: b9a35b98-2d37-11eb-bd5d-0a580a830102
   MEMBER_HOST: mysql-mgr-node3-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.22
3 rows in set (0.00 sec

【可正常加入到MGR组复制集群内并自动同步其他节点数据】

【node1没恢复加入集群前,没有测试数据,加入后,自动从主库恢复了数据】

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
mysql> SELECT * FROM test.table1;
ERROR 1049 (42000): Unknown database 'test'
mysql> START GROUP_REPLICATION USER='rpl_user', PASSWORD='rpl_pass';
Query OK, 0 rows affected, 1 warning (7.97 sec)

mysql> SELECT * FROM test.table1;
+----+---------+
| id | content |
+----+---------+
|  1 | CJCC    |
+----+---------+
1 row in set (0.00 sec)

9.3 自动故障恢复

将node1的配置文件中loose-group_replication_start_on_boot=OFF设置为ON。

对于容器化部署而言,就是更改configmap的值。

1
2
[root@support mysql-mgr]$oc apply -f mysqlconfigmap.yaml 
configmap/mysqlconfigmap configured

按照前述”9.2 故障测试“步骤继续操作,在恢复前自动再插入一条记录。

继续手动在Openshift webconsole上关闭node1 pod。因为statefulset的特性,pod自动拉起。

视机器性能情况,pod瞬间拉起后,数据同步完成。

1
2
3
4
5
6
7
8
mysql> SELECT * FROM test.table1;
+----+---------+
| id | content |
+----+---------+
|  1 | CJCC    |
|  2 | CJCC    |
+----+---------+
2 rows in set (0.01 sec)

两条记录完整。

9.4 I/O故障

长时间I/O故障后的集群修复,需要先进行一次同步。后续文章详细描述。

10.模式切换

首先简单介绍下单主和多主模式的区别。

单主模型: 从复制组中多个MySQL节点中自动选举一个master节点,只有master节点可以写,其他节点自动设置为read only。当master节点故障时,会自动选举一个新的master节点,选举成功后,它将设置为可写,其他slave将指向这个新的master。

多主模型: 复制组中的任何一个节点都可以写,因此没有master和slave的概念,只要突然故障的节点数量不太多,这个多主模型就能继续可用。

MySQL组复制使用Paxos分布式算法来提供节点间的分布式协调。正因如此,它要求组中大多数节点在线才能达到法定票数,从而对一个决策做出一致的决定。

大多数指的是N/2+1(N是组中目前节点总数),

当要停止组中的某个成员中的组复制功能时,可在那个节点上执行stop group_replication语句。

但一定要注意,在执行这个语句之前,必须要保证这个节点不会向外提供MySQL服务,否则有可能会有新数据写入(例如主节点停止时),或者读取到过期数据。

故要安全地重启整个组,最佳方法是先停止所有非主节点的MySQL实例(不仅是停止组复制功能),然后停止主节点的MySQL实例。启动次序是先重启主节点,在这个节点上引导组,并启动它的组复制功能,最后再将各slave节点加入组。【其实可以停止中间件服务实现停止整个集群的对外服务】。

10.1 单主切换至多主

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
# node1节点
[root@support mysql-mgr]$oc rsh mysql-mgr-node1-0
$ mysql -uroot -p
stop group_replication; 
set global group_replication_single_primary_mode=OFF;
set global group_replication_enforce_update_everywhere_checks=ON;

# node2节点
[root@support mysql-mgr]$oc rsh mysql-mgr-node2-0
$ mysql -uroot -p
stop group_replication; 
set global group_replication_single_primary_mode=OFF;
set global group_replication_enforce_update_everywhere_checks=ON;

# node3节点
[root@support mysql-mgr]$oc rsh mysql-mgr-node3-0
$ mysql -uroot -p

# node3节点上可以看到只有集群只有1个节点了。
mysql> SELECT * FROM performance_schema.replication_group_members \G
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: b9a35b98-2d37-11eb-bd5d-0a580a830102
   MEMBER_HOST: mysql-mgr-node3-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.22
1 row in set (0.00 sec)

# 具体状态,这里视图已经发生了多次变化
mysql> select * from performance_schema.replication_group_member_stats\G ;
*************************** 1. row ***************************
                              CHANNEL_NAME: group_replication_applier
                              # 这里视图已经发生了多次变化
                                   VIEW_ID: 16061003965937304:13
                                 MEMBER_ID: b9a35b98-2d37-11eb-bd5d-0a580a830102
               COUNT_TRANSACTIONS_IN_QUEUE: 0
                COUNT_TRANSACTIONS_CHECKED: 4
                  COUNT_CONFLICTS_DETECTED: 0
        COUNT_TRANSACTIONS_ROWS_VALIDATING: 0
        # b99e0593-2d37-11eb-931f-0a580a830103是node2节点
        TRANSACTIONS_COMMITTED_ALL_MEMBERS: b99e0593-2d37-11eb-931f-0a580a830103:1-5,
deabf3a9-66d3-4cc3-acb2-66ebd7800a7c:1-11
# 这里deabf3a9-66d3-4cc3-acb2-66ebd7800a7c是我们组的UUID
            LAST_CONFLICT_FREE_TRANSACTION: deabf3a9-66d3-4cc3-acb2-66ebd7800a7c:10
COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE: 0
         COUNT_TRANSACTIONS_REMOTE_APPLIED: 7
         COUNT_TRANSACTIONS_LOCAL_PROPOSED: 0
         COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 0
1 row in set (0.00 sec)

# 执行停止组复制
stop group_replication; 
set global group_replication_single_primary_mode=OFF;
set global group_replication_enforce_update_everywhere_checks=ON;

正式执行多主操作

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
# 登录任一节点
[root@support mysql-mgr]$oc rsh mysql-mgr-node1-0
$ mysql -uroot -p
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION USER='rpl_user', PASSWORD='rpl_pass';
SET GLOBAL group_replication_bootstrap_group=OFF;

# 其他节点直接开启
[root@support mysql-mgr]$oc rsh mysql-mgr-node2-0
$ mysql -uroot -p
START GROUP_REPLICATION USER='rpl_user', PASSWORD='rpl_pass';

# 其他节点直接开启
[root@support mysql-mgr]$oc rsh mysql-mgr-node3-0
$ mysql -uroot -p
START GROUP_REPLICATION USER='rpl_user', PASSWORD='rpl_pass';

【验证】

继续停留在上一操作的最后一个节点上

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
mysql> SELECT * FROM performance_schema.replication_group_members \G
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: b97d71fd-2d37-11eb-8f43-0a580a800396
   MEMBER_HOST: mysql-mgr-node2-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.22
*************************** 2. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: b99e0593-2d37-11eb-931f-0a580a830103
   MEMBER_HOST: mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.22
*************************** 3. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: b9a35b98-2d37-11eb-bd5d-0a580a830102
   MEMBER_HOST: mysql-mgr-node3-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.22
3 rows in set (0.01 sec)


mysql> select * from performance_schema.replication_group_member_stats\G 
*************************** 1. row ***************************
                              CHANNEL_NAME: group_replication_applier
                                   VIEW_ID: 16061388435295357:3
                                 MEMBER_ID: b97d71fd-2d37-11eb-8f43-0a580a800396
               COUNT_TRANSACTIONS_IN_QUEUE: 0
                COUNT_TRANSACTIONS_CHECKED: 0
                  COUNT_CONFLICTS_DETECTED: 0
        COUNT_TRANSACTIONS_ROWS_VALIDATING: 0
        TRANSACTIONS_COMMITTED_ALL_MEMBERS: b99e0593-2d37-11eb-931f-0a580a830103:1-5,
deabf3a9-66d3-4cc3-acb2-66ebd7800a7c:1-13
            LAST_CONFLICT_FREE_TRANSACTION: 
COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE: 0
         COUNT_TRANSACTIONS_REMOTE_APPLIED: 1
         COUNT_TRANSACTIONS_LOCAL_PROPOSED: 0
         COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 0
*************************** 2. row ***************************
                              CHANNEL_NAME: group_replication_applier
                                   VIEW_ID: 16061388435295357:3
                                 MEMBER_ID: b99e0593-2d37-11eb-931f-0a580a830103
               COUNT_TRANSACTIONS_IN_QUEUE: 0
                COUNT_TRANSACTIONS_CHECKED: 0
                  COUNT_CONFLICTS_DETECTED: 0
        COUNT_TRANSACTIONS_ROWS_VALIDATING: 0
        TRANSACTIONS_COMMITTED_ALL_MEMBERS: b99e0593-2d37-11eb-931f-0a580a830103:1-5,
deabf3a9-66d3-4cc3-acb2-66ebd7800a7c:1-13
            LAST_CONFLICT_FREE_TRANSACTION: 
COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE: 0
         COUNT_TRANSACTIONS_REMOTE_APPLIED: 3
         COUNT_TRANSACTIONS_LOCAL_PROPOSED: 0
         COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 0
*************************** 3. row ***************************
                              CHANNEL_NAME: group_replication_applier
                                   VIEW_ID: 16061388435295357:3
                                 MEMBER_ID: b9a35b98-2d37-11eb-bd5d-0a580a830102
               COUNT_TRANSACTIONS_IN_QUEUE: 0
                COUNT_TRANSACTIONS_CHECKED: 0
                  COUNT_CONFLICTS_DETECTED: 0
        COUNT_TRANSACTIONS_ROWS_VALIDATING: 0
        TRANSACTIONS_COMMITTED_ALL_MEMBERS: 
            LAST_CONFLICT_FREE_TRANSACTION: 
COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE: 0
         COUNT_TRANSACTIONS_REMOTE_APPLIED: 0
         COUNT_TRANSACTIONS_LOCAL_PROPOSED: 0
         COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 0
3 rows in set (0.00 sec)

【可以看到所有MGR节点状态都是online,角色都是PRIMARY,MGR多主模式搭建成功。完成转换】

10.2 多主切换至单主

类似前述”单主切换至多主“

在每个节点上操作

1
2
3
4
stop group_replication;
# 下面的顺序不能颠倒
set global group_replication_enforce_update_everywhere_checks=OFF;
set global group_replication_single_primary_mode=ON;

选择一个节点作为主节点, 在主节点上执行

1
2
3
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION USER='rpl_user', PASSWORD='rpl_pass';
SET GLOBAL group_replication_bootstrap_group=OFF;

剩余节点直接,类似新节点加入时候。千万不要使用SET GLOBAL group_replication_bootstrap_group=ON;

1
START GROUP_REPLICATION USER='rpl_user', PASSWORD='rpl_pass';

11.系统高可用设计

什么是高可用?我们通常使用百分比来表示,即全年正常可用时间占比,例如99.999%,即表示全年99.999%的时间可用,换句话说全年仅仅允许5分钟宕机时间。

对于高可用实际上是在宕机造成的损失与降低宕机时间所花费的成本之间取一个平衡。实现高可用性可以一般通过提升MTBF与减低MTTR。

在之前,我们搭建了MySQL组复制集群环境,MySQL组复制集群环境解决了MySQL集群内部的自动故障转移,但是组复制并没有解决外部业务的故障转移。

MGR只是解决了数据一致性问题,并没有对外提供统一的访问VIP。

对于应用层来说,他并不能知道当前集群故障情况,很多时候通过类似SQL Proxy中间件来实现代理访问和客户访问故障转移。

当然也可以利用Vitess等分库分表中间件来解决,当时这种设计太重,不在本次讨论范围之内。

Mysql InnoDB cluster是Mysql官方提供的一个完全高可用的数据库解决方案,实现故障转移、故障恢复、读写分离、负载均衡等

Mysql InnoDB Cluster 主要由三个模块构成:

  1. MGR,
  2. Mysql-shell:提供了一套AdminAPI,可以自动化配置Group Replication,让我们无须再手动配置cluster中group replication相关参数【本文是采用手动配置】,Mysql-shell主要是将MGR配置成InnoDB。
  3. Mysql-router:内置读写分离,负载均衡。自动根据Mysql InnoDB Cluster中的metadata自动调整。

故障转移Failover, 即当活动的服务或应用意外终止时,快速启用冗余或备用的服务器、系统、硬件或者网络接替它们工作)

故障恢复Failback,将系统,组件,服务恢复到故障之前的组态

11.1 部署MySQL Shell

配置MySQL Shell是为了解决将MySQL MGR配置为InnoDB Cluster。

将MySQL MGR配置为InnoDB Cluster是为了配置Mysql Router。

前者都是后者的必要输入条件。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
# 下载mysql-shell镜像文件
[root@support mysql-mgr]$docker pull docker.io/muhamedalshafey/mysql-shell

[root@support mysql-mgr]$docker image ls | grep dandi028/mysql-shell
docker.io/muhamedalshafey/mysql-shell            latest              c0259b672358        2 years ago         217 MB


# db2b37ec6181是镜像ID,推送前先打上标记
[root@support mysqlm-mgr-pri]$docker tag 631893770523 registry.cj.io:5000/mysqlshell:latest

# 向内部私有镜像仓库推送
[root@support mysqlm-mgr-pri]$docker push registry.cj.io:5000/mysqlshell:latest

编辑mysqlshell的deployment,mysqlshell需要root权限运行。

注意这里以ethan账户权限运行

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
apiVersion: apps/v1
#kind: StatefulSet
kind: Deployment
metadata:
  name: mysqlshell
  #namespace: mysql-mgr
  namespace: mysql-mgr
spec:
  #serviceName: mysqlmgr-svc
  replicas: 1
  selector:
    matchLabels:
      app: mysqlshell
  template:
    metadata:
      labels:
        app: mysqlshell
    spec:
      serviceAccount: ethan
      serviceAccountName: ethan
      containers:
        - name: mysqlshell
          image: 'registry.cj.io:5000/mysqlshell:latest'
          command:
          - sleep
          - "3600"

应为mysqlshell需要特权模式运行,因此添加sa

1
2
3
4
[root@support mysql-mgr]$oc create sa ethan
serviceaccount/ethan created
[root@support mysql-mgr]$oc adm policy add-scc-to-user anyuid -z ethan
clusterrole.rbac.authorization.k8s.io/system:openshift:scc:anyuid added: "ethan"

创建mysqlshell的deployment

1
[root@support mysql-mgr]$oc create -f mysqlshell.yaml

11.2 将MGR配置成InnoDB Cluster

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
# 获取mysqlshell pod信息
[root@support mysql-mgr]$oc get pod
NAME                             READY   STATUS    RESTARTS   AGE
centos7-6c5c45b6bf-228b2         1/1     Running   0          12h
etcd-operator-66f94b6847-9s2gb   1/1     Running   0          3d14h
mysql-mgr-node1-0                1/1     Running   0          8m48s
mysql-mgr-node2-0                1/1     Running   0          8m47s
mysql-mgr-node3-0                1/1     Running   0          8m47s
mysql-router-0                   1/1     Running   0          13m
mysqlshell-78bd978565-24cjj      1/1     Running   11         11h


# 登录到mysqlshell内
[root@support mysql-mgr]$oc rsh mysqlshell-78bd978565-24cjj

mysqlsh操作,集群名为cjtestmysqlmgr

【注意这里是单主模式,多主模式的MGR转InnoDB Cluster小白我目前不会操作】

参考https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-innodb-cluster-from-group-replication.html

Adopting a Group Replication Deployment

If you have an existing deployment of Group Replication and you want to use it to create a cluster, pass the adoptFromGR option to the dba.createCluster() function. The created InnoDB Cluster matches whether the replication group is running as single-primary or multi-primary.

To adopt an existing Group Replication group, connect to a group member using MySQL Shell. In the following example a single-primary group is adopted. We connect to gr-member-2, a secondary instance, while gr-member-1 is functioning as the group’s primary. Create a cluster using dba.createCluster(), passing in the adoptFromGR option.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
# 输入mysqlsh
sh-4.2# mysqlsh
MySQL Shell 8.0.16

Copyright (c) 2016, 2019, 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 '\?' for help; '\quit' to exit.

MySQL  JS > 

shell.connect('root@rsh mysql-mgr-node1-0')

# \connect root@mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local 连接到
 MySQL  JS > \connect root@mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local
Creating a session to 'root@mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local'
Please provide the password for 'root@mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local': ****
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 28 (X protocol)
Server version: 8.0.22 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.

 MySQL  mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:33060+ ssl  JS > 
 
 
# 关键操作
var cluster = dba.createCluster('cjtestmysqlmgr', {adoptFromGR: true});

 MySQL  mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:33060+ ssl  JS > var cluster = dba.createCluster('cjtestmysqlmgr', {adoptFromGR: true});
A new InnoDB cluster will be created based on the existing replication group on instance 'root@mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306'.

Creating InnoDB cluster 'cjtestmysqlmgr' on 'root@mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306'...

Adding Seed Instance...
Adding Instance 'mysql-mgr-node2-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306'...
Adding Instance 'mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306'...
Adding Instance 'mysql-mgr-node3-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306'...
Cluster successfully created based on existing replication group.

【# 至此转换完毕!,注意这里是单主模式。】

再次说明:【注意这里是单主模式,多主模式的MGR转InnoDB Cluster小白我目前不会操作】

11.3 InnoDB Cluster验证操作

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
# 首先务必登录已建立好的集群。
MySQL  JS > \connect root@mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local
Creating a session to 'root@mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local'
Please provide the password for 'root@mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local': ****
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 31 (X protocol)
Server version: 8.0.22 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.

# 就是var cluster = dba.getCluster();和cluster.status();连个函数

 MySQL  mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:33060+ ssl  JS > var cluster = dba.getCluster();
 MySQL  mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:33060+ ssl  JS > cluster.status();
 
{
    "clusterName": "cjtestmysqlmgr", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306", 
        "ssl": "DISABLED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        # 各成员状态
        "topology": {
            "mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306": {
                "address": "mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.22"
            }, 
            "mysql-mgr-node2-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306": {
                "address": "mysql-mgr-node2-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.22"
            }, 
            "mysql-mgr-node3-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306": {
                "address": "mysql-mgr-node3-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.22"
            }
        }, 
        # 单主模式
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306"
}

其他命令cluster.describe();

【至此MySQL MGR已配置为InnoDB Cluster,如果不进行配置,MySQL Router无法使用】,

11.4 MySQL Router

11.4.1 介绍

MySQL Router 是一个轻量级透明中间件,可以自动获取上述集群的状态,规划 SQL 语句,分配到合理的 MySQL 后端进行执行。

官方参考地址:https://dev.mysql.com/doc/mysql-router/8.0/en/mysql-router-installation-docker.html

官方的生产系统部署图:

将三个MySQL服务器分组在一起,作为生产InnoDB集群。

其中一台服务器是主实例,另外两台是辅助实例。

MySQL Shell中的管理功能直接与生产InnoDB集群交互。

https://typorabyethancheung911.oss-cn-shanghai.aliyuncs.com/typora/production_servers.png

MySQL Router是一个可执行文件,可以与应用程序在同一平台上运行,也可以单独部署。

MySQL Router是MySQL Proxy的替代方案,MySQL官方不建议将MySQL Proxy用于生产环境,并且已经不提供MySQL Proxy的下载。

MySQL Router作为一个流量转发层,位于应用与MySQL服务器之间,其功能类似于LVS。MySQL Servers作为Router的“downstream”(NAT模式),应用不再直连MySQL Servers,而是与Router相连。根据Router的配置,将会把应用程序的读写请求转发给下游的MySQL Servers。

当下游有多个MySQL Servers,无论主、从,Router可以对读写请求进行负载均衡。当下游某个Server失效时,Router可以将其从Active列表中移除,当其online后再次加入Active列表,即提供了Failover特性。

当MySQL Servers集群拓扑变更时,比如增减Slaves节点,只需要修改Router的配置即可,无需修改应用中数据库连接配置,因为应用配置的为Router地址而非MySQL Servers的原始地址,即数据库集群对应用来说是透明的。如果MySQL Servers为5.7+版本,且构建为InnoDB Cluster模式,那么Router还能基于metaCache(metaServers)机制,感知MySQL Servers的主从切换、从库增减等集群拓扑变更,而且基于变更能够实现Master自动切换、Slaves列表自动装配等。比如Master失效后,Cluster将会自动选举一个新的Master,此时Router不需要任何调整、可以自动发现此新Master进而继续为应用服务。

考虑到Router独立部署可能引入“额外的部署成本”、“性能降级”、“连接数上限”等问题,通常建议基于“Agent”方式部署,即将Router与应用部署在机器上。Router通常是解决“MySQL集群规模性迁移”,比如跨机房部署、流量迁移、异构兼容,或者解决MySQL集群规模性宕机时快速切换等。

Router中间件本身不会对请求“拆包”(unpackage),所以无法在Router中间件上实现比如“SQL审计”、“隔离”、“限流”、“分库分表”等功能。但是Router提供了plugin(C语言)机制,用户可以开发自己的plugin来扩展Router的额外特性。

11.4.2 安装

安装MySQL Router,还是采用容器部署方式。

这里MySQL Router容器化部署方式曾经一度放弃。

1
2
3
4
5
6
7
[root@support mysql-mgr]$docker pull mysql/mysql-router

[root@support ~]$docker image ls | grep mysql-router 
docker.io/mysql/mysql-router      latest              8b9d44f0a5bd        5 weeks ago         433 MB

[root@support mysql-mgr]$docker tag 8b9d44f0a5bd registry.cj.io:5000/mysql-router:latest
[root@support mysql-mgr]$docker push registry.cj.io:5000/mysql-router:latest

创建MySQL Router的StatefulSet的yaml文件,之所以这里采用StatefulSet,是需要通过固定域名访问。

【注意:这里的环境变量设置,包括MGR的节点地址,端口号,账户名和密码,MYSQL_INNODB_CLUSTER_MEMBERS是可选项】

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mysql-router
  namespace: mysql-mgr
spec:
  replicas: 3
  selector:
    matchLabels:
      app: mysqlrouter
  template:
    metadata:
      labels:
        app: mysqlrouter
    spec:
      serviceAccount: ethan
      serviceAccountName: ethan
      containers:
        - name: mysql-router
          image: 'registry.cj.io:5000/mysql-router:latest'
          #command: ["/bin/sh"]
          #args: ["-c","/usr/local/mysqlrouter1/start.sh"]
          #command:
          #- mysqlrouter -c /usr/local/mysqlrouter1/mysqlrouter.conf
          env:
          - name: MYSQL_HOST
            value: mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local
          - name: MYSQL_PORT
            value: "3306"
          - name: MYSQL_USER
            value: root
          - name: MYSQL_PASSWORD
            value: Pass
          - name: MYSQL_INNODB_CLUSTER_MEMBERS
            value: "3"
          #volumeMounts:
          #- name: mysqlrouterstorage
          #  mountPath: "/usr/local"
      #volumes:
      #- name: mysqlrouterstorage
      #  persistentVolumeClaim:
      #    claimName: pvc-mysqlmgr

创建MySQL Router

1
oc create -f mysql-router.yaml

创建完成后,查看日志

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
Succesfully contacted mysql server at mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local. Checking for cluster state.
0
12
Successfully contacted cluster with 3 members. Bootstrapping.
Succesfully contacted mysql server at mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local. Trying to bootstrap.
Please enter MySQL password for root: 
# Bootstrapping MySQL Router instance at '/tmp/mysqlrouter'...

- Creating account(s) (only those that are needed, if any)
- 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 /tmp/mysqlrouter/mysqlrouter.conf

# MySQL Router configured for the InnoDB Cluster 'cjtestmysqlmgr'

After this MySQL Router has been started with the generated configuration

    $ mysqlrouter -c /tmp/mysqlrouter/mysqlrouter.conf

the cluster 'cjtestmysqlmgr' 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

Starting mysql-router.
2020-12-12 14:41:44 http_server INFO [7f346de7d880] listening on 0.0.0.0:8443
2020-12-12 14:41:44 io INFO [7f346de7d880] starting 8 io-threads, using backend 'linux_epoll'
2020-12-12 14:41:44 routing INFO [7f3424ff9700] [routing:cjtestmysqlmgr_ro] started: listening on 0.0.0.0:6447, routing strategy = round-robin-with-fallback
2020-12-12 14:41:44 routing INFO [7f340bfff700] [routing:cjtestmysqlmgr_rw] started: listening on 0.0.0.0:6446, routing strategy = first-available
2020-12-12 14:41:44 routing INFO [7f340b7fe700] [routing:cjtestmysqlmgr_x_ro] started: listening on 0.0.0.0:64470, routing strategy = round-robin-with-fallback
2020-12-12 14:41:44 routing INFO [7f340affd700] [routing:cjtestmysqlmgr_x_rw] started: listening on 0.0.0.0:64460, routing strategy = first-available
2020-12-12 14:41:44 metadata_cache INFO [7f343bfff700] Starting Metadata Cache
2020-12-12 14:41:44 metadata_cache INFO [7f343bfff700] Connections using ssl_mode 'PREFERRED'
2020-12-12 14:41:44 metadata_cache INFO [7f34585f1700] Starting metadata cache refresh thread
2020-12-12 14:41:44 metadata_cache INFO [7f34585f1700] Potential changes detected in cluster 'cjtestmysqlmgr' after metadata refresh
2020-12-12 14:41:44 metadata_cache INFO [7f34585f1700] Metadata for cluster 'cjtestmysqlmgr' has 1 replicasets:
2020-12-12 14:41:44 metadata_cache INFO [7f34585f1700] 'default' (3 members, single-primary)
2020-12-12 14:41:44 metadata_cache INFO [7f34585f1700]     mysql-mgr-node2-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306 / 33060 - mode=RO 
2020-12-12 14:41:44 metadata_cache INFO [7f34585f1700]     mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306 / 33060 - mode=RW 
2020-12-12 14:41:44 metadata_cache INFO [7f34585f1700]     mysql-mgr-node3-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306 / 33060 - mode=RO 
2020-12-12 14:41:44 routing INFO [7f34585f1700] Routing routing:cjtestmysqlmgr_x_rw listening on 64460 got request to disconnect invalid connections: metadata change
2020-12-12 14:41:44 routing INFO [7f34585f1700] Routing routing:cjtestmysqlmgr_rw listening on 6446 got request to disconnect invalid connections: metadata change
2020-12-12 14:41:44 routing INFO [7f34585f1700] Routing routing:cjtestmysqlmgr_x_ro listening on 64470 got request to disconnect invalid connections: metadata change
2020-12-12 14:41:44 routing INFO [7f34585f1700] Routing routing:cjtestmysqlmgr_ro listening on 6447 got request to disconnect invalid connections: metadata change
2020-12-12 14:41:44 metadata_cache WARNING [7f34585f1700] metadata_cache authentication backend is not supported for metadata version 1.0
2020-12-12 14:41:46 metadata_cache WARNING [7f34585f1700] metadata_cache authentication backend is not supported for metadata version 1.0

备存下最初我Dockfile安装的方式(后续正式环境未采用):

打开https://dev.mysql.com/downloads/repo/yum/选择对应MySQL Yum Repository

1
2
3
[root@support ~]$wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
[root@support ~]$rpm -Uvh mysql80-community-release-el7-3.noarch.rpm
[root@support ~]$yum -y install mysql-router

11.4.3 配置(如非容器化部署环境下)

最终选择容器化部署MySQL Router,环境变量无误即可部署完毕,对于无状态化部署,这种方式非常合适。

【后续如果在其他环境下部署,下面内容供参考,更重要的是其中的路由策略进行阐述和说明】

【版本验证】

1
2
[root@support ~]$mysqlrouter --version
MySQL Router  Ver 8.0.22 for Linux on x86_64 (MySQL Community - GPL)

具体配置

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
# 建立
sh-4.2$ mkdir -p /usr/local/mysqlrouter1

# 关键步骤,生成配置文件。
sh-4.2$ mysqlrouter --bootstrap root@mysql-mgr-node2-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306 --directory /usr/local/mysqlrouter --conf-use-sockets --account root --force
Please enter MySQL password for root: 
# Bootstrapping MySQL Router instance at '/usr/local/mysqlrouter1'...

Please enter MySQL password for root: 
Executing statements failed with: 'Error executing MySQL query "INSERT INTO mysql_innodb_cluster_metadata.routers        (host_id, router_name) VALUES (5, '')": The MySQL server is running with the --super-read-only option so it cannot execute this statement (1290)' (1290), trying to connect to another node
Fetching Cluster Members
disconnecting from mysql-server
trying to connect to mysql-server at mysql-mgr-node2-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306
- Creating account(s) (only those that are needed, if any)
- 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 /usr/local/mysqlrouter1/mysqlrouter.conf

# MySQL Router configured for the InnoDB Cluster 'cjtestmysqlmgr'

After this MySQL Router has been started with the generated configuration

    $ mysqlrouter -c /usr/local/mysqlrouter1/mysqlrouter.conf

the cluster 'cjtestmysqlmgr' can be reached by connecting to:

## MySQL Classic protocol

- Read/Write Connections: localhost:6446, /usr/local/mysqlrouter1/mysql.sock
- Read/Only Connections:  localhost:6447, /usr/local/mysqlrouter1/mysqlro.sock

## MySQL X protocol

- Read/Write Connections: localhost:64460, /usr/local/mysqlrouter1/mysqlx.sock
- Read/Only Connections:  localhost:64470, /usr/local/mysqlrouter1/mysqlxro.sock
  • [--conf-use-sockets]:有选择地为所有四种连接类型启用UNIX域套接字,如示例中所示。
  • [--conf-skip-tcp]:有选择地禁用TCP端口,--conf-use-sockets如果只希望使用套接字,则可以使用该选项 。
  • [--conf-base-port]:(可选)更改端口范围,而不使用默认端口。这将设置经典读写(PRIMARY)连接的端口,默认为 6446
  • [--conf-bind-address]:(可选)更改每个路由的bind_address值。

【生成的各文件如下,这是在/usr/local/mysqlrouter1/,这个路径就是前述mysqlrouter --bootstrap指定的目录名。】

关键这个目录数据已经持久化。

1
2
3
4
5
6
7
8
9
sh-4.2$ ls /usr/local/mysqlrouter1 -l
total 16
drwx------. 2 1000610000 65534  116 Nov 29 14:38 data
drwx------. 2 1000610000 65534   29 Nov 29 14:38 log
-rw-------. 1 1000610000 65534 1953 Nov 29 14:38 mysqlrouter.conf
-rw-------. 1 1000610000 65534   94 Nov 29 14:38 mysqlrouter.key
drwx------. 2 1000610000 65534    6 Nov 29 14:38 run
-rwx------. 1 1000610000 65534  142 Nov 29 14:38 start.sh
-rwx------. 1 1000610000 65534  179 Nov 29 14:38 stop.sh

【查看mysqlrouter.conf配置文件】

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
sh-4.2$ cat /usr/local/mysqlrouter1/mysqlrouter.conf
# File automatically generated during MySQL Router bootstrap
[DEFAULT]
logging_folder=/usr/local/mysqlrouter1/log
runtime_folder=/usr/local/mysqlrouter1/run
data_folder=/usr/local/mysqlrouter1/data
keyring_path=/usr/local/mysqlrouter1/data/keyring
master_key_path=/usr/local/mysqlrouter1/mysqlrouter.key
connect_timeout=15
read_timeout=30
dynamic_state=/usr/local/mysqlrouter1/data/state.json

[logger]
level = INFO

[metadata_cache:cjtestmysqlmgr]
cluster_type=gr
router_id=3
user=root
metadata_cluster=cjtestmysqlmgr
ttl=0.5
auth_cache_ttl=-1
auth_cache_refresh_interval=2
use_gr_notifications=0

[routing:cjtestmysqlmgr_rw]
bind_address=0.0.0.0
bind_port=6446
socket=/usr/local/mysqlrouter1/mysql.sock
destinations=metadata-cache://cjtestmysqlmgr/?role=PRIMARY
routing_strategy=first-available
protocol=classic

[routing:cjtestmysqlmgr_ro]
bind_address=0.0.0.0
bind_port=6447
socket=/usr/local/mysqlrouter1/mysqlro.sock
destinations=metadata-cache://cjtestmysqlmgr/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=classic

[routing:cjtestmysqlmgr_x_rw]
bind_address=0.0.0.0
bind_port=64460
socket=/usr/local/mysqlrouter1/mysqlx.sock
destinations=metadata-cache://cjtestmysqlmgr/?role=PRIMARY
routing_strategy=first-available
protocol=x

[routing:cjtestmysqlmgr_x_ro]
bind_address=0.0.0.0
bind_port=64470
socket=/usr/local/mysqlrouter1/mysqlxro.sock
destinations=metadata-cache://cjtestmysqlmgr/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=x

[http_server]
port=8443
ssl=1
ssl_cert=/usr/local/mysqlrouter1/data/router-cert.pem
ssl_key=/usr/local/mysqlrouter1/data/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

11.4.4 参数解释

【重要:】

在MySQL Router 8.0之前,配置文件使用了mode选项,mode选项的可选值为read-write或read-only,但其实际作用并不是字面含义所示,非常容易造成误解。

在在MySQL Router 8.0之后,使用routing_strategy选项作为定义策略的更灵活方式,代替现已弃用的mode选项。其中first-available对应read-write,而round-robin对应read-only。

first-available:首个可用,如果失败,则使用下一个可用服务器。此循环将持续到所有服务器都不可用。

next-available:类似first-available,只不过所有时效的服务器将不再循环。

round-robin:用于负载平滑,轮询。

round-robin-with-fallback:轮询-回退。

另外MySQL Router 8.0之后增加的功能【Bootstrap接受InnoDB Cluster的任何成员,这个很好!】

添加了–ssl-key和–ssl-cert命令行选项,指定客户端证书和私钥以方便客户端身份验证。用于使用REQUIRE X509创建root帐户时。

添加了connect_timeout和read_timeout元数据配置文件选项。它们在[DEFAULT]命名空间下定义,并影响元数据服务器连接等内部操作。

Bootstrap接受InnoDB Cluster的任何成员,并自动查找并重新连接到可写服务器。以前只接受主库。

Bootstrap接受–config选项并读取[logger]级别选项的定义。

最大并发客户端连接数从500增加到5000。

添加了一个新的mysqlrouter_plugin_info实用程序来帮助调试MySQL Router插件。

【路由解释】

这里设置了四个路由策略:其中2个对应MySQL X protocol,两个对应MySQL Classic protocol。

1、6446和6447对应是MySQL Classic protocol的读写和只读端口。分别采用了首个可用和轮询回退的路由策略。

2、64460和64470对应是MySQL X protocol的读写和只读端口。分别采用了首个可用和轮询回退的路由策略。

【查看start.sh启动脚本】

1
2
3
4
5
sh-4.2$ cat start.sh 
#!/bin/bash
basedir=/tmp/myrouter
ROUTER_PID=$basedir/mysqlrouter.pid /usr/bin/mysqlrouter -c $basedir/mysqlrouter.conf &
disown %-

【查看stop.sh停止脚本】

1
2
3
4
5
sh-4.2$ cat stop.sh 
#!/bin/bash
if [ -f /tmp/myrouter/mysqlrouter.pid ]; then
  kill -TERM `cat /tmp/myrouter/mysqlrouter.pid` && rm -f /tmp/myrouter/mysqlrouter.pid
fi

11.4.5 Mysql Router的HA配置

为提高Mysql Router的高可用性,还需要编制Mysql Router的服务svc。

创建mysqlroutersvc.yaml文件。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
apiVersion: v1
kind: Service
metadata:
  name: mysqlroutersvc
spec:
  selector:
    app: mysqlrouter
  ports:
  - port: 6446
    name: mysqlrouter-rw
    protocol: TCP
    targetPort: 6446
  - port: 6447
    name: mysqlrouter-ro
    protocol: TCP
    targetPort: 6447

创建svc

1
$oc create -f mysqlroutersvc.yaml

注意:这里6446和6447对应是MySQL Classic protocol的读写和只读端口。分别采用了首个可用和轮询回退的路由策略。

12.高可用验证

12.1 初步验证

在其他节点,直接访问MySQL Router 对应内部svc的6446端口

【结论:MySQL Router Cluster IP的url为mysqlroutersvc.mysql-mgr.svc.cluster.local】

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
# 获取svc名称
[root@support mysql-mgr]$oc get svc
NAME             TYPE        CLUSTER-IP       EXTERNAL-IP   PORT(S)             AGE
helloworldsvc    ClusterIP   172.30.121.121   <none>        8081/TCP            5h9m
mysqlmgr-svc     ClusterIP   None             <none>        3306/TCP            23d
mysqlroutersvc   ClusterIP   172.30.60.186    <none>        6446/TCP,6447/TCP   9m37s
# 获取pod信息
root@support mysql-mgr]$oc get pod
NAME                             READY   STATUS    RESTARTS   AGE
centos7-74b85b6b9d-vtt4n         1/1     Running   0          7d13h
etcd-operator-66f94b6847-9s2gb   1/1     Running   0          17d
helloworld-548b8dc94f-wb584      1/1     Running   0          5h8m
mysql-mgr-node1-0                1/1     Running   0          13d
mysql-mgr-node2-0                1/1     Running   0          111m
mysql-mgr-node3-0                1/1     Running   0          13d
mysql-router-0                   1/1     Running   0          6m59s
mysql-router-1                   1/1     Running   0          6m56s
mysql-router-2                   1/1     Running   0          6m42s
mysqlshell-78bd978565-24cjj      1/1     Running   335        13d

# 登录到其中一个节点,结果证明链路ok
# 关键svc的url为mysqlroutersvc.mysql-mgr.svc.cluster.local
[root@support mysql-mgr]$oc rsh mysql-mgr-node1-0 
$ mysql -h mysqlroutersvc.mysql-mgr.svc.cluster.local -P 6446 -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 887
Server version: 8.0.22 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> 

因为是6646端口,对应是可读写端口,这里开始建立测试数据。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
# table1表格,和test数据库在此之前【9.1 数据同步测试】章节已创建。
mysql> INSERT INTO test.table1 VALUES (1, 'value1bymysqlrouter');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM test.table1;
+----+---------------------+
| id | content             |
+----+---------------------+
|  1 | value1bymysqlrouter |
+----+---------------------+
1 row in set (0.00 sec)

分别到node1、node2以及node3节点上进行数据验证,数据完全一致。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
[root@support ~]$oc rsh mysql-mgr-node3-0
$ mysql -u root -p

mysql> SELECT * FROM test.table1;
+----+---------------------+
| id | content             |
+----+---------------------+
|  1 | value1bymysqlrouter |
+----+---------------------+
1 row in set (0.00 sec)

说明MySQL Router部署正常。

【测试6447端口】

1
2
3
4
$ mysql -h mysqlroutersvc.mysql-mgr.svc.cluster.local -P 6447 -u root -p

mysql> INSERT INTO test.table1 VALUES (1, 'value1bymysqlrouter2');
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

说明6447对应的是读端口,因此无法写入。

【如果是访问64460端口的MySQL X protocol,则提示协议不匹配】

1
2
3
$ mysql -h mysql-router-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local -P 64460 -u root -p
Enter password: 
ERROR 2007 (HY000): Protocol mismatch; server version = 11, client version = 10

12.2 Failover测试

准备测试数据

这里单独准备了测试数据库

数据库脚本dbtest1.sql

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
/*
 Navicat Premium Data Transfer

 Source Server         : test
 Source Server Type    : MySQL
 Source Server Version : 80022
 Source Host           : 127.0.0.1:3306
 Source Schema         : dbtest1

 Target Server Type    : MySQL
 Target Server Version : 80022
 File Encoding         : 65001

 Date: 12/12/2020 12:36:45
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for table1
-- ----------------------------
DROP TABLE IF EXISTS `table1`;
CREATE TABLE `table1` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  `deleted_at` datetime(3) DEFAULT NULL,
  `content` longtext,
  PRIMARY KEY (`id`),
  KEY `idx_table1_deleted_at` (`deleted_at`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of table1
-- ----------------------------
BEGIN;
INSERT INTO `table1` VALUES (1, '2020-12-12 12:19:21.319', '2020-12-12 12:19:21.319', NULL, '张诚');
INSERT INTO `table1` VALUES (2, '2020-12-12 12:19:21.319', '2020-12-12 12:19:21.319', NULL, '徐志刚');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

拷贝到nfs共享目录下,方便后续操作

1
[root@support helloworld]$cp dbtest1.sql /data/nfs/mysqlm-mgr-pri/node1/

登录到MGR任意节点(本集群为单主,因此需要登录当前primary节点)或者MySQL Router即可。执行脚本

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
# 登录mgr节点
oc rsh mysql-mgr-node1-0
mysql -uroot -p

# 创建测试用例数据库
mysql> CREATE DATABASE dbtest1;
Query OK, 1 row affected (0.02 sec)

mysql> use dbtest1;
Database changed

mysql> source dbtest1.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.07 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM dbtest1.table1;
+----+-------------------------+-------------------------+------------+-----------+
| id | created_at              | updated_at              | deleted_at | content   |
+----+-------------------------+-------------------------+------------+-----------+
|  1 | 2020-12-12 12:19:21.319 | 2020-12-12 12:19:21.319 | NULL       | 张诚    |
|  2 | 2020-12-12 12:19:21.319 | 2020-12-12 12:19:21.319 | NULL       | 徐志刚 |
+----+-------------------------+-------------------------+------------+-----------+
2 rows in set (0.00 sec)

mysql> 

接下来部署代码,编写测试代码,采用golang编写。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
package main

import (
        "github.com/gin-gonic/gin"
        "gorm.io/driver/mysql"
        "gorm.io/gorm"
)

type table1 struct {
        gorm.Model
        Content string
}

func main() {
        dsn := "root:Pass@tcp(mysqlroutersvc.mysql-mgr.svc.cluster.local:6446)/dbtest1?charset=utf8mb4&parseTime=True&loc=Local"
        db, _ := gorm.Open(mysql.Open(dsn), &gorm.Config{})
        db.AutoMigrate(&table1{})

        r := gin.Default()
        r.GET("/add", func(c *gin.Context) {
                db.Create(&table1{Content: "新增"})
                c.JSON(200, gin.H{
                        "message": "add success",
                })
        })
        r.GET("/ping1", func(c *gin.Context) {
                var user table1
                db.Where("content = ?", "张诚").Find(&user)
                c.JSON(200, gin.H{
                        "message": user.Content,
                })
        })
        r.GET("/ping2", func(c *gin.Context) {
                var user table1
                db.Where("content = ?", "徐志刚").Find(&user)
                c.JSON(200, gin.H{
                        "message": user.Content,
                })
        })
        r.Run("0.0.0.0:8081")
}

当前目录执行go init

1
2
$ go mod init cj.com.cn/goginhello1/v0.1
go: creating new go.mod: module cj.com.cn/goginhello1/v0.1

编写Dockfile

【采用多阶段构建,-ldflags “-s -w"减少文件体积,并利用upx压缩】

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
FROM golang:1.14-alpine as builder
WORKDIR /usr/src/app
ENV GOPROXY=https://goproxy.cn
RUN sed -i 's/dl-cdn.alpinelinux.org/mirrors.aliyun.com/g' /etc/apk/repositories && \
  apk add --no-cache upx ca-certificates tzdata
COPY ./go.mod ./
COPY ./go.sum ./
RUN go mod download
COPY . .
RUN CGO_ENABLED=0 go build -ldflags "-s -w" -o server &&\
  upx --best server -o _upx_server && \
  mv -f _upx_server server

FROM scratch as runner
COPY --from=builder /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
COPY --from=builder /etc/ssl/certs/ca-certificates.crt /etc/ssl/certs/
COPY --from=builder /usr/src/app/server /opt/app/
CMD ["/opt/app/server"]

【这里调入了一个大坑,即采用docker save和import在家用环境和企业内部环境进行镜像仓库导出和导入,结果发现镜像的metadata数据会丢失,最终导致没有CMD等信息。容器即进程,没有入口命令容器自然销毁。悲催折腾了我好久】

PS:后疫情时代下,万恶的企业信用,应收账款太难讨了,没啥时间写文章了。

【编译镜像,听说docker小抖了一把,这里采用podman做教程】

1
2
3
4
5
$podman build -f dockerfile -t registry.cj.io:5000/helloworld:0.0.2 .

# 镜像大小只有5.02M,果然满意
$podman image ls | grep helloworld
registry.cj.io:5000/helloworld      0.0.2          babb5d221db0   7 hours ago     5.02 MB

【推送到企业内部镜像仓库】

1
$podman push registry.cj.io:5000/helloworld:0.0.2

【编写测试yaml文件helloworld.yaml

注意这里,imagePullPolicy: Always我用了这个镜像拉取策略,避免application测试环境阶段的重新部署

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
apiVersion: apps/v1
#kind: StatefulSet
kind: Deployment
metadata:
  name: helloworld
  namespace: mysql-mgr
spec:
  #serviceName: mysqlmgr-svc
  replicas: 1
  selector:
    matchLabels:
      app: helloworld
  template:
    metadata:
      labels:
        app: helloworld
    spec:
      containers:
      - name: helloworld
        image: 'registry.cj.io:5000/helloworld:0.0.2'
        imagePullPolicy: Always
        #ports:
        #  - containerPort: 8081
        #    protocol: TCP

创建RS

1
oc creater -f helloworld.yaml

【考虑到集群外访问,这里编制了对应svc】

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
cat helloworldsvc.yaml 
apiVersion: v1
kind: Service
metadata:
  name: helloworldsvc
spec:
  selector:
    app: helloworld
  ports:
  - port: 8081
    name: hellowo
    protocol: TCP
    targetPort: 8081

创建svc

1
oc creater -f helloworldsvc.yaml

【创建Openshift下的Router】

注意这里oc expose的用法

1
2
3
4
5
6
7
[root@support mysql-mgr]$oc get svc
NAME             TYPE        CLUSTER-IP       EXTERNAL-IP   PORT(S)             AGE
helloworldsvc    ClusterIP   172.30.121.121   <none>        8081/TCP            6h28m
mysqlmgr-svc     ClusterIP   None             <none>        3306/TCP            23d
mysqlroutersvc   ClusterIP   172.30.60.186    <none>        6446/TCP,6447/TCP   88m

oc expose svc helloworldsvc --hostname=helloworld.apps.openshift4.cj.io

【验证】

1
2
3
[root@support mysql-mgr]$oc get routes.route.openshift.io 
NAME            HOST/PORT                          PATH   SERVICES        PORT      TERMINATION   WILDCARD
helloworldsvc   helloworld.apps.openshift4.cj.io          helloworldsvc   hellowo                 None

【连续性测试】,应用的对外发布地址url为http://helloworld.apps.openshift4.cj.io/ping1

1
2
3
4
5
6
7
8
9
[root@support helloworld]$for (( c=1; c<=10000; c++ ));do curl http://helloworld.apps.openshift4.cj.io/ping1 ; echo "\r"; done
{"message":"张诚"}\r
{"message":"张诚"}\r
{"message":"张诚"}\r
{"message":"张诚"}\r
{"message":"张诚"}\r
{"message":"张诚"}\r
{"message":"张诚"}\r
{"message":"张诚"}\r

模拟数据库集群任两个node故障,将MGR的两个pod delete掉,可以看出不断curl过程中,中间有一个小的停顿后服务继续进行。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
[GIN] 2020/12/14 - 19:21:55 | 200 |    1.768118ms |      172.18.1.2 | GET      "/ping1"

2020/12/14 19:21:55 /usr/src/app/goginhello.go:38 Error 1053: Server shutdown in progress
[1.446ms] [rows:0] SELECT * FROM `table1` WHERE content = '张诚' AND `table1`.`deleted_at` IS NULL
[GIN] 2020/12/14 - 19:21:55 | 200 |    1.541046ms |      172.18.1.2 | GET      "/ping1"
[mysql] 2020/12/14 19:21:55 packets.go:122: closing bad idle connection: EOF
[mysql] 2020/12/14 19:21:55 connection.go:158: driver: bad connection

2020/12/14 19:21:59 /usr/src/app/goginhello.go:38 SLOW SQL >= 200ms
[3782.476ms] [rows:1] SELECT * FROM `table1` WHERE content = '张诚' AND `table1`.`deleted_at` IS NULL
[GIN] 2020/12/14 - 19:21:59 | 200 |  3.782572577s |      172.18.1.2 | GET      "/ping1"
[GIN] 2020/12/14 - 19:21:59 | 200 |    2.012091ms |      172.18.1.2 | GET      "/ping1"

直接截屏比较清晰

https://typorabyethancheung911.oss-cn-shanghai.aliyuncs.com/typora/image-20201214192812343.png

可以很明晰看出,前端curl命令在不断访问go部署的后端服务,后端服务在通过mysql-router进行代理访问mgr集群时候,进行了成功的failover故障切换。

12.3 Failback测试

MGR中的两个node节点故障后,通过前述【9.2 故障测试】中进行手动故障恢复,将集群状态恢复,再次启动前端业务访问。

1
2
3
4
5
6
7
8
9
[root@support helloworld]$for (( c=1; c<=10000; c++ ));do curl http://helloworld.apps.openshift4.cj.io/ping1 ; echo "\r"; done
{"message":"张诚"}\r
{"message":"张诚"}\r
{"message":"张诚"}\r
{"message":"张诚"}\r
{"message":"张诚"}\r
{"message":"张诚"}\r
{"message":"张诚"}\r
{"message":"张诚"}\r

将【12.2 Failback测试】中剩余一个节点模拟故障。可以看出不断curl过程中,中间有一个小的停顿后服务继续进行。

https://typorabyethancheung911.oss-cn-shanghai.aliyuncs.com/typora/image-20201214203253605.png

进行了成功的Failback故障恢复,MySQL Router将已恢复节点自动加入了可用节点。

13.基准测试

13.1 基本介绍

这里进行简单的数据库基准测试:

数据库的基准测试是对数据库的性能指标进行定量的、可复现的、可对比的测试。基准测试与压力测试 基准测试可以理解为针对系统的一种压力测试。但基准测试不关心业务逻辑,更加简单、直接、易于测试,数据可以由工具生成,不要求真实;而压力测试一般考虑业务逻辑(如购物车业务),要求真实的数据。

对于多数Web应用,整个系统的瓶颈在于数据库;原因很简单:Web应用中的其他因素,例如网络带宽、负载均衡节点、应用服务器(包括CPU、内存、硬盘灯、连接数等)、缓存,都很容易通过水平的扩展(俗称加机器)来实现性能的提高。而对于MySQL,由于数据一致性的要求,无法通过增加机器来分散向数据库写数据带来的压力;虽然可以通过前置缓存(Redis等)、读写分离、分库分表来减轻压力,但是与系统其它组件的水平扩展相比,受到了太多的限制。

而对数据库的基准测试的作用,就是分析在当前的配置下(包括硬件配置、OS、数据库设置等),数据库的性能表现,从而找出MySQL的性能阈值,并根据实际系统的要求调整配置。

常见的数据库指标包括:

TPS/QPS:衡量吞吐量。

响应时间:包括平均响应时间、最小响应时间、最大响应时间、时间百分比等,其中时间百分比参考意义较大,如前95%的请求的最大响应时间。

并发量:同时处理的查询请求的数量。

下面介绍使用sysbench进行数据库性能(OLTP基准测试)

sysbench是跨平台的基准测试工具,支持多线程,支持多种数据库;主要包括以下几种测试:

1.cpu性能

2.磁盘io性能

3.调度程序性能

4.内存分配及传输速度

5.POSIX线程性能

6.数据库性能(OLTP基准测试)【本次测试】

13.2 安装

1
2
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench

验证

1
2
sysbench --version
sysbench 1.0.20

利用自带脚本测试

13.3 环境准备

登录到MySQL MGR Primary节点

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
# 
mysql -uroot -p
# 创建sysbench测试用户
CREATE USER  'sysbench'@'%'  IDENTIFIED BY  'Pass'# 修改密码加密方式,否则sysbench报错
ALTER USER 'sysbench'@'%' IDENTIFIED WITH mysql_native_password BY 'Pass';
# 授权,否则sysbench报错
GRANT ALL PRIVILEGES ON *.* TO 'sysbench'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
# 创建sysbench脚本制定测试数据库sbtest,否则sysbench报错
mysql> create database sbtest;
Query OK, 1 row affected (0.02 sec)

【sysbench报错处理】

1
2
3
4
5
6
7
# 如下报错,需要修改为mysql_native_password
FATAL: error 2059: Plugin caching_sha2_password could not be loaded: lib64/mariadb/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory
FATAL: `prepare' function failed: /usr/share/sysbench/tests/include/oltp_legacy/common.lua:111: Failed to connect to the database
# 如下报错,需要进行用户授权
FATAL: error 1044: Access denied for user 'sysbench'@'%' to database 'sbtest'
# 如下报错,需要创建测试数据库
FATAL: error 1049: Unknown database 'sbtest'

13.4 准备数据

这里跳过MySQL Router来针对MGR进行测试

这里试验发现直接sysbench直连MySQL Router的SVC会出现多次死锁现象。这里采用直连MGR进行基准测试。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=mysql-mgr-node2-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local --mysql-port=3306 --mysql-user=sysbench --mysql-password=Pass --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 --threads=5 --time=600 --report-interval=10 prepare

Creating table 'sbtest1'...
Inserting 10000 records into 'sbtest1'
Creating secondary indexes on 'sbtest1'...
Creating table 'sbtest2'...
Inserting 10000 records into 'sbtest2'
Creating secondary indexes on 'sbtest2'...
Creating table 'sbtest3'...
Inserting 10000 records into 'sbtest3'
Creating secondary indexes on 'sbtest3'...
Creating table 'sbtest4'...
Inserting 10000 records into 'sbtest4'
Creating secondary indexes on 'sbtest4'...
Creating table 'sbtest5'...
Inserting 10000 records into 'sbtest5'
Creating secondary indexes on 'sbtest5'...
Creating table 'sbtest6'...
Inserting 10000 records into 'sbtest6'
Creating secondary indexes on 'sbtest6'...
Creating table 'sbtest7'...
Inserting 10000 records into 'sbtest7'
Creating secondary indexes on 'sbtest7'...
Creating table 'sbtest8'...
Inserting 10000 records into 'sbtest8'
Creating secondary indexes on 'sbtest8'...
Creating table 'sbtest9'...
Inserting 10000 records into 'sbtest9'
Creating secondary indexes on 'sbtest9'...
Creating table 'sbtest10'...
Inserting 10000 records into 'sbtest10'
Creating secondary indexes on 'sbtest10'...

【相关解释】

–db–driver数据库类型,mysql - MySQL driver 、pgsql - PostgreSQL driver –mysql-host 数据库主机 –mysql-port 数据库端口 –mysql-user 登陆名 –mysql-password 登陆密码 –mysql-db 测试数据库名称,默认为sbtest

【mysql执行参数】 –oltp-test-mode:执行模式,包括simple、nontrx和complex,默认是complex。simple模式下只测试简单的查询; nontrx不仅测试查询,还测试插入更新等,但是不使用事务;complex模式下测试最全面,会测试增删改查,而且会使用事务。可以根据自己的需要选择测试模式。 –oltp-tables-count:测试的表数量,根据实际情况选择 –oltp-table-size:测试的表的大小,根据实际情况选择 –threads:客户端的并发连接数,基准测试要进行多次才有意义。测试必须模拟多线程的情况,单线程情况不但无法模拟真实的效率,也无法模拟阻塞甚至死锁情况。 –time:测试执行的时间,单位是秒,该值不要太短,实际选择不要低于20分钟。 –report-interval:生成报告的时间间隔,单位是秒,如10

测试脚本:利用默认的,例如/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua

perpare 准备数据; run 运行数据; cleanup 清理数据

13.5 测试方案

执行模式为complex,使用了10个表,每个表有1万条数据,客户端的并发线程数为5,执行时间为10分钟,每10秒生成一次报告。

13.6 执行测试

将测试结果导出到文件中,便于后续分析。

1
sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=mysql-mgr-node2-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local --mysql-port=3306 --mysql-user=sysbench --mysql-password=Pass --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 --threads=5 --time=600 --report-interval=10 run >> /mysysbench.log

1、测试结果【2个线程,1000条数据】

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

# 测试的相关参数信息
Running the test with following options:
Number of threads: 2
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

# -- 每10秒钟报告一次测试结果,tps、每秒读、每秒写、99%以上的响应时长统计

Threads started!
# 中间过程
[ 10s ] thds: 2 tps: 84.38 qps: 1689.41 (r/w/o: 1182.73/337.72/168.96) lat (ms,95%): 33.12 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 2 tps: 87.60 qps: 1754.05 (r/w/o: 1227.84/351.01/175.21) lat (ms,95%): 30.81 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 2 tps: 87.70 qps: 1753.53 (r/w/o: 1227.72/350.41/175.40) lat (ms,95%): 30.81 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 2 tps: 88.40 qps: 1768.50 (r/w/o: 1237.70/354.00/176.80) lat (ms,95%): 30.26 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 2 tps: 87.80 qps: 1754.59 (r/w/o: 1228.19/350.80/175.60) lat (ms,95%): 31.37 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 2 tps: 86.60 qps: 1733.31 (r/w/o: 1213.33/346.78/173.19) lat (ms,95%): 31.37 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 2 tps: 88.41 qps: 1768.10 (r/w/o: 1237.67/353.62/176.81) lat (ms,95%): 30.81 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 2 tps: 84.07 qps: 1679.40 (r/w/o: 1175.78/335.48/168.14) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 2 tps: 85.63 qps: 1713.82 (r/w/o: 1199.63/342.92/171.26) lat (ms,95%): 33.12 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 2 tps: 86.70 qps: 1734.80 (r/w/o: 1214.20/347.20/173.40) lat (ms,95%): 31.37 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 2 tps: 88.70 qps: 1772.40 (r/w/o: 1240.60/354.40/177.40) lat (ms,95%): 30.26 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 2 tps: 90.90 qps: 1818.50 (r/w/o: 1273.10/363.60/181.80) lat (ms,95%): 29.72 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
    #读总数
        read:                            146594
    #写总数    
        write:                           41884
    #其他操作总数(SELECT、INSERT、UPDATE、DELETE之外的操作,例如COMMIT等)     
        other:                           20942
    #全部总数 
        total:                           209420
    ##总事务数(每秒事务数)
    transactions:                        10471  (87.24 per sec.) 
    #查询总数及qps
    queries:                             209420 (1744.87 per sec.)
    #总忽略错误总数(每秒忽略错误次数)
    ignored errors:                      0      (0.00 per sec.)
    #重连总数(每秒重连次数)
    reconnects:                          0      (0.00 per sec.)

General statistics:
   #总耗时
    total time:                          120.0186s
   #共发生多少事务
    total number of events:              10471

Latency (ms):
         min:                                   10.48
         avg:                                   22.92
         max:                                  149.10
         # --超过95%平均耗时
         95th percentile:                       31.37  
         sum:                               239946.72

Threads fairness:
    events (avg/stddev):           5235.5000/10.50
    execution time (avg/stddev):   119.9734/0.00

2、测试结果【5个线程,10万条数据】,qps和tps提高到了2倍。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 5
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 5 tps: 132.77 qps: 2664.31 (r/w/o: 1865.59/532.68/266.04) lat (ms,95%): 65.65 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 5 tps: 143.21 qps: 2863.20 (r/w/o: 2003.97/572.82/286.41) lat (ms,95%): 56.84 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 5 tps: 149.70 qps: 2995.51 (r/w/o: 2096.91/599.20/299.40) lat (ms,95%): 48.34 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 5 tps: 153.90 qps: 3077.80 (r/w/o: 2154.60/615.40/307.80) lat (ms,95%): 47.47 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 5 tps: 151.80 qps: 3036.21 (r/w/o: 2125.20/607.40/303.60) lat (ms,95%): 51.02 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 5 tps: 156.40 qps: 3127.99 (r/w/o: 2189.59/625.60/312.80) lat (ms,95%): 45.79 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 5 tps: 155.20 qps: 3103.51 (r/w/o: 2172.71/620.40/310.40) lat (ms,95%): 49.21 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 5 tps: 157.40 qps: 3148.19 (r/w/o: 2203.69/629.70/314.80) lat (ms,95%): 47.47 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 5 tps: 161.89 qps: 3237.01 (r/w/o: 2265.76/647.46/323.78) lat (ms,95%): 43.39 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 5 tps: 149.01 qps: 2980.68 (r/w/o: 2086.83/595.84/298.02) lat (ms,95%): 54.83 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 5 tps: 161.60 qps: 3231.75 (r/w/o: 2262.37/646.19/323.20) lat (ms,95%): 44.98 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 5 tps: 161.70 qps: 3233.84 (r/w/o: 2263.23/647.21/323.40) lat (ms,95%): 45.79 err/s: 0.00 reconn/s: 0.00
[ 130s ] thds: 5 tps: 164.30 qps: 3283.62 (r/w/o: 2298.71/656.30/328.60) lat (ms,95%): 44.98 err/s: 0.00 reconn/s: 0.00
[ 140s ] thds: 5 tps: 163.70 qps: 3274.55 (r/w/o: 2292.26/654.89/327.39) lat (ms,95%): 44.17 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 5 tps: 164.80 qps: 3293.62 (r/w/o: 2305.22/658.80/329.60) lat (ms,95%): 44.98 err/s: 0.00 reconn/s: 0.00
[ 160s ] thds: 5 tps: 160.20 qps: 3209.23 (r/w/o: 2246.42/642.41/320.40) lat (ms,95%): 45.79 err/s: 0.00 reconn/s: 0.00
[ 170s ] thds: 5 tps: 164.60 qps: 3292.00 (r/w/o: 2304.40/658.40/329.20) lat (ms,95%): 45.79 err/s: 0.00 reconn/s: 0.00
[ 180s ] thds: 5 tps: 164.90 qps: 3294.50 (r/w/o: 2306.70/658.00/329.80) lat (ms,95%): 43.39 err/s: 0.00 reconn/s: 0.00
[ 190s ] thds: 5 tps: 167.60 qps: 3353.69 (r/w/o: 2346.90/671.60/335.20) lat (ms,95%): 43.39 err/s: 0.00 reconn/s: 0.00
[ 200s ] thds: 5 tps: 165.20 qps: 3305.81 (r/w/o: 2314.20/661.20/330.40) lat (ms,95%): 45.79 err/s: 0.00 reconn/s: 0.00
[ 210s ] thds: 5 tps: 166.10 qps: 3320.10 (r/w/o: 2324.00/664.00/332.10) lat (ms,95%): 44.17 err/s: 0.00 reconn/s: 0.00
[ 220s ] thds: 5 tps: 166.50 qps: 3330.80 (r/w/o: 2331.70/666.00/333.10) lat (ms,95%): 42.61 err/s: 0.00 reconn/s: 0.00
[ 230s ] thds: 5 tps: 164.00 qps: 3279.80 (r/w/o: 2296.20/655.60/328.00) lat (ms,95%): 44.98 err/s: 0.00 reconn/s: 0.00
[ 240s ] thds: 5 tps: 166.20 qps: 3325.29 (r/w/o: 2327.29/665.60/332.40) lat (ms,95%): 45.79 err/s: 0.00 reconn/s: 0.00
[ 250s ] thds: 5 tps: 167.40 qps: 3346.51 (r/w/o: 2342.51/669.20/334.80) lat (ms,95%): 43.39 err/s: 0.00 reconn/s: 0.00
[ 260s ] thds: 5 tps: 163.00 qps: 3260.59 (r/w/o: 2282.99/651.60/326.00) lat (ms,95%): 45.79 err/s: 0.00 reconn/s: 0.00
[ 270s ] thds: 5 tps: 164.20 qps: 3284.91 (r/w/o: 2298.91/657.60/328.40) lat (ms,95%): 44.98 err/s: 0.00 reconn/s: 0.00
[ 280s ] thds: 5 tps: 167.50 qps: 3349.99 (r/w/o: 2344.99/670.00/335.00) lat (ms,95%): 44.98 err/s: 0.00 reconn/s: 0.00
[ 290s ] thds: 5 tps: 165.50 qps: 3309.50 (r/w/o: 2316.90/661.60/331.00) lat (ms,95%): 44.98 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 5 tps: 166.98 qps: 3338.65 (r/w/o: 2336.79/667.91/333.96) lat (ms,95%): 44.17 err/s: 0.00 reconn/s: 0.00
[ 310s ] thds: 5 tps: 162.82 qps: 3257.84 (r/w/o: 2280.51/651.69/325.64) lat (ms,95%): 46.63 err/s: 0.00 reconn/s: 0.00
[ 320s ] thds: 5 tps: 168.00 qps: 3357.67 (r/w/o: 2350.78/670.89/336.00) lat (ms,95%): 43.39 err/s: 0.00 reconn/s: 0.00
[ 330s ] thds: 5 tps: 165.89 qps: 3319.88 (r/w/o: 2323.72/664.38/331.79) lat (ms,95%): 44.17 err/s: 0.00 reconn/s: 0.00
[ 340s ] thds: 5 tps: 168.31 qps: 3366.27 (r/w/o: 2356.32/673.33/336.62) lat (ms,95%): 45.79 err/s: 0.00 reconn/s: 0.00
[ 350s ] thds: 5 tps: 150.10 qps: 3001.80 (r/w/o: 2101.40/600.20/300.20) lat (ms,95%): 51.94 err/s: 0.00 reconn/s: 0.00
[ 360s ] thds: 5 tps: 165.30 qps: 3306.40 (r/w/o: 2314.20/661.60/330.60) lat (ms,95%): 44.98 err/s: 0.00 reconn/s: 0.00
[ 370s ] thds: 5 tps: 167.50 qps: 3348.97 (r/w/o: 2344.78/669.19/335.00) lat (ms,95%): 43.39 err/s: 0.00 reconn/s: 0.00
[ 380s ] thds: 5 tps: 166.90 qps: 3336.02 (r/w/o: 2334.61/667.60/333.80) lat (ms,95%): 44.17 err/s: 0.00 reconn/s: 0.00
[ 390s ] thds: 5 tps: 167.80 qps: 3353.51 (r/w/o: 2347.51/670.40/335.60) lat (ms,95%): 44.98 err/s: 0.00 reconn/s: 0.00
[ 400s ] thds: 5 tps: 151.20 qps: 3029.51 (r/w/o: 2120.70/606.40/302.40) lat (ms,95%): 56.84 err/s: 0.00 reconn/s: 0.00
[ 410s ] thds: 5 tps: 170.00 qps: 3399.64 (r/w/o: 2379.96/679.69/339.99) lat (ms,95%): 44.17 err/s: 0.00 reconn/s: 0.00
[ 420s ] thds: 5 tps: 163.20 qps: 3263.66 (r/w/o: 2284.54/652.71/326.41) lat (ms,95%): 46.63 err/s: 0.00 reconn/s: 0.00
[ 430s ] thds: 5 tps: 168.90 qps: 3378.69 (r/w/o: 2364.90/676.00/337.80) lat (ms,95%): 42.61 err/s: 0.00 reconn/s: 0.00
[ 440s ] thds: 5 tps: 164.80 qps: 3296.00 (r/w/o: 2307.20/659.20/329.60) lat (ms,95%): 45.79 err/s: 0.00 reconn/s: 0.00
[ 450s ] thds: 5 tps: 165.30 qps: 3305.99 (r/w/o: 2314.19/661.20/330.60) lat (ms,95%): 44.98 err/s: 0.00 reconn/s: 0.00
[ 460s ] thds: 5 tps: 170.60 qps: 3412.01 (r/w/o: 2388.40/682.40/341.20) lat (ms,95%): 43.39 err/s: 0.00 reconn/s: 0.00
[ 470s ] thds: 5 tps: 165.90 qps: 3318.01 (r/w/o: 2322.61/663.60/331.80) lat (ms,95%): 45.79 err/s: 0.00 reconn/s: 0.00
[ 480s ] thds: 5 tps: 170.10 qps: 3402.00 (r/w/o: 2381.40/680.40/340.20) lat (ms,95%): 43.39 err/s: 0.00 reconn/s: 0.00
[ 490s ] thds: 5 tps: 169.80 qps: 3394.20 (r/w/o: 2376.60/678.00/339.60) lat (ms,95%): 44.17 err/s: 0.00 reconn/s: 0.00
[ 500s ] thds: 5 tps: 166.79 qps: 3337.00 (r/w/o: 2335.43/667.98/333.59) lat (ms,95%): 45.79 err/s: 0.00 reconn/s: 0.00
[ 510s ] thds: 5 tps: 164.00 qps: 3279.61 (r/w/o: 2295.61/656.00/328.00) lat (ms,95%): 46.63 err/s: 0.00 reconn/s: 0.00
[ 520s ] thds: 5 tps: 165.10 qps: 3301.63 (r/w/o: 2311.02/660.41/330.20) lat (ms,95%): 47.47 err/s: 0.00 reconn/s: 0.00
[ 530s ] thds: 5 tps: 167.90 qps: 3359.57 (r/w/o: 2351.75/672.01/335.81) lat (ms,95%): 44.98 err/s: 0.00 reconn/s: 0.00
[ 540s ] thds: 5 tps: 164.60 qps: 3291.47 (r/w/o: 2304.28/657.99/329.20) lat (ms,95%): 46.63 err/s: 0.00 reconn/s: 0.00
[ 550s ] thds: 5 tps: 170.50 qps: 3410.44 (r/w/o: 2387.06/682.39/340.99) lat (ms,95%): 44.17 err/s: 0.00 reconn/s: 0.00
[ 560s ] thds: 5 tps: 165.70 qps: 3313.27 (r/w/o: 2319.45/662.41/331.41) lat (ms,95%): 44.98 err/s: 0.00 reconn/s: 0.00
[ 570s ] thds: 5 tps: 171.00 qps: 3420.78 (r/w/o: 2394.39/684.40/342.00) lat (ms,95%): 43.39 err/s: 0.00 reconn/s: 0.00
[ 580s ] thds: 5 tps: 172.50 qps: 3449.39 (r/w/o: 2414.79/689.60/345.00) lat (ms,95%): 42.61 err/s: 0.00 reconn/s: 0.00
[ 590s ] thds: 5 tps: 170.50 qps: 3409.81 (r/w/o: 2386.81/682.00/341.00) lat (ms,95%): 44.17 err/s: 0.00 reconn/s: 0.00
[ 600s ] thds: 5 tps: 172.50 qps: 3450.79 (r/w/o: 2415.39/690.40/345.00) lat (ms,95%): 43.39 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            1372182
        write:                           392052
        other:                           196026
        total:                           1960260
    transactions:                        98013  (163.35 per sec.)
    queries:                             1960260 (3266.98 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          600.0196s
    total number of events:              98013

Latency (ms):
         min:                                   10.99
         avg:                                   30.60
         max:                                  291.70
         95th percentile:                       45.79
         sum:                              2999375.76

Threads fairness:
    events (avg/stddev):           19602.6000/21.74
    execution time (avg/stddev):   599.8752/0.01

进一步测试10线程,qps和tps都有显著提高,单瓶颈来了,MGR Rrimary主节点所在OCP计算节点CPU不行。

在10个线程下,下图可以看出MGR的Primary节点压力上来了

https://typorabyethancheung911.oss-cn-shanghai.aliyuncs.com/typora/image-20201214224548395.png

计算节点已经报错The total CPU resource limit of all pods on this node is approaching the node’s capacity. Pod performance may be throttled under high load.

公司测试环境为2013年Q2 发布的Intel(R) Xeon(R) CPU E5-2630 v2 @ 2.60GHz

明年3月份公司实验环境鸟枪换炮后,再进行一次测试。

![image-20201214225952716](../../../../../Library/Application Support/typora-user-images/image-20201214225952716.png)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 10
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 10 tps: 296.64 qps: 5951.70 (r/w/o: 4166.89/1190.54/594.27) lat (ms,95%): 46.63 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 10 tps: 277.01 qps: 5540.18 (r/w/o: 3878.13/1108.04/554.02) lat (ms,95%): 57.87 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 10 tps: 273.59 qps: 5468.68 (r/w/o: 3829.02/1092.48/547.19) lat (ms,95%): 65.65 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 10 tps: 270.58 qps: 5404.95 (r/w/o: 3783.29/1080.61/541.06) lat (ms,95%): 66.84 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 10 tps: 273.42 qps: 5468.51 (r/w/o: 3827.52/1094.06/546.93) lat (ms,95%): 63.32 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 10 tps: 248.31 qps: 4975.87 (r/w/o: 3482.79/996.45/496.63) lat (ms,95%): 80.03 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 10 tps: 271.70 qps: 5431.86 (r/w/o: 3802.37/1086.09/543.40) lat (ms,95%): 62.19 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 10 tps: 274.00 qps: 5474.54 (r/w/o: 3831.96/1094.69/547.89) lat (ms,95%): 63.32 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 10 tps: 267.30 qps: 5353.08 (r/w/o: 3747.58/1070.80/534.70) lat (ms,95%): 63.32 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 10 tps: 276.69 qps: 5526.57 (r/w/o: 3868.24/1104.95/553.38) lat (ms,95%): 56.84 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 10 tps: 267.31 qps: 5342.46 (r/w/o: 3739.58/1068.25/534.63) lat (ms,95%): 65.65 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 10 tps: 271.80 qps: 5447.48 (r/w/o: 3813.46/1090.42/543.61) lat (ms,95%): 61.08 err/s: 0.00 reconn/s: 0.00
[ 130s ] thds: 10 tps: 272.99 qps: 5446.00 (r/w/o: 3811.63/1088.38/545.99) lat (ms,95%): 65.65 err/s: 0.00 reconn/s: 0.00
[ 140s ] thds: 10 tps: 268.60 qps: 5385.96 (r/w/o: 3770.74/1078.01/537.21) lat (ms,95%): 62.19 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 10 tps: 274.60 qps: 5492.01 (r/w/o: 3844.40/1098.40/549.20) lat (ms,95%): 62.19 err/s: 0.00 reconn/s: 0.00
[ 160s ] thds: 10 tps: 266.60 qps: 5324.03 (r/w/o: 3728.22/1062.61/533.20) lat (ms,95%): 64.47 err/s: 0.00 reconn/s: 0.00
[ 170s ] thds: 10 tps: 275.90 qps: 5521.56 (r/w/o: 3864.77/1104.99/551.80) lat (ms,95%): 59.99 err/s: 0.00 reconn/s: 0.00
[ 180s ] thds: 10 tps: 275.60 qps: 5516.23 (r/w/o: 3860.42/1104.61/551.20) lat (ms,95%): 62.19 err/s: 0.00 reconn/s: 0.00
[ 190s ] thds: 10 tps: 272.79 qps: 5452.00 (r/w/o: 3816.86/1089.56/545.58) lat (ms,95%): 62.19 err/s: 0.00 reconn/s: 0.00
[ 200s ] thds: 10 tps: 270.91 qps: 5421.00 (r/w/o: 3794.14/1085.04/541.82) lat (ms,95%): 65.65 err/s: 0.00 reconn/s: 0.00
[ 210s ] thds: 10 tps: 269.90 qps: 5399.15 (r/w/o: 3779.36/1079.99/539.79) lat (ms,95%): 64.47 err/s: 0.00 reconn/s: 0.00
[ 220s ] thds: 10 tps: 272.79 qps: 5450.28 (r/w/o: 3816.71/1087.98/545.59) lat (ms,95%): 61.08 err/s: 0.00 reconn/s: 0.00
[ 230s ] thds: 10 tps: 262.11 qps: 5247.78 (r/w/o: 3671.93/1051.64/524.22) lat (ms,95%): 66.84 err/s: 0.00 reconn/s: 0.00
[ 240s ] thds: 10 tps: 277.60 qps: 5545.36 (r/w/o: 3881.77/1108.39/555.20) lat (ms,95%): 59.99 err/s: 0.00 reconn/s: 0.00
[ 250s ] thds: 10 tps: 270.90 qps: 5414.08 (r/w/o: 3789.88/1082.40/541.80) lat (ms,95%): 64.47 err/s: 0.00 reconn/s: 0.00
[ 260s ] thds: 10 tps: 269.30 qps: 5395.70 (r/w/o: 3777.10/1080.00/538.60) lat (ms,95%): 61.08 err/s: 0.00 reconn/s: 0.00
[ 270s ] thds: 10 tps: 268.80 qps: 5376.17 (r/w/o: 3763.58/1074.99/537.60) lat (ms,95%): 65.65 err/s: 0.00 reconn/s: 0.00
[ 280s ] thds: 10 tps: 269.70 qps: 5394.65 (r/w/o: 3775.84/1079.41/539.41) lat (ms,95%): 61.08 err/s: 0.00 reconn/s: 0.00
[ 290s ] thds: 10 tps: 273.50 qps: 5466.24 (r/w/o: 3826.23/1093.21/546.80) lat (ms,95%): 61.08 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 10 tps: 274.42 qps: 5487.04 (r/w/o: 3841.54/1096.47/549.03) lat (ms,95%): 64.47 err/s: 0.00 reconn/s: 0.00
[ 310s ] thds: 10 tps: 270.48 qps: 5412.22 (r/w/o: 3788.47/1082.80/540.95) lat (ms,95%): 64.47 err/s: 0.00 reconn/s: 0.00
[ 320s ] thds: 10 tps: 274.70 qps: 5493.06 (r/w/o: 3845.44/1098.31/549.31) lat (ms,95%): 59.99 err/s: 0.00 reconn/s: 0.00
[ 330s ] thds: 10 tps: 272.80 qps: 5458.61 (r/w/o: 3820.91/1092.00/545.70) lat (ms,95%): 61.08 err/s: 0.00 reconn/s: 0.00
[ 340s ] thds: 10 tps: 269.90 qps: 5398.80 (r/w/o: 3778.60/1080.40/539.80) lat (ms,95%): 63.32 err/s: 0.00 reconn/s: 0.00
[ 350s ] thds: 10 tps: 265.09 qps: 5291.03 (r/w/o: 3702.98/1057.97/530.08) lat (ms,95%): 61.08 err/s: 0.00 reconn/s: 0.00
[ 360s ] thds: 10 tps: 251.91 qps: 5048.76 (r/w/o: 3535.02/1009.83/503.92) lat (ms,95%): 73.13 err/s: 0.00 reconn/s: 0.00
[ 370s ] thds: 10 tps: 276.20 qps: 5522.51 (r/w/o: 3865.50/1104.60/552.40) lat (ms,95%): 61.08 err/s: 0.00 reconn/s: 0.00
[ 380s ] thds: 10 tps: 265.60 qps: 5313.66 (r/w/o: 3719.67/1062.79/531.20) lat (ms,95%): 64.47 err/s: 0.00 reconn/s: 0.00
[ 390s ] thds: 10 tps: 275.40 qps: 5507.34 (r/w/o: 3855.33/1101.21/550.80) lat (ms,95%): 61.08 err/s: 0.00 reconn/s: 0.00
[ 400s ] thds: 10 tps: 262.80 qps: 5256.68 (r/w/o: 3679.48/1051.60/525.60) lat (ms,95%): 65.65 err/s: 0.00 reconn/s: 0.00
[ 410s ] thds: 10 tps: 268.00 qps: 5359.23 (r/w/o: 3751.62/1071.61/536.00) lat (ms,95%): 59.99 err/s: 0.00 reconn/s: 0.00
[ 420s ] thds: 10 tps: 272.50 qps: 5450.81 (r/w/o: 3815.40/1090.40/545.00) lat (ms,95%): 62.19 err/s: 0.00 reconn/s: 0.00
[ 430s ] thds: 10 tps: 267.00 qps: 5338.11 (r/w/o: 3736.90/1067.20/534.00) lat (ms,95%): 64.47 err/s: 0.00 reconn/s: 0.00
[ 440s ] thds: 10 tps: 273.45 qps: 5465.00 (r/w/o: 3826.27/1091.82/546.91) lat (ms,95%): 61.08 err/s: 0.00 reconn/s: 0.00
[ 450s ] thds: 10 tps: 269.32 qps: 5388.08 (r/w/o: 3771.37/1078.08/538.64) lat (ms,95%): 63.32 err/s: 0.00 reconn/s: 0.00
[ 460s ] thds: 10 tps: 274.83 qps: 5500.40 (r/w/o: 3849.85/1100.90/549.65) lat (ms,95%): 59.99 err/s: 0.00 reconn/s: 0.00
[ 470s ] thds: 10 tps: 270.30 qps: 5405.19 (r/w/o: 3783.39/1081.20/540.60) lat (ms,95%): 61.08 err/s: 0.00 reconn/s: 0.00
[ 480s ] thds: 10 tps: 278.40 qps: 5569.22 (r/w/o: 3898.42/1114.00/556.80) lat (ms,95%): 62.19 err/s: 0.00 reconn/s: 0.00
[ 490s ] thds: 10 tps: 265.29 qps: 5304.43 (r/w/o: 3713.08/1060.77/530.58) lat (ms,95%): 62.19 err/s: 0.00 reconn/s: 0.00
[ 500s ] thds: 10 tps: 271.11 qps: 5420.13 (r/w/o: 3795.19/1082.73/542.21) lat (ms,95%): 64.47 err/s: 0.00 reconn/s: 0.00
[ 510s ] thds: 10 tps: 246.70 qps: 4934.23 (r/w/o: 3453.52/987.31/493.40) lat (ms,95%): 71.83 err/s: 0.00 reconn/s: 0.00
[ 520s ] thds: 10 tps: 212.30 qps: 4249.20 (r/w/o: 2973.80/850.80/424.60) lat (ms,95%): 82.96 err/s: 0.00 reconn/s: 0.00
[ 530s ] thds: 10 tps: 279.40 qps: 5580.96 (r/w/o: 3908.17/1113.99/558.80) lat (ms,95%): 56.84 err/s: 0.00 reconn/s: 0.00
[ 540s ] thds: 10 tps: 276.30 qps: 5530.28 (r/w/o: 3871.09/1106.60/552.60) lat (ms,95%): 58.92 err/s: 0.00 reconn/s: 0.00
[ 550s ] thds: 10 tps: 273.40 qps: 5469.92 (r/w/o: 3827.72/1095.40/546.80) lat (ms,95%): 62.19 err/s: 0.00 reconn/s: 0.00
[ 560s ] thds: 10 tps: 274.10 qps: 5482.73 (r/w/o: 3837.75/1096.79/548.19) lat (ms,95%): 58.92 err/s: 0.00 reconn/s: 0.00
[ 570s ] thds: 10 tps: 273.30 qps: 5466.07 (r/w/o: 3826.25/1093.21/546.61) lat (ms,95%): 59.99 err/s: 0.00 reconn/s: 0.00
[ 580s ] thds: 10 tps: 265.20 qps: 5304.05 (r/w/o: 3712.84/1060.81/530.41) lat (ms,95%): 65.65 err/s: 0.00 reconn/s: 0.00
[ 590s ] thds: 10 tps: 272.19 qps: 5432.06 (r/w/o: 3802.50/1085.17/544.39) lat (ms,95%): 61.08 err/s: 0.00 reconn/s: 0.00
[ 600s ] thds: 10 tps: 268.71 qps: 5383.92 (r/w/o: 3769.58/1076.92/537.41) lat (ms,95%): 66.84 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            2267034
        write:                           647724
        other:                           323862
        total:                           3238620
    transactions:                        161931 (269.86 per sec.)
    queries:                             3238620 (5397.19 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          600.0552s
    total number of events:              161931

Latency (ms):
         min:                                   12.36
         avg:                                   37.05
         max:                                  240.43
         95th percentile:                       63.32
         sum:                              5999247.26

Threads fairness:
    events (avg/stddev):           16193.1000/37.62
    execution time (avg/stddev):   599.9247/0.02

13.7 清理数据

执行完测试后,清理数据,否则后面的测试会受到影响。

注意这里-oltp-tables-count=10需要根据前步骤中的-oltp-tables-count的相关设置进行同步设置,否则下次测试会提示表数据已存在。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=mysqlroutersvc.mysql-mgr.svc.cluster.local --mysql-port=6446 --mysql-user=sysbench --mysql-password=Pass --oltp-tables-count=10 cleanup
Dropping table 'sbtest1'...
Dropping table 'sbtest2'...
Dropping table 'sbtest3'...
Dropping table 'sbtest4'...
Dropping table 'sbtest5'...
Dropping table 'sbtest6'...
Dropping table 'sbtest7'...
Dropping table 'sbtest8'...
Dropping table 'sbtest9'...
Dropping table 'sbtest10'..

14. 其他说明

实际部署过程中,在MGR集群搭建后,建议将root账户的名称和密码进行修改。

由于MGR的数据文件全部持久化并存储在外部存储中,root账户的名称和密码修改好,及时POD重启后,仍然维持修改后的状态,确保生产环境可用。

实际部署中,针对MySQL MGR容器化部署还要考虑例如NUMA优化、CPUSET等参数。前面10线程下的OCP下可以看出所需资源压力。

MySQL Router建议还是在应用程序中进行部署。

另外看好ShardingSphere后续发展和应用。

15.TroubleShooting

15.1 错误1

1
2020-11-22T02:30:44.712469Z 8 [Warning] [MY-010604] [Repl] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=mysql-mgr-node2-0-relay-bin' to avoid this problem.

意思是正在使用的relay log名字,是数据库根据system的hostname来取的,一旦system的hostname改变了,那么会影响relay log的名字,同时可能会中断整个replication。

【解决方式】

myslq配置文件添加固定文件名relay-log=mysql-mgr-node1-0-relay-bin

15.2 错误2

1
2020-11-22T02:31:03.734451Z 18 [Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.

MySQL 8.0.21以后版本,START GROUP_REPLICATION;改为使用 START GROUP_REPLICATION USER='rpl_user', PASSWORD='rpl_pass';

15.3 错误3

1
2
2020-11-23T02:55:39.558907Z 20 [ERROR] [MY-010584] [Repl] Slave I/O for channel 'group_replication_recovery': error connecting to master 'rpl_user@mysql-mgr-node1-0:3306' - retry-time: 60 retries: 1 message: Can't connect to MySQL server on 'mysql-mgr-node1-0' (110), Error_code: MY-002003
2020-11-23T02:55:39.562083Z 17 [ERROR] [MY-011582] [Repl] Plugin group_replication reported: 'There was an error when connecting to the donor server. Please check that group_replication_recovery channel credentials and all MEMBER_HOST column values of performance_schema.replication_group_members table are correct and DNS resolvable.'

系DNS解析错误

【解决方式】

myslq配置文件添加

1
2
report_host=mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local
report_port=3306

15.4 错误4

1
2
3
4
[Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
[ERROR] [MY-010584] [Repl] Slave I/O for channel 'group_replication_recovery': error connecting to master 'rpl_user@mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061
[ERROR] [MY-011582] [Repl] Plugin group_replication reported: 'There was an error when connecting to the donor server. Please check that group_replication_recovery channel credentials and all MEMBER_HOST column values of performance_schema.replication_group_members table are correct and DNS resolvable.'
[ERROR] [MY-011583] [Repl] Plugin group_replication reported: 'For details please check performance_schema.replication_connection_status table and error log messages of Slave I/O for channel group_replication_recovery.'

此项错误信息是mysql8.0之后加密规则变成 caching_sha2_password了,造成复制通道无法正常通讯。

【解决办法】

myslq配置文件添加

1
loose-group_replication_recovery_get_public_key=ON

但是这种方式不太安全,无法验证服务器的身份,存在中间人攻击。

更安全的方法是将公钥复制 rpl_user,通过group_replication_recovery_public_key_path 在加入组的成员上配置 系统变量,并为其提供公钥的路径rpl_user

15.5 错误5

1
[ERROR] [MY-011526] [Repl] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: b9a35b98-2d37-11eb-bd5d-0a580a830102:1-5 > Group transactions: b99e0593-2d37-11eb-931f-0a580a830103:1-5, [ERROR] [MY-011522] [Repl] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'

MySQL是新装的没问题,但是容器化部署MySQL,往往都会通过环境变量重设了密码,本文的容器在初始化中,根据StatefulSet的yaml文件中env环境变量的设置,密码已经修改了。那么修改密码的记录是存在在binlog日志中的。

很多人根据日志信息将此变量置为:set global group_replication_allow_local_disjoint_gtids_join=ON;但是根据官方文档描述

RESET MASTER enables you to delete any binary log files and their related binary log index file, returning the master to its state before binary logging was started.

只要重新执行RESET MASTER 既可。

15.6 错误6

MGR出现提示refuse ip等错误信息。

MGR的各节点白名单会根据当前网卡的绑定地址,自动添加。

对于跨网段的通讯,插件启动后默认的白名单会组织其他节点通讯。

【解决办法】

设置白名单

15.7 错误7

1
2
3
4
5
6
7
8
 MySQL  mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:33060+ ssl  JS > var cluster = dba.createCluster('cjtestmysqlmgr', {adoptFromGR: true});
A new InnoDB cluster will be created based on the existing replication group on instance 'root@mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306'.

Creating InnoDB cluster 'cjtestmysqlmgr' on 'root@mysql-mgr-node1-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306'...

Adding Seed Instance...
Adding Instance 'mysql-mgr-node2-0.mysqlmgr-svc.mysql-mgr.svc.cluster.local:3306'...
Dba.createCluster: The table does not comply with the requirements by an external plugin. (MySQL Error 3098)

很可能因为MGR未转换成InnoDB Cluster

【解决办法】

通过MySQL Shell将MGR未转换成InnoDB Cluster,详见《11.2 将MGR配置成InnoDB Cluster》。

**BY 张诚 2020.12.14